Statements › ROLLBACK

ROLLBACK

The ROLLBACK transaction management statement performs the following tasks:

• Cancels changes made to the database during the current transaction
• Ends the transaction
• Optionally ends the SQL session

Authorization

None required.

Syntax

►►─── ROLLBACK work ─┬───────────┬────────────────────────────────────────────►◄
                     └─ RELEASE ─┘

Parameters

RELEASE

Directs CA IDMS to end the current SQL session and the current transaction after canceling the changes to the database.

The RELEASE parameter is a CA IDMS extension of the SQL standard.

Usage

Effect of a ROLLBACK on an SQL Session

A ROLLBACK statement has the following impact on the SQL session and its transaction:

• Rolls back all changes made by the session
• Releases all locks
• Closes all open cursors
• Drops all temporary tables
• Deletes all dynamically compiled statements
• Terminates the SQL session CA IDMS connected it automatically or if RELEASE is specified.

Effect of Transaction Sharing

If more than one database session is sharing the SQL session's transaction, the changes made by all sharing sessions are immediately rolled back. All sharing sessions other than the one through which the ROLLBACK statement was issued are flagged to indicate that they must also issue a ROLLBACK. If the next statement issued by each of these sessions is not a ROLLBACK, it will receive an error:

• For SQL, the application receives an SQLCODE of -5 (transaction failure) and an SQLRSN of 1088 (transaction forced to backout).
• For navigational DML, the run unit is terminated and an error status of xx19 is returned to the application.

Example

Canceling Database Changes

The following ROLLBACK statement cancels the uncommitted changes to the database made during the current transaction and ends both the transaction and the current SQL session:

EXEC SQL
   ROLLBACK RELEASE
END-EXEC

More Information

• For more information about committing changes to the database before ending a transaction, see COMMIT.
• For more information about ending an SQL session, see RELEASE.
• For more information about managing or sharing transactions, see the CA IDMS SQL Programming Guide.