ROLLBACK

Invocation

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

None required.

Description

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 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

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.

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.