SAVEPOINT

The SAVEPOINT statement sets a savepoint within a unit of recovery to identify a point in time within the unit of recovery to which relational database changes can be rolled back.

Invocation

This statement can be imbedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax


>>-SAVEPOINT--savepoint-name--+--------+------------------------>
                              '-UNIQUE-'   

                              (1)   
>--ON ROLLBACK RETAIN CURSORS----------------------------------->

                              (1)     
   .-ON ROLLBACK RETAIN LOCKS-----.   
>--+------------------------------+----------------------------><

Notes:

These clauses can be specified in either order.

Description

savepoint-name: Names the savepoint. savepoint-name must not begin with 'SYS'.
UNIQUE: Specifies that the application program cannot reuse the savepoint name within the unit of recovery. An error occurs if a savepoint with the same name as savepoint-name already exists within the unit of recovery.
Omitting UNIQUE indicates that the application can reuse the savepoint name within the unit of recovery. If svpt-name identifies a savepoint that already exists within the unit of recovery and the savepoint was not created with the UNIQUE option, the existing savepoint is destroyed and a new savepoint is created. Destroying a savepoint to reuse its name for another savepoint is not the same as releasing the savepoint. Reusing a savepoint name destroys only one savepoint. Releasing a savepoint with the RELEASE SAVEPOINT statement releases the savepoint and all savepoints that have been subsequently set.
ON ROLLBACK RETAIN CURSORS: Specifies that any cursors that are opened after the savepoint is set are not tracked, and thus, are not closed upon rollback to the savepoint. Although these cursors remain open after rollback to the savepoint, they might not be usable. For example, if rolling back to the savepoint causes the insertion of a row on which the cursor is positioned to be rolled back, using the cursor to update or delete the row results in an error.
ON ROLLBACK RETAIN LOCKS: Specifies that any locks that are acquired after the savepoint is set are not tracked, and thus, are not released on rollback to the savepoint. ON ROLLBACK RETAIN LOCKS is the default behavior.

Example

Assume that you want to set three savepoints at various points in a unit of recovery. Name the first savepoint A and allow the savepoint name to be reused. Name the second savepoint B and do not allow the name to be reused. Because you no longer need savepoint A when you are ready to set the third savepoint, reuse A as the name of the savepoint.

   SAVEPOINT A ON ROLLBACK RETAIN CURSORS;
       ⋮
   SAVEPOINT B UNIQUE ON ROLLBACK RETAIN CURSORS;
       ⋮
   SAVEPOINT A ON ROLLBACK RETAIN CURSORS;