Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
any option to keep cursor open after rollback

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vaibhavbleo

New User


Joined: 25 Sep 2007
Posts: 7
Location: pune

PostPosted: Wed Oct 08, 2008 4:26 pm    Post subject: any option to keep cursor open after rollback
Reply with quote

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..
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Oct 08, 2008 5:24 pm    Post subject:
Reply with quote

sounds as if you need restart logic.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Oct 09, 2008 3:16 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Thu Oct 09, 2008 5:19 pm    Post subject:
Reply with quote

You can declare the cursor using "WITH HOLD" Optiom so that it would not close even if you issued rollback or commit statements.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Oct 09, 2008 6:33 pm    Post subject:
Reply with quote

Murugan,

I don't think there is any way to prevent a cursor from closing due to rollback.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Oct 09, 2008 7:51 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
mkarthikeyan

New User


Joined: 07 Aug 2008
Posts: 34
Location: Bangalore

PostPosted: Tue Nov 04, 2008 12:37 am    Post subject: Reply to: any option to keep cursor open after rollback
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Where is output from PARM ABEXIT (SNA... packerm CA Products 3 Thu Sep 28, 2017 4:06 pm
No new posts MQ command in option 6 of ISPF! Vignesh Sid All Other Mainframe Topics 3 Tue Sep 26, 2017 6:01 pm
No new posts Reply MQ open error Vignesh Sid COBOL Programming 6 Thu Sep 07, 2017 6:02 pm
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us