ROLLBACK statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Description

The unit of work in which the ROLLBACK statement is executed is terminated and a new unit of work is initiated. All changes made to the database during the unit of work are backed out.

The following statements, however, are not under transaction control, and changes made by them are independent of the ROLLBACK statement:

• SET CONNECTION
• SET ENCRYPTION PASSWORD
• SET EVENT MONITOR STATE
• SET PASSTHRU
  Note: Although the SET PASSTHRU statement is not under transaction control, the passthru session initiated by the statement is under transaction control.
• SET SERVER OPTION
• SET variable
• Assignments to updatable special registers

The generation of sequence and identity values is not under transaction control. Values generated and consumed by the nextval-expression or by inserting rows into a table that has an identity column are independent of issuing the ROLLBACK statement. Also, issuing the ROLLBACK statement does not affect the value returned by the prevval-expression, nor the IDENTITY_VAL_LOCAL function.

Modification of the values of global variables is not under transaction control. ROLLBACK statements do not affect the values assigned to global variables.

TO SAVEPOINT

Specifies that a partial rollback (ROLLBACK TO SAVEPOINT) is to be performed. If no savepoint is active in the current savepoint level (see the Rules section in the description of the SAVEPOINT statement), an error is returned (SQLSTATE 3B502). After a successful rollback, the savepoint continues to exist, but any nested savepoints are released and no longer exist. The nested savepoints, if any, are considered to have been rolled back and then released as part of the rollback to the current savepoint. If a savepoint-name is not provided, rollback occurs to the most recently set savepoint within the current savepoint level.

If this clause is omitted, the ROLLBACK statement rolls back the entire transaction. Furthermore, savepoints within the transaction are released.

savepoint-name

Specifies the savepoint that is to be used in the rollback operation. The specified savepoint-name cannot begin with 'SYS' (SQLSTATE 42939). After a successful rollback operation, the named savepoint continues to exist. If the savepoint name does not exist, an error (SQLSTATE 3B001) is returned. Data and schema changes made since the savepoint was set are undone.

Notes

• All locks held are released on a ROLLBACK of the unit of work. All open cursors are closed. All LOB locators are freed.
• Executing a ROLLBACK statement does not affect either the SET statements that change special register values or the RELEASE statement.
• If the program terminates abnormally, the unit of work is implicitly rolled back.
• Statement caching is affected by the rollback operation.
• The impact on cursors resulting from a ROLLBACK TO SAVEPOINT depends on the statements within the savepoint
  • If the savepoint contains DDL on which a cursor is dependent, the cursor is marked invalid. Attempts to use such a cursor results in an error (SQLSTATE 57007).
  • Otherwise:
    • If the cursor is referenced in the savepoint, the cursor remains open and is positioned before the next logical row of the result table. (A FETCH must be performed before a positioned UPDATE or DELETE statement is issued.)
    • Otherwise, the cursor is not affected by the ROLLBACK TO SAVEPOINT (it remains open and positioned).
• Dynamic SQL statements prepared in a package bound with the KEEPDYNAMIC YES option are kept in the SQL context after a ROLLBACK statement. The statement might be implicitly prepared again, as a result of DDL operations that are rolled back within the unit of work.
• Inactive dynamic SQL statements prepared in a package bound with KEEPDYNAMIC NO are removed from the SQL context after a rollback operation. The statement must be prepared again before it can be executed in a new transaction.
• The following dynamic SQL statements may be active during ROLLBACK:
  • ROLLBACK statement
  • CALL statements under which the ROLLBACK statement was executed
• A ROLLBACK TO SAVEPOINT operation will drop any created temporary tables created within the savepoint. If a created temporary table is modified within the savepoint and that table has been defined as not logged, then all rows in the table are deleted.
• A ROLLBACK TO SAVEPOINT operation will drop any declared temporary tables declared within the savepoint. If a declared temporary table is modified within the savepoint and that table has been defined as not logged, then all rows in the table are deleted.
• All locks are retained after a ROLLBACK TO SAVEPOINT statement.
• All LOB locators are preserved following a ROLLBACK TO SAVEPOINT operation.

Example

   ROLLBACK WORK