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.

    Requires Free Membership to View

    Login

    When you register, you'll begin receiving targeted emails from my team of award-winning writers. Our goal is to keep you informed on the hottest data and information management trends today.

    Hannah Smalltree, Editorial Director

    By submitting your registration information to SearchDataManagement.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchDataManagement.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 .VO7aaqqaAFk.0@/search390>discussion forums.

This was first published in December 2003