ROLLBACK
Valid in: SQL, ESQL, DBProc, OpenAPI, ODBC, JDBC, .NET
The ROLLBACK statement rolls back the current transaction.
Note: This statement has additional considerations when used in a distributed environment. For more information, see the Star User Guide.
The ROLLBACK statement has the following format:
[EXEC SQL] ROLLBACK [WORK] [TO savepoint_name];
TO savepoint_name
Rolls back only those changes made after the specified savepoint. The transaction is not terminated. Processing resumes with the statement following the ROLLBACK TO savepointname statement. If autocommit is enabled, the ROLLBACK statement has no effect.
If ROLLBACK is issued without the optional TO clause, the statement terminates the transaction and rolls back any changes made by the transaction.
Note: The ROLLBACK TO savepoint statement is not permitted when an X100 operation has occurred after the savepoint.
WORK
Is an optional keyword included for compatibility with the ISO and ANSI SQL standards.
The ROLLBACK statement aborts part or all of the current transaction.
Only the ROLLBACK statement without the optional TO clause can be used in database procedures, and only in procedures that are directly executed. A database procedure that is invoked by a rule cannot contain either version of the ROLLBACK statement.
If a database event registration is removed (using the REMOVE DBEVENT statement), and the transaction is subsequently rolled backed, the database event registration is not restored.
Embedded Usage
In addition to aborting all or part of the current transaction, an embedded ROLLBACK statement:
• Closes all open cursors
• Discards all statements that were prepared in the current transaction
The TO savepoint_name clause cannot be included if there are open cursors in the transaction. Also, when a savepoint is specified in the ROLLBACK statement, the DBMS Server discards only those statements that were prepared after the declaration of the specified savepoint.
Savepoint_name cannot be specified using a host language variable.
Usage in OpenAPI, ODBC, JDBC, .NET
In OpenAPI, ODBC, JDBC, and .NET, the rollback function is supported through the interface-specific mechanism, for example, IIapi_rollback() for OpenAPI.
Permissions
This statement is available to all users.
Locking
If the ROLLBACK statement is issued without the TO savepoint option, the statement terminates the transaction and releases all locks held during the transaction. If the TO savepoint_name option is included, no locks are released.
Performance
Executing a rollback undoes some or all of the work done by a transaction. The time required to do this is generally the same amount of time taken to perform the work.
Related Statements
COMMIT
Last modified date: 11/09/2022