SQL TCL ROLLBACK Statement

The ROLLBACK statement is used to cancel a transaction and revert any changes made during that transaction. By issuing a ROLLBACK, all updates, inserts, and deletions made during the transaction are undone, preserving data integrity.

In DB2, the ROLLBACK statement reverses all modifications made to the database in the current transaction. It’s particularly useful in multi-step transactions where an error or problem in one step requires reversing all changes to avoid inconsistencies. Situations where ROLLBACK is useful include:

• Program or system errors that interrupt a transaction.
• Incorrect data updates or modifications.
• User cancellation of a transaction.

Syntax -

ROLLBACK

In a CICS-DB2 environment, the SYNCPOINT ROLLBACK command is used to undo changes across multiple resources, such as DB2 tables, VSAM files, or queues managed by CICS:

Using ROLLBACK Statement in a COBOL Program

In a COBOL-DB2 program, ROLLBACK is used after a series of SQL operations if an error occurs that requires undoing changes. It is issued through an EXEC SQL block and is commonly found within error-handling sections of programs.

General Steps for Using ROLLBACK in a COBOL-CICS Program:

1. EXEC SQL: Begin with the EXEC SQL block.
2. ROLLBACK: Use ROLLBACK after detecting an error that requires undoing changes.
3. Error Handling: Check SQLCODE or EIBRESP (in CICS) to confirm the ROLLBACK was successful.

Examples - ROLLBACK Statement in a COBOL Program

   * Step 1: Attempt to Update Data in DB2 Table
	   EXEC SQL
		   UPDATE EMPLOYEE
		   SET SALARY = SALARY + 1000
		   WHERE JOB = 'MANAGER'
	   END-EXEC.
		...

		* Step 2: Rollback Transaction
		EXEC SQL
			ROLLBACK
		END-EXEC.
		...

Error Handling for the ROLLBACK Statement in a COBOL Program

Error handling for ROLLBACK in a COBOL program involves checking the SQLCODE after issuing ROLLBACK:

• SQLCODE = 0: Indicates successful rollback.
• SQLCODE < 0: Indicates an error, suggesting that the rollback did not complete as expected, which may require additional troubleshooting or corrective action.

Examples - Error Handling with ROLLBACK Statement

      ...
   EXEC SQL
	   ROLLBACK
   END-EXEC.

   IF SQLCODE = 0
	   DISPLAY 'Transaction rolled back successfully.'
   ELSE
	   DISPLAY 'Error in ROLLBACK operation: ' SQLCODE
   END-IF 
   STOP RUN.

---