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
1 – 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 --+
3 – Arguments
3.1 – AND_CHAIN
When AND CHAIN is used, a new transaction is implicitly started
using the same attributes as the previously commited transaction.
3.2 – WORK
An optional keyword that has no effect on the COMMIT statement.
4 – 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>