Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

CURSOR WITH HOLD behavior when Roll Back

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: CURSOR WITH HOLD behavior when Roll Back
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    Post subject:
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    Post subject: Reply to: CURSOR WITH HOLD behavior when Roll Back
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    Post subject:
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    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 Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts CICS NEW SIT PARAMETERS CICS BEHAVIOR... Kyle Carroll CICS 0 Fri Nov 04, 2016 11:55 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us