Undoing selected changes within a unit of work by using savepoints

Savepoints enable you to undo selected changes within a unit of work. Your application can set any number of savepoints and then specify a specific savepoint to indicate which changes to undo within the unit of work.

Procedure

To undo selected changes within a unit of work by using savepoints:

  1. Set any savepoints by using SQL SAVEPOINT statements. Savepoints set a point to which you can undo changes within a unit of work.

    Consider the following abilities and restrictions when setting savepoints:

    • You can set a savepoint with the same name multiple times within a unit of work. Each time that you set the savepoint, the new value of the savepoint replaces the old value.
    • If you do not want a savepoint to have different values within a unit of work, use the UNIQUE option in the SAVEPOINT statement. If an application executes a SAVEPOINT statement with the same name as a savepoint that was previously defined as unique, an SQL error occurs.
    • If you set a savepoint before you execute a CONNECT statement, the scope of that savepoint is the local site. If you set a savepoint after you execute the CONNECT statement, the scope of that savepoint is the site to which you are connected.
    • When savepoints are active, which they are until the unit of work completes, you cannot access remote sites by using three-part names or aliases for three-part names. You can, however, use DRDA access with explicit CONNECT statements.
    • You cannot use savepoints in global transactions, triggers, user-defined functions, or stored procedures that are nested within triggers or user-defined functions.
  2. Specify the changes that you want to undo within a unit of work by using the SQL ROLLBACK TO SAVEPOINT statement.

    DB2® undoes all changes since the specified savepoint. If you do not specify a savepoint name, DB2 rolls back work to the most recently created savepoint.

  3. Optional: If you no longer need a savepoint, delete it by using the SQL RELEASE SAVEPOINT statement.
    Recommendation: If you no longer need a savepoint before the end of a transaction, release it. Otherwise, savepoints are automatically released at the end of a unit of work. Releasing savepoints is essential if you need to use three-part names to access remote locations, because you cannot perform this action while savepoints are active.

Examples

Rolling back to the most recently created savepoint: When the ROLLBACK TO SAVEPOINT statement is executed in the following code, DB2 rolls back work to savepoint B. 
EXEC SQL SAVEPOINT A;
...
EXEC SQL SAVEPOINT B;
...
EXEC SQL ROLLBACK TO SAVEPOINT;
Setting savepoints during distributed processing: An application performs the following tasks:
  1. Sets savepoint C1.
  2. Does some local processing.
  3. Executes a CONNECT statement to connect to a remote site.
  4. Sets savepoint C2.
Because savepoint C1 is set before the application connects to a remote site, savepoint C1 is known only at the local site. However, because savepoint C2 is set after the application connects to the remote site, savepoint C2 is known only at the remote site.
Setting multiple savepoints with the same name: Suppose that the following actions occur within a unit of work:
  1. Application A sets savepoint S.
  2. Application A calls stored procedure P.
  3. Stored procedure P sets savepoint S.
  4. Stored procedure P executes the following statement: ROLLBACK TO SAVEPOINT S
When DB2 executes the ROLLBACK statement, DB2 rolls back work to the savepoint that was set in the stored procedure, because that value is the most recent value of savepoint S.