SQL Reference

SAVEPOINT

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

Invocation

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

Authorization

None required.

Syntax

>>-SAVEPOINT--savepoint-name----+---------+--------------------->
                                '-UNIQUE--'
 
                                 .-ON ROLLBACK RETAIN LOCKS--.
>----ON ROLLBACK RETAIN CURSORS--+---------------------------+--><
 

Description

savepoint-name

Name of the savepoint.

UNIQUE

Specifying a UNIQUE savepoint indicates that the application does not intend to reuse this savepoint name while the savepoint is active.

ON ROLLBACK RETAIN CURSORS

Specifies system behavior upon rollback to this savepoint with respect to open cursor statements processed after the SAVEPOINT statement. The RETAIN CURSORS clause indicates that, whenever possible, the cursors are unchanged by a rollback to savepoint. 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) on rollback to the savepoint.

Rules

• Savepoints cannot be nested. If a savepoint statement is issued, and there is already an established savepoint present, then an error occurs (SQLSTATE 3B002).

Notes

• The UNIQUE keyword is supported for compatibility with DB2 Universal Database for OS/390. The following describes the behavior on DB2 Universal Database for OS/390.

  If a savepoint named savepoint-name already exists within the transaction, an error is returned (SQLSTATE 3B501). By omitting the UNIQUE clause, the applications assert that this savepoint name may be reused within the transaction. If savepoint-name already exists within the transaction, it will be destroyed and a new savepoint named savepoint-name will be created.

  Destruction of a savepoint by reusing its name for another savepoint is not the same as releasing the old savepoint with the RELEASE SAVEPOINT statement. Destruction of a savepoint by reusing its name destroys just that savepoint. Releasing a savepoint by means of the RELEASE SAVEPOINT statement releases the named savepoint and all savepoints established after the named savepoint.

• Within a savepoint, if a utility, SQL statement, or DB2 command performs intermittent COMMIT statements during processing, then the savepoint will be implicitly released.
• The SQL statement SET INTEGRITY has the same effects as a DDL statement within a savepoint.
• In an application, inserts may be buffered (that is, the application was precompiled with INSERT BUF option). The buffer will be flushed when SAVEPOINT, ROLLBACK, or RELEASE TO SAVEPOINT statements are issued.