Unanswered: SQL state HY010 - Function Sequence Error
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:
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.