Too many 'open files' : -104 ISAM error

We are working with a Jasper report with 6 sub-reports. Each of the sub-reports have a Stored Procedure behind it. Five sub-reports are accessing a temp table created in one of the sub-report. We see that this temp table is visible to all SP's when the report is run. But as we add a new sub-report and try to also access the temp table in it, the informix DB is throwing an error that the temp table is not in the database. The accompanying error is pasted below. We are not sure what the 'open files' are and how to restrict their number to clear this error. Kindly help. -242 Could not open database table table-name. The database server cannot begin reading a table. Check the accompanying ISAM error code for more information. The problem might be file permissions, a hardware error, or a corrupted system catalog. Unless the ISAM error code or an operating-system message points to another cause, run the bcheck or secheck utility to verify file integrity. -104 ISAM error: too many files open. The ISAM processor has reached its limit of open files. For C-ISAM programs, review the program logic and change it so that fewer files are open concurrently. Use isclose to close unneeded files. For SQL products, this query is too complex; it uses too many tables concurrently. Perform the query in steps, and use temporary tables.

I got caught with this the other by having a deeply nested prepare statement - all my fault :) Look at the onstat -g ses for the falling session Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of RAVI PASUPULETI Sent: Tuesday, April 03, 2012 7:58 AM To: ids@iiug.org Subject: Too many 'open files' : -104 ISAM error [26647] We are working with a Jasper report with 6 sub-reports. Each of the sub-reports have a Stored Procedure behind it. Five sub-reports are accessing a temp table created in one of the sub-report. We see that this temp table is visible to all SP's when the report is run. But as we add a new sub-report and try to also access the temp table in it, the informix DB is throwing an error that the temp table is not in the database. The accompanying error is pasted below. We are not sure what the 'open files' are and how to restrict their number to clear this error. Kindly help. -242 Could not open database table table-name. The database server cannot begin reading a table. Check the accompanying ISAM error code for more information. The problem might be file permissions, a hardware error, or a corrupted system catalog. Unless the ISAM error code or an operating-system message points to another cause, run the bcheck or secheck utility to verify file integrity. -104 ISAM error: too many files open. The ISAM processor has reached its limit of open files. For C-ISAM programs, review the program logic and change it so that fewer files are open concurrently. Use isclose to close unneeded files. For SQL products, this query is too complex; it uses too many tables concurrently. Perform the query in steps, and use temporary tables. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Probably Jasper is not closing and releasing cursors when it is finished with them and that is resulting in the ISAM -104 error (which is the underlying cause of the SQL level error -242). It may be that you have hit an OS limit on open files for a process, but more likely it is an internal error. I have not run into this before, but if you open a support case with IBM, they should be better able to point you in the direction of a resolution. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Apr 3, 2012 at 8:57 AM, RAVI PASUPULETI <rpasupul@gmail.com> wrote: > We are working with a Jasper report with 6 sub-reports. Each of the > sub-reports have a Stored Procedure behind it. Five sub-reports are > accessing > a temp table created in one of the sub-report. We see that this temp table > is > visible to all SP's when the report is run. But as we add a new sub-report > and > try to also access the temp table in it, the informix DB is throwing an > error > that the temp table is not in the database. The accompanying error is > pasted > below. We are not sure what the 'open files' are and how to restrict their > number to clear this error. Kindly help. > > -242 Could not open database table table-name. > > The database server cannot begin reading a table. Check the > accompanying ISAM error code for more information. The problem might be > file permissions, a hardware error, or a corrupted system catalog. > Unless the ISAM error code or an operating-system message points to > another cause, run the bcheck or secheck utility to verify file > integrity. > > -104 ISAM error: too many files open. > > The ISAM processor has reached its limit of open files. For C-ISAM > programs, review the program logic and change it so that fewer files > are open concurrently. Use isclose to close unneeded files. For SQL > products, this query is too complex; it uses too many tables > concurrently. Perform the query in steps, and use temporary tables. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340815b71bc704bcc67c75

Found the issue. There was a drop on this temp table in the last subreport. Obviously the Jasper server is going to and fro from one sub-report of the other and all the SP's accessing the temp table are not done before the temp table is dropped. We removed the drop on the temp table and the error was gone. Ron : "I wouldn't think we would have any sort of control over the order in which the SP's were run by Jasper. Not sure if there is a concept of having a post report "trigger" in the report like there was in Actuate to do any cleanup of tables,etc" Art must be right when he says, "Probably Jasper is not closing and releasing cursors when it is finishedwith them and that is resulting in the ISAM -104 error (which is the underlying cause of the SQL level error -242). It may be that you have hit an OS limit on open files for a process, but more likely it is an internal error." Thanks a lot all involved.