ROLLBACK statement

The ROLLBACK statement can be used to end a unit of recovery and back out all the relational database changes that were made by that unit of recovery. If relational databases are the only recoverable resources used by the application process, ROLLBACK also ends the unit of work. ROLLBACK can also be used to back out only the changes made after a savepoint was set within the unit of recovery without ending the unit of recovery. Rolling back to a savepoint enables selected changes to be undone.

Invocation for ROLLBACK

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. It can be used in the IMS or CICS® environment only if the TO SAVEPOINT clause is specified.

Authorization for ROLLBACK

None required.

Syntax for ROLLBACK

Read syntax diagramSkip visual syntax diagramROLLBACKWORKTO SAVEPOINTsavepoint-name

Description for ROLLBACK

When ROLLBACK is used without the SAVEPOINT clause, the unit of recovery in which the ROLLBACK statement is executed is ended and a new unit of recovery is started.

All changes that are made by the following statements during the unit of recovery are backed out:

  • ALTER
  • COMMENT
  • CREATE
  • DELETE
  • DROP
  • EXPLAIN
  • GRANT
  • INSERT
  • LABEL
  • MERGE
  • REFRESH TABLE
  • RENAME
  • REVOKE
  • SELECT INTO with an SQL data change statement
  • select-statement with an SQL data change statement
  • TRUNCATE when the IMMEDIATE clause is not specified
  • UPDATE

ROLLBACK without the TO SAVEPOINT clause also causes the following actions to occur:

  • All locks that are implicitly acquired during the unit of recovery are released. See LOCK TABLE statement for an explanation of the duration of explicitly acquired locks.
  • All cursors are closed, all prepared statements are destroyed, and any cursors that are associated with the prepared statements are invalidated.
  • All rows and all logical work files of every created temporary table of the application process are deleted. (All the rows of a declared temporary table are not implicitly deleted. As with base tables, any changes that are made to a declared temporary table during the unit of recovery are undone to restore the table to its state at the last commit point.)
  • All LOB locators, including those that are held, are freed.
TO SAVEPOINT
Specifies that the unit of recovery is not to be ended and that only a partial rollback (to a savepoint) is to be performed. If a savepoint name is not specified, rollback is to the last active savepoint. For example, if in a unit of recovery, savepoints A, B, and C are set in that order and then C is released, ROLLBACK TO SAVEPOINT causes a rollback to savepoint B.
savepoint-name
Identifies the savepoint to which to roll back. The name must identify a savepoint that exists at the current server.

All database changes (including changes made to a declared temporary tables but excluding changes made to created temporary tables) that were made after the savepoint was set are backed out. Changes that are made to created temporary tables are not logged and are not backed out; a warning is issued instead. (A warning is also issued when a created temporary table is changed and there is an active savepoint.)

In addition, none of the following items are backed out:

  • The opening or closing of cursors
  • Changes in cursor positioning
  • The acquisition and release of locks
  • The caching of the rolled back statements

Any savepoints that are set after the one to which rollback is performed are released. The savepoint to which rollback is performed is not released.

ROLLBACK with or without the TO SAVEPOINT clause has no effect on connections.

Notes for ROLLBACK

The following information applies only to rolling back all changes in the unit of recovery (the ROLLBACK statement without the TO SAVEPOINT clause):

  • Stored procedures. The ROLLBACK statement cannot be used if the procedure is in the calling chain of a user-defined function or a trigger or if Db2 is not the commit coordinator.
  • IMS or CICS. Using a ROLLBACK to SAVEPOINT statement in an IMS or CICS environment only rolls back Db2 resources. Any other recoverable resources updated in the environment are not rolled back. To do a rollback operation in these environments, SQL programs must use the call prescribed by their transaction manager. The effect of these rollback operations on Db2 data is the same as that of the SQL ROLLBACK statement.

    A rollback operation in an IMS or CICS environment might handle the closing of cursors that were declared with the WITH hold option differently than the SQL ROLLBACK statement does. If an application requests a rollback operation from CICS or IMS, but no work has been performed in Db2 since the last commit point, the rollback request will not be broadcast to Db2. If the application had opened cursors using the WITH HOLD option in a previous unit of work, the cursors will not be closed, and any prepared statements associated with those cursors will not be destroyed.

  • Implicit rollback operations: In all Db2 environments, the abend of a process is an implicit rollback operation.

ROLLBACK and non-LOB table spaces that are not logged: If ROLLBACK is executed for a unit of work that includes changes to a non-LOB table space that is not logged (specifies the NOT LOGGED attribute), that table space is marked RECOVER-pending and the table space is placed in the logical page list. The table space is therefore not available after the rollback operation completes. For more information about the RECOVER utility, see RECOVER.

ROLLBACK and declared global temporary tables that are not logged: When NOT LOGGED is specified on a declared global temporary table and Db2 must roll back because of an error such as a duplicate key error, rows are deleted or preserved depending on the option that was specified for ON ROLLBACK.

If the ON ROLLBACK DELETE ROWS option was specified for the table, insert, update, and delete activity is not logged. During a ROLLBACK or ROLLBACK TO SAVEPOINT operation, if the table was updated since the last COMMIT statement, all rows are deleted from the table. Any open cursors for the table do not have positions. If the declaration of the declared global temporary table was not committed, the declaration of the table is rolled back.

If the ON ROLLBACK PRESERVE ROWS option was specified for the table, insert, update, and delete activity is not logged. During a ROLLBACK or ROLLBACK TO SAVEPOINT operation, all rows in the table are preserved regardless of any updates to the table since the last COMMIT statement. Any open cursors for the table do not have positions. If the declaration of the declared global temporary table was not committed, the declaration of the table is rolled back.

Effect of ROLLBACK on global variables: Global variables are not controlled at the transaction level. Issuing a ROLLBACK statement does not affect the contents of a global variable.

Effect of ROLLBACK on insert operations: If execution of an INSERT statement results in creation of a data set in a table space, and the INSERT operation is rolled back, the inserted data is deleted, but the data set is not deleted.

Start of changeEffect of ROLLBACK on prepared dynamic statements: All prepared dynamic statements in a package that is bound with KEEPDYNAMIC(YES) are kept past rollback points.End of change

Examples for ROLLBACK

Example 1: Roll back all Db2 database changes made since the unit of recovery was started. 
   ROLLBACK WORK;
Example 2: After a unit of recovery started, assume that three savepoints A, B, and C were set and that C was released: 
   ...
   SAVEPOINT A ON ROLLBACK RETAIN CURSORS;
   ...
   SAVEPOINT B ON ROLLBACK RETAIN CURSORS;
   ...
   SAVEPOINT C ON ROLLBACK RETAIN CURSORS;
   ...
   RELEASE SAVEPOINT C;
   ...
Roll back all Db2 database changes only to savepoint A: 
   ROLLBACK WORK TO SAVEPOINT A;
If a savepoint name was not specified (that is, ROLLBACK WORK TO SAVEPOINT), the rollback would be to the last active savepoint that was set, which is B.