1  ROLLBACK
   Ends a transaction and undoes all changes you made since that
   transaction began. The ROLLBACK statement also:

   o  Closes all open cursors (with the exception of WITH HOLD
      cursors)

   o  Releases all row locks

   o  Performs a checkpoint operation if fast commit processing is
      enabled

   The ROLLBACK statement affects:

   o  All open databases included in the current transaction

   o  All changes to data made with SQL data manipulation statements
      (DELETE, UPDATE, and INSERT)

   o  All changes to data definitions made with SQL data definition
      statements (ALTER, CREATE, DROP, RENAME, GRANT, and REVOKE)
 

2  Environment
   You can use the ROLLBACK statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 (B)0Rollback-statement =  
 
 ROLLBACK WORK qqqqwqqqqqqqqqqqqqqqwqqq>
                   mq> AND CHAIN qqj

 

2  Arguments
 

3  AND_CHAIN

   Starts a new transaction implicitly using the same attributes as
   the rolled back transaction.
 

3  WORK

   Specifies an optional keyword that has no effect on the ROLLBACK
   statement. It is provided for compatibility with the ANSI/ISO SQL
   standard.
 

2  Examples
   Example 1: Rolling back changes in a COBOL program

   GET-ID-NUMBER.
         DISPLAY "Enter employee ID number:  "
            WITH NO ADVANCING.
         ACCEPT EMPLOYEE-ID.
   CHANGE-SALARY.
         DISPLAY "Enter new salary amount:  "
            WITH NO ADVANCING.
         ACCEPT SALARY-AMOUNT.

   EXEC SQL  UPDATE  SALARY_HISTORY
             SET     SALARY_AMOUNT = :SALARY-AMOUNT
             WHERE   EMPLOYEE_ID = :EMPLOYEE-ID
             AND     END_DATE IS NULL
   END-EXEC

         DISPLAY EMPLOYEE-ID, SALARY-AMOUNT.
         DISPLAY "Is this figure correct? [Y or N]  "
            WITH NO ADVANCING.
         ACCEPT ANSWER.
         IF ANSWER = "Y" THEN

           EXEC SQL  COMMIT END-EXEC
         ELSE
           EXEC SQL  ROLLBACK END-EXEC
           DISPLAY "Please enter the new salary amount again."
           GO TO CHANGE-SALARY
         END-IF.

   Example 2: Using COMMIT and AND CHAIN

   The following simple example executes SET TRANSACTION once at
   the start of the procedure. Then periodically the transaction is
   committed and restarted using the COMMIT AND CHAIN syntax. This
   simplifies the application since there is only one definition of
   the transaction characteristics.

   SQL> -- process table in batches
   SQL>
   SQL> set compound transactions 'internal';
   SQL> set flags 'transaction,trace';
   SQL>
   SQL> begin
   cont> declare :counter integer = 0;
   cont> declare :emp integer;
   cont>
   cont> set transaction
   cont>     read write
   cont>     reserving employees for exclusive write;
   cont>
   cont> for :emp in 0 to 600
   cont> do
   cont>     begin
   cont>     declare :id char(5)
   cont>           default substring (cast (:emp+100000 as varchar(6))
   cont>                              from 2 for 5);
   cont>     if exists (select * from employees where employee_id = :id)
   cont>     then
   cont>         trace 'found: ', :id;
   cont>         if :counter > 20
   cont>         then
   cont>             commit and chain;
   cont>             set :counter = 1;
   cont>         else
   cont>             set :counter = :counter + 1;
   cont>         end if;
   cont>     end if;
   cont>     end;
   cont> end for;
   cont>
   cont> commit;
   cont> end;
   ~T Compile transaction (1) on db: 1
   ~T Transaction Parameter Block: (len=2)
   0000 (00000) TPB$K_VERSION = 1
   0001 (00001) TPB$K_WRITE (read write)
   ~T Start_transaction (1) on db: 1, db count=1
   ~T Rollback_transaction on db: 1
   ~T Compile transaction (3) on db: 1
   ~T Transaction Parameter Block: (len=14)
   0000 (00000) TPB$K_VERSION = 1
   0001 (00001) TPB$K_WRITE (read write)
   0002 (00002) TPB$K_LOCK_WRITE (reserving) "EMPLOYEES" TPB$K_EXCLUSIVE
   ~T Start_transaction (3) on db: 1, db count=1
   ~Xt: found: 00164
      .
      .
      .
   ~Xt: found: 00184
   ~Xt: found: 00185
   ~T Commit_transaction on db: 1
   ~T Prepare_transaction on db: 1
   ~T Restart_transaction (3) on db: 1, db count=1
   ~Xt: found: 00186
      .
      .
      .
   ~Xt: found: 00205
   ~Xt: found: 00206
   ~T Commit_transaction on db: 1
   ~T Prepare_transaction on db: 1
   ~T Restart_transaction (3) on db: 1, db count=1
   ~Xt: found: 00207
      .
      .
      .
   ~Xt: found: 00228
   ~Xt: found: 00229
   ~T Commit_transaction on db: 1
   ~T Prepare_transaction on db: 1
   ~T Restart_transaction (3) on db: 1, db count=1
   ~Xt: found: 00230
      .
      .
      .
   ~Xt: found: 00249
   ~Xt: found: 00267
   ~T Commit_transaction on db: 1
   ~T Prepare_transaction on db: 1
   ~T Restart_transaction (3) on db: 1, db count=1
   ~Xt: found: 00276
      .
      .
      .
   ~Xt: found: 00435
   ~Xt: found: 00471
   ~T Commit_transaction on db: 1
   ~T Prepare_transaction on db: 1
   SQL>