View previous topic :: View next topic
|
Author |
Message |
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008 Posts: 132 Location: IBM
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
|