Committing and Backing Out Transactions

Committing and Backing Out Transactions

Use the COMMIT WORK statement to commit any changes made to the database when updating tables. Uncommitted changes to a database can be backed out with the ROLLBACK WORK statement. In the following example, inserts to the table are committed if no errors are detected during processing. If an error is detected, the inserts are backed out. Following is an example:

 Problem

Commit your transactions if no error is detected during processing.
If an error is detected, rollback the transactions.
 Solution
               .
               .
       (COBOL statements)
               .
               .
  1        IF SQLCODE = 0
  2            MOVE 'INSERT COMPLETE' TO MSG-REC1
  3            PERFORM COM-WORK
  4            GO TO WRITE-REC.
  5        IF SQLCODE > 100
  6            MOVE 'WARNINGS ON INSERT' TO MSG-REC1
  7            GO TO WRITE-REC.
  8        IF SQLCODE < 0
  9            PERFORM ROLBK-WORK
 10            GO TO ERRORTN.
 11    COM-WORK.
 12         EXEC SQL
 13             COMMIT WORK
 14         END-EXEC.
 15    ROLBK-WORK.
 16         EXEC SQL
 17             ROLLBACK WORK
 18         END-EXEC.

The COBOL IF sets the condition for committing or backing out the inserts to the table. If the SQLCODE value in the SQL Communication Area (SQLCA) is 0 (zero), no errors were detected during processing. Any other SQLCODE value indicates an error has been detected.

Lines 1-4, 11-14

In this example, if no errors are detected, the inserts to the table are committed by the COMMIT WORK statement.

Lines 5-7

The program handles the case where a condition code greater than 100 is returned by issuing a message.

Lines 8-10, 15-18

If the SQLCODE value is less than 0, the ROLLBACK WORK statement is executed to back out the inserts.