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.
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Craig Mullins explains various definitions of a data mart and sheds some light on the complexity of its relation to a data warehouse. Continue Reading