IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

CURSOR WITH HOLD behavior when Roll Back


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Mon Dec 27, 2010 9:12 pm
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..

Thanks,
Santosh
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Dec 27, 2010 9:35 pm
Reply with quote

you need to save the last 'key' that you processed on the outter cursor,
then reopen (you know that you have to because you issued a roll-back)
with key > your saved key.


you do not want the cursor positioned as before the rollback, you want the next row.

if you reopen with last key processed you will get into a loop of ROLLBACK, reopen.


you should have a where clause for the cursor declare which you populate before open. key > host-variable.

and as a last thought,
forget that ANSII standard garbage.

you are working with DB2, follow the manual.......................
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Tue Dec 28, 2010 10:41 am
Reply with quote

or alternatively...you can laod the cursor rows in a cobol table and close the cursor. After this you can do whatever with rows, the cursor wont be there to bug you.


dbzTHEdinosauer-
your veiw point is solicitated on this approach, as I have seen this way in many programs in my shop.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Dec 28, 2010 2:17 pm
Reply with quote

my experience has been that I did not need to code for ROLLBACKs unless -9xx SQLcodes were returned. some of the -9xx series include a ROLLBACK, others suggest that you should. an example is a TIMEOUT (w/o rollback). whoever receives this sql code should issue a rollback so that the other transaction can complete.

all this talk of ROLLBACK is based on my confusion of why the TS is encountering ROLLBACK scenarios. Why?

direct answer to your question, I usually only load a COBOL table if there will be multiple accesses to the same row.
a seq fetch and process loop,
i would process from the CURSOR.

Now, I have only worked on Banking, Insurance and Transportation applications, BUT I have never needed to issue a ROLLBACK based on application conditions while processing a CURSOR. Single Row selects, web processes, ATM, swift transactions - yes - if the other end times out or my process cannot react fast enough, I need to ROLLBACK my applilcation changes (example: ATM withdrawal that times out. If swift tells me to CANCEL the transaction because I was to slow, I need to ROLLBACK - but there is no cursor involved.)

I question the reason for the ROLLBACK.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Back Page print direction (Duplex Pri... JCL & VSAM 3
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
No new posts Restart logic by using cursor name in... DB2 1
No new posts Seeking Resolution for SQKCODE -991 o... DB2 2
No new posts Multiple rows within Cursor when Coun... DB2 14
Search our Forums:

Back to Top