Unanswered: Cursor fetch stops fetching at 1000 records
We are using a C program, with embedded SQL, to fetch records from a table in a DB2 database. After fetching 1000 records, we issue a commit. After this, the cursor stops fetching anything. Because of the way the code is written, I don't know what the error message is (it is installed in production), and I can't reproduce the problem in test. The cursor is declared as follows:
EXEC SQL DECLARE MyCursor CURSOR WITH HOLD FOR
There are hundreds of thousands of records in the table. Why does the cursor presumably error out after fetching 1000 records? Here's my version info:
DB2 v22.214.171.1242, Fixpack 6, DB2 Workgroup Server Edition
Windows 2003, service pack 1
The only things I can think of is that on your production system the "WITH HOLD" clause is not there. Either that or you are issuing a rollback at some point after the commit and before the next fetch.
I've just checked the code, and the WITH HOLDS is in the production code, and there are no explicit rollbacks. I feel as though it could be a setting somewhere, since the code hasn't been changed in several years, and this problem just started occurring a couple of months ago. Unfortunately, I don't know what could have changed in the system to cause this. Thanks for your help!
If anyone else has seen this before, please let me know.