RELEASE SAVEPOINT statement

Use the RELEASE SAVEPOINT statement to destroy a specified savepoint. The RELEASE SAVEPOINT statement is compliant with the ANSI/ISO standard for SQL.

Syntax


1  RELEASE SAVEPOINT savepoint
Element Description Restrictions Syntax
savepoint Name of the savepoint to be destroyed Must exist in the current savepoint level Identifier

Usage

Restriction: After this statement executes successfully, rollback to the specified savepoint (or to any other savepoint between the RELEASE SAVEPOINT statement and the specified savepoint) is no longer possible.

The RELEASE SAVEPOINT statement destroys the specified savepoint. Any savepoints set between that savepoint and the RELEASE SAVEPOINT statement in the current savepoint level are also destroyed. Any other savepoints, however, that were set earlier than the specified savepoint in the current savepoint level continue to be active.

The RELEASE SAVEPOINT statement fails with an error in the following contexts:
  • No SQL transaction is open.
  • No savepoint with the specified name exists in the current savepoint level.
  • The statement is part of a triggered action.
  • The statement is part of an XA transaction.
  • The autocommit transaction mode of the client API is enabled.
  • The statement is part of a cross-server distributed SQL transaction in which one of the participating database servers does not support savepoints.
  • The statement is issued within a UDR that is invoked within a DML statement.

The identifier of any savepoint that RELEASE SAVEPOINT destroys can be reused in a subsequent SAVEPOINT statement of the same savepoint level, even if the released savepoint was set by a SAVEPOINT statement that included the UNIQUE keyword.

Because savepoints are program objects, not database objects, the RELEASE SAVEPOINT statement has no direct effect on the database or on its system catalog tables. RELEASE SAVEPOINT can affect user tables and the system catalog indirectly, however, if it changes the scope of a subsequent ROLLBACK TO SAVEPOINT operation that cancels uncommitted changes to the database within a different portion of the current savepoint level, as the next example illustrates.

The following program fragment sets and then releases a savepoint called sp45:
BEGIN WORK;
CREATE DATABASE third_base IN db3 WITH BUFFERED LOG;
SAVEPOINT sp46;
CREATE TABLE tab1 ( col1 INT, col2 CHAR(24));
SAVEPOINT sp45 UNIQUE;
...
CREATE TABLE tab2 ( col1 INT8, col2 LVARCHAR(24000));
SAVEPOINT sp44;
...
RELEASE SAVEPOINT sp45; 
ROLLBACK TO SAVEPOINT;
The effect of the RELEASE SAVEPOINT statement in this example is to destroy two savepoints, sp45 and sp44. If the only remaining savepoint in the current savepoint level is sp46, the subsequent ROLLBACK TO SAVEPOINT statement cancels the DDL statements that created tab1 and tab2, and any DML operations on those tables that preceded the ROLLBACK statement. The rollback does not, however, cancel the CREATE DATABASE statement that created the third_base database. Without the RELEASE SAVEPOINT statement, the CREATE TABLE statement that created tab1 would not have been cancelled, because HCL OneDB™ would have treated sp44 as the default.savepoint that the TO SAVEPOINT clause of the ROLLBACK statement references.