DB2 10.5 for Linux, UNIX, and Windows

SAVEPOINT statement

Use the SAVEPOINT statement to set a savepoint within a transaction.

Invocation

This statement can be imbedded in an application program (including a procedure) or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-SAVEPOINT--savepoint-name--+--------+------------------------>
                              '-UNIQUE-'   

                               .-ON ROLLBACK RETAIN LOCKS-.   
>--ON ROLLBACK RETAIN CURSORS--+--------------------------+----><

Description

savepoint-name
Specifies the name of a savepoint. The specified savepoint-name cannot begin with 'SYS' (SQLSTATE 42939). If a savepoint by this name has already been defined as UNIQUE within this savepoint level, an error is returned (SQLSTATE 3B501).
UNIQUE
Specifies that the application does not intend to reuse this savepoint name while the savepoint is active within the current savepoint level. If savepoint-name already exists within this savepoint level, an error is returned (SQLSTATE 3B501).
ON ROLLBACK RETAIN CURSORS
Specifies system behavior upon rollback to this savepoint with respect to open cursor statements processed after the SAVEPOINT statement. This clause indicates that, whenever possible, the cursors are unaffected by a rollback to savepoint operation. For situations where the cursors are affected by the rollback to savepoint, see "ROLLBACK".
ON ROLLBACK RETAIN LOCKS
Specifies system behavior upon rollback to this savepoint with respect to locks acquired after the setting of the savepoint. Locks acquired since the savepoint are not tracked, and are not rolled back (released) upon rollback to the savepoint.

Rules

Notes

Example

Perform a rollback operation for nested savepoints. First, create a table named DEPARTMENT. Insert a row before starting SAVEPOINT1; insert another row and start SAVEPOINT2; then, insert a third row and start SAVEPOINT3.
   CREATE TABLE DEPARTMENT (
     DEPTNO   CHAR(6),
     DEPTNAME VARCHAR(20),
     MGRNO    INTEGER)

     INSERT INTO DEPARTMENT VALUES ('A20', 'MARKETING', 301)

     SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS

     INSERT INTO DEPARTMENT VALUES ('B30', 'FINANCE', 520)

     SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS

     INSERT INTO DEPARTMENT VALUES ('C40', 'IT SUPPORT', 430)

     SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS

     INSERT INTO DEPARTMENT VALUES ('R50', 'RESEARCH', 150)
At this point, the DEPARTMENT table exists with rows A20, B30, C40, and R50. If you now issue:
   ROLLBACK TO SAVEPOINT SAVEPOINT3
row R50 is no longer in the DEPARTMENT table. If you then issue:
   ROLLBACK TO SAVEPOINT SAVEPOINT1
the DEPARTMENT table still exists, but the rows inserted since SAVEPOINT1 was established (B30 and C40) are no longer in the table.