1  COMMIT
   Ends a transaction and makes permanent any changes that you made
   during that transaction. The COMMIT statement also:

   o  Releases all locks

   o  Closes all open cursors (unless they are WITH HOLD cursors)

   o  Prestarts a new transacation if prestarted transactions are
      enabled
 

2  Environment
   You can use the COMMIT 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

 commit-statement =
 
 COMMIT WORK ---+---------------+--->
                +-> AND CHAIN --+

 

2  Arguments
 

3  AND_CHAIN

   When AND CHAIN is used, a new transaction is implicitly started
   using the same attributes as the previously commited transaction.
 

3  WORK

   An optional keyword that has no effect on the COMMIT statement.
 

2  Examples
   Example 1: Using the COMMIT statement to write a change to the
   database

   This example gives a raise to an employee. To maintain a
   consistent database, the program performs three operations within
   one transaction. The program:

   o  Prompts for an employee identification number (:ID).

   o  Prompts for a percentage increase, which is used to calculate
      the raise.

   o  Uses the UPDATE statement to change the current salary row by
      changing its salary ending date from null to the current date.

   o  Uses the INSERT statement to create a new row in the SALARY_
      HISTORY table. All the columns of the new row can be derived
      from columns of the old row, except the start date, which
      must be calculated from the current date. SQL calculates a
      new value for the SALARY_AMOUNT column from the old record's
      SALARY_AMOUNT column using the specified percentage increase
      (:PERC).

   o  Uses the COMMIT statement to make the changes to the database
      permanent.

   The first two SQL statements in the example are the WHENEVER
   SQLERROR and WHENEVER SQLWARNING statements. If an error or
   warning occurs, control transfers to another paragraph that
   contains a ROLLBACK statement. Therefore, this set of operations
   is never just partially completed.

      .
      .
      .
   PROCEDURE DIVISION.
   START-UP.

          DISPLAY "Enter employee's ID number:  "
            WITH NO ADVANCING.
          ACCEPT ID.
          DISPLAY "Percentage increase:  "
            WITH NO ADVANCING.
          ACCEPT PERC.

   EXEC SQL
             WHENEVER SQLERROR GOTO ERROR-PAR   END_EXEC.

   EXEC SQL
             WHENEVER SQLWARNING GOTO ERROR-PAR    END_EXEC.

   EXEC SQL SET TRANSACTION READ WRITE RESERVING
                SALARY_HISTORY FOR EXCLUSIVE WRITE
   END_EXEC.

   EXEC SQL
             UPDATE  SALARY_HISTORY SH
             SET     SH.SALARY_END = CURRENT_TIMESTAMP
             WHERE   SH.EMPLOYEE_ID = :ID
             AND     SH.SALARY_END IS NULL
   END_EXEC.

   EXEC SQL
             INSERT INTO SALARY_HISTORY
                    (EMPLOYEE_ID, SALARY_AMOUNT, SALARY_START)
                    SELECT EMPLOYEE_ID,
                           (SALARY_AMOUNT * (1 + (:PERC / 100))),
                           SALARY_END
                    FROM    SALARY_HISTORY
                    WHERE   EMPLOYEE_ID = :ID
                    AND     CAST(SALARY_END as DATE ANSI) = CURRENT_DATE
   END_EXEC.

   EXEC SQL
             COMMIT WORK  END_EXEC.

   Example 2: Using the COMMIT statement with data definition

   This example shows a simple database and table definition. The
   COMMIT statement makes the table definition permanent.

   SQL> CREATE DATABASE ALIAS INVENTORY;
   SQL> --
   SQL> CREATE TABLE INVENTORY.PART
   cont>    (TEST CHAR(10));
   SQL> COMMIT;
   SQL> SHOW TABLES
   User tables in database with alias INVENTORY
        PART

   Example 3: Using the AND CHAIN argument

   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: 00165
   .
   .
   .
   ~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: 00187
   .
   .
   .
   ~Xt: found: 00435
   ~Xt: found: 00471
   ~T Commit_transaction on db: 1
   ~T Prepare_transaction on db: 1
   SQL>