We are using Java Stored Procs to pull data from application DB2
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