SQL Reference

ROLLBACK

The ROLLBACK statement is used to back out of the database changes that were made within a unit of work or a savepoint.

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

               .-WORK--.
>>-ROLLBACK----+-------+---------------------------------------->
 
>-----+------------------------------------+-------------------><
      '-TO SAVEPOINT--+-----------------+--'
                      '-savepoint-name--'
 

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 issuing the ROLLBACK statement:

• SET CONNECTION,
• SET CURRENT DEGREE,
• SET CURRENT DEFAULT TRANSFORM GROUP,
• SET CURRENT EXPLAIN MODE,
• SET CURRENT EXPLAIN SNAPSHOT,
• SET CURRENT PACKAGESET,
• SET CURRENT QUERY OPTIMIZATION,
• SET CURRENT REFRESH AGE,
• SET EVENT MONITOR STATE,
• SET PASSTHRU,
• SET PATH,
• SET SCHEMA,
• SET SERVER OPTION.

TO SAVEPOINT

Indicates that a partial rollback (ROLLBACK TO SAVEPOINT) is to be performed. If no savepoint is active, an SQL error is returned (SQLSTATE 3B502). After a successful ROLLBACK, the savepoint continues to exist. If a savepoint-name is not provided, rollback is to the most recently set savepoint.

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

savepoint-name

Indicate the savepoint to which to rollback. After a successful ROLLBACK, the savepoint defined by savepoint-name continues to exist. If the savepoint name does not exist, an error is returned (SQLSTATE 3B001). 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. See the Notes for information.
• Savepoints are not allowed in atomic execution contexts such as atomic compound statements and triggers.
• 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. ¹⁰⁴
    • Otherwise, the cursor is not affected by the ROLLBACK TO SAVEPOINT (it remains open and positioned).
• Dynamically prepared statement names are still valid, although the statement may be implicitly prepared again, as a result of DDL operations that are rolled back within the savepoint.
• A ROLLBACK TO SAVEPOINT operation will drop any declared temporary tables named within the savepoint. If a declared temporary table is modified within the savepoint, 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

Delete the alterations made since the last commit point or rollback.

   ROLLBACK WORK

¹⁰⁴

A FETCH must be performed before a positioned UPDATE or DELETE statement is issued.