We are using Java Stored Procs to pull data from application DB2(on AIX) tables and the final output is written to a file.

Stored Procs are structured as mentioned below:
There is a driver table from which records are extracted and then for each of this record,subsequently, few other transaction tables are queried and finally the record is written to a text file.

The result from the driver table is fetched into a ResultSet and we loop through it to fetch corresponding details from other tables.
There are some stored procs where in I have to loop through multiple Result sets:

for ex:

Step 1: Fetch results from table 1 into ResultSet1

Step 2 : While not end of ResultSet1

Step 3: fetch results from table 2 into ResultSet2

Step 4: While not end of ResultSet2

Step 5: 1) fetch from table 3
2) Write to output file

End of While in Step 4
End of While inStep 2


This works fine when I have limited amount of data in the tables.
When the tables have huge volumes of data , the SP fails with SQLSTATE = HY010 (Function Sequence Error) .
Another observation is that the same SPs work fine when there are no multiple results sets involved. Results in successful execution irrespective of the time the SP takes to fetch the results.

Any ideas on this will be of great help.

Thanks
Shilpa

We had the same error with the resultset executed from java code. To overcome this we had set cursor hold property value in client application server file(ie. WAS 3.2). And it get resolved.

I think in odbc/cli file some property is there, I am not sure abt this. Pls. check with IBM help.

thanx
ms