any option to keep cursor open after rollback

vaibhavbleo · New User Joined: 25 Sep 2007 Posts: 7 Location: pune

Hi all,

Cursors in DB2 follows the ANSI SQL standard of closing open cursors whenever a COMMIT or ROLLBACK statement is issued. But cursors that are declared with the WITH HOLD option remain open after a COMMIT statement is issued. Here all open cursors are closed when a ROLLBACK statement is issued.

If we are processing something in a cursor and inside the cursor for every record we have to commit or rollback and then go to the next record.

What will happen if i have to rollback on the 50th row and cursor has 500 rows to process?Here it closes the cursor itself since rollback happened.

What should i do to retain the cursor OPEN and the cursor position same as before the ROLLBACK happened. Some body help me on this..

dbzTHEdinosauer · Posted: Wed Oct 08, 2008 5:24 pm

sounds as if you need restart logic.

Suresh Ponnusamy · Posted: Thu Oct 09, 2008 3:16 am

Hi

If I am correct, after issuing ROLLBACK command, you want to fetch the next record.

If so, we can move the key value to a variable and re-open the cursor with the key value greater than the Stored Value.

Record_value
1
2
3
4
5
6

Processing
1
2
3
Rollback

Here the cursor is closed.

Re-Open the cursor again with

WHERE RECORD_VALUE > 3.

Hope this helps.

murugan_mf · Posted: Thu Oct 09, 2008 5:19 pm

You can declare the cursor using "WITH HOLD" Optiom so that it would not close even if you issued rollback or commit statements.

dbzTHEdinosauer · Posted: Thu Oct 09, 2008 6:33 pm

Murugan,

I don't think there is any way to prevent a cursor from closing due to rollback.

Suresh Ponnusamy · Posted: Thu Oct 09, 2008 7:51 pm

Hi Vaibhav,

Which application you are using.. For ex. IMS, CICS or Stored Procedure etc.

Please refer the below given point.

A rollback operation in an IMS or CICS environment might handle the closing of cursors that were declared with the WITH hold option differently than the SQL ROLLBACK statement does. If an application requests a rollback operation from CICS or IMS, but no work has been performed in DB2 since the last commit point, the rollback request will not be broadcast to DB2. If the application had opened cursors using the WITH HOLD option in a previous unit of work, the cursors will not be closed, and any prepared statements associated with those cursors will not be destroyed.

Since you are having COMMIT after every record FETCH in your application, each record FETCH would considered as a Logical Unit of Work and as per the above statement, the cursor should not be closed.

Suggestions are welcome.

mkarthikeyan · New User Joined: 07 Aug 2008 Posts: 34 Location: Bangalore

yes. You can hold the resource excepts the locks it held.

Specifies a hold on resources. If specified, currently open cursors are not closed and all resources acquired during the unit of work, except locks on the rows of tables, are held. Locks on specific rows implicitly acquired during the unit of work, however, are released.

EXEC SQL
ROLLBACK HOLD
END-EXEC