ROLLBACK WORK statement
Use the ROLLBACK WORK statement to cancel all or part of the current transaction intentionally, undoing any changes that occurred since the beginning of the transaction, or between the ROLLBACK WORK statement and a specified or default savepoint.
Syntax
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| savepoint | Name of the savepoint that delimits the scope of the rollback | Must exist in the current transaction. | Identifier |
Usage
The ROLLBACK WORK statement is valid only in databases that support transaction logging. Only logged operations can be rolled back. Use ROLLBACK WORK only at the end of a multistatement operation.
The ROLLBACK WORK statement restores the database to its state that existed before the cancelled portion of the transaction began.
In a database that is not ANSI-compliant, the BEGIN WORK statement starts a transaction. You can end a transaction with the COMMIT WORK statement or cancel all or part of the transaction with the ROLLBACK WORK statement. If you issue the ROLLBACK WORK statement when no transaction is pending in a database that is not ANSI-compliant, HCL OneDB™ issues an error.
In an ANSI-compliant database, multistatement transactions are implicit. You do not need to mark the beginning of a transaction with the BEGIN WORK statement. You only need to mark the end of each transaction with a COMMIT WORK statement or cancel the transaction with a ROLLBACK WORK statement. If you issue the ROLLBACK WORK statement when no transaction is pending, the statement is accepted but has no effect.
The ROLLBACK WORK statement restores the database to the state that existed before the cancelled portion of the transaction began. Unless you include the TO SAVEPOINT keywords, ROLLBACK WORK cancels the entire transaction.
The ROLLBACK WORK statement releases all row and table locks that the cancelled transaction holds.
In and SPL, the ROLLBACK WORK statement closes all open cursors except those that are declared as hold cursors by including the WITH HOLD keywords. Hold cursors remain open after a transaction is committed or rolled back.
If you use the ROLLBACK WORK statement within an SPL routine that the WHENEVER statement calls, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. This step prevents the program from looping if the ROLLBACK WORK statement encounters an error or a warning.
If a program terminates abnormally, the current transaction is implicitly rolled back.