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..
Joined: 20 Oct 2006 Posts: 6970 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.)