SAVEPOINT ON ROLLBACK RETAIN CURSORS issue

cgilker · New User Joined: 02 Mar 2022 Posts: 6 Location: USA

I have a batch program, running in JCL under IKJEFT1A.

I am issuing a SAVEPOINT <name> ON ROLLBACK RETAIN CURSORS before I open my cursor.

Then I open, fetch, and do some processing. After each fetched row, I either issue a COMMIT or a ROLLBACK.

If I issue a ROLLBACK, I then issue another SAVEPOINT <name> ON ROLLBACK RETAIN CURSORS.

Otherwise, I just COMMIT.

Then I fetch the next row.

In the ROLLBACK / SAVEPOINT scenario, the next fetch is returning SQLCODE = -501 (i.e. the cursor is not open)

I am confused. I expected the cursor to remain open. Am I missing something obvious here?

cgilker · New User Joined: 02 Mar 2022 Posts: 6 Location: USA

I may have answered my own question, so apologies in advance if so.

Apparently, my ROLLBACK should be qualified as TO SAVEPOINT <name>.

Since I am not qualifying it that way, I am guessing that DB2 is doing a rollback to the last commit point, which is closing my cursor.

I will test the code again with ROLLBACK TO SAVEPOINT <name> as soon as I can get the necessary test conditions established.

But, in the meantime, does this explain my issue?