ROLLBACK

The ROLLBACK statement is used to end a unit of work and to back out the database changes that were made by that unit of work.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

ROLLBACK is not allowed in a trigger program if the trigger program and the triggering program are run under the same commitment definition. ROLLBACK is not allowed in an external procedure if the external procedure and the program that issued the CALL statement run under the same commitment definition.

Authorization

None required.

Syntax

             .-WORK-.
>>-ROLLBACK--+------+----+-------+-----------------------------><
                         '-HOLD--'
 

Description

The ROLLBACK statement ends the unit of work in which it is executed and starts a new unit of work. All changes made by ALTER, CALL, COMMENT ON, CREATE, DELETE, DROP (except for DROP COLLECTION), GRANT, INSERT, LABEL ON, RENAME, REVOKE, and UPDATE statements executed during the unit of work are backed out.

WORK

ROLLBACK WORK has the same effect as ROLLBACK.

HOLD

Indicates a hold on resources. If specified, currently open cursors are not closed and all resources acquired during the unit of work, except locks on the rows of tables, are held. Locks on specific rows implicitly acquired during the unit of work, however, are released.

The following are true under this unit of work's commitment definition if HOLD is omitted:

• Cursors opened under this unit of work's commitment definition are closed.
• Table locks acquired by the LOCK TABLE statement under this unit of work's commitment definition are released.

At the end of a ROLLBACK HOLD, the cursor position is the same as it was at the start of the unit of work.

Notes

The ending of the default activation group causes an implicit rollback. Thus, an explicit COMMIT or ROLLBACK statement should be issued before the end of the default activation group.

A ROLLBACK is automatically performed when:

1. The default activation group ends without a final COMMIT being issued.
2. A failure occurs that prevents the activation group from completing its work (for example, a power failure).

   If the unit of work is in the prepared state because a COMMIT was in progress when the failure occurred, a rollback is not performed. Instead, resynchronization of all the connections involved in the unit of work will occur. For more information, see the book Backup and Recovery, SC41-5304-04.

3. A failure occurs that causes a loss of the connection to an application server (for example, a communications line failure).

   If the unit of work is in the prepared state because a COMMIT was in progress when the failure occurred, a rollback is not performed. Instead, resynchronization of all the connections involved in the unit of work will occur. For more information, see the book Backup and Recovery, SC41-5304-04.

4. A nondefault activation group ends abnormally.

A unit of work may include the processing of up to and including 4 million rows, including rows retrieved during a SELECT INTO or FETCH statement⁴⁵, and rows inserted, deleted, or updated as part of INSERT, DELETE, and UPDATE operations. ⁴⁶

The commit and rollback operations do not affect the DROP COLLECTION statement, and this statement is not, therefore, allowed in an application program that also specifies COMMIT(*CHG), COMMIT(*CS), COMMIT(*ALL), or COMMIT(*RR).

See the notes to the COMMIT statement for information on determining which commitment definition is used by SQL.

Any cursors associated with a prepared statement that is destroyed cannot be opened until the statement is prepared again. ROLLBACK has no effect on the state of connections.

If, within a unit of work, a CLOSE is followed by a ROLLBACK, all changes made within the unit of work are backed out. The CLOSE itself is not backed out and the file is not reopened.

Example

See the examples under COMMIT on page *** for examples using the ROLLBACK statement.

⁴⁵

Unless you specified COMMIT(*CHG) or COMMIT(*CS), in which case these rows are not included in this total.

⁴⁶

This limit also includes:

• Any records accessed or changed through files opened under commitment control through high-level language file processing
• Any rows deleted, updated, or inserted as a result of a trigger or CASCADE, SET NULL, or SET DEFAULT referential integrity delete rule.