Problem solve
Get help with specific problems with your technologies, process and projects.
Rollback changes coming undone
I have a cursor that selects "n" rows from a table based on certain conditions. For each of the rows selected, certain functions have to be performed that updates a few tables. If there is any error while processing, the updates to the table are rolled back and continue processing with the next fetched row. But, rollback is forcing the cursor to close, which gives a -501 error. How do I handle this? Is there any way to avoid the cursor from closing, even as a rollback is issued? Please advise.
Your solution will be to use an application SAVEPOINT. This feature was added to DB2 for OS/390 under Version 7. You can think of a SAVEPOINT as a sub-UOW (unit of work) "stability" point. You can code application logic to undo any data modifications and database schema changes that were made since the application set the SAVEPOINT. Application development should be more efficient using SAVEPOINTs because you will not need to include contingency and what-if logic in your application code.
Issuing a SAVEPOINT does not COMMIT work to DB2. It is simply a mechanism for registering milestones within a transaction or program. Let's learn by example. Consider the following pseudo-code:
SAVEPOINT POINTX ON ROLLBACK RETAIN CURSORS; ...Subsequent processing... ROLLBACK TO SAVEPOINT POINTX;
The ROLLBACK will cause any data or schema changes made in the "subsequent processing" to be undone. It is permissible to code multiple SAVEPOINTs within a UOW and you can ROLLBACK to any SAVEPOINT (as long as you do not reuse the SAVEPOINT name). The UNIQUE key word can be specified to ensure that the SAVEPOINT name is not reused within the unit of recovery.
There are two clauses that can be specified to further define the nature of the SAVEPOINT when a ROLLBACK is issued: RETAIN CURSORS -- specifies that any cursors that are opened after the SAVEPOINT is set are not tracked, and will not be closed when rolling back to that SAVEPOINT. You will want to use this. RETAIN LOCKS -- specifies that any locks that are acquired after the SAVEPOINT is set are not tracked, and will not be released when rolling back to the SAVEPOINT. Keep in mind though, that even if RETAIN CURSORS is specified some of the cursors may not be usable. For example, if the ROLLBACK removes a row (that is, rolls back an INSERT) on which the cursor was positioned, an error will arise.Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our [email protected]/search390>discussion forums.
