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>