COMMIT

The COMMIT statement ends a unit of work and commits the database changes that were made by that unit of work.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

COMMIT is not allowed in a trigger program if the trigger program and the triggering program run under the same commitment definition. COMMIT is not allowed in a procedure if the procedure is called on a remote application server.

Authorization

None required.

Syntax

          .-WORK-.
>>-COMMIT-+------+--+------+-----------------------------------><
                    '-HOLD-'
 

Description

The COMMIT statement ends the unit of work in which it is executed and starts a new unit of work. It commits all changes made by ALTER, CALL, CREATE, COMMENT ON, DELETE, DROP (except DROP COLLECTION), GRANT, INSERT, LABEL ON, RENAME, REVOKE, and UPDATE statements executed during the unit of work.

Connections in the release-pending state are ended.

WORK

COMMIT WORK has the same effect as COMMIT.

HOLD

Indicates a hold on resources. If specified, currently open cursors are not closed and all resources acquired during the unit of work are held. Locks on specific rows and objects implicitly acquired during the unit of work are released.

If HOLD is omitted:

• Cursors opened under this unit of work's commitment definition are closed unless the cursors were declared with the WITH HOLD clause.
• Table locks acquired by the LOCK TABLE statement under this unit of work's commitment definition are released.

All implicitly acquired locks are released; except for object level locks required for the cursors that are not closed.

Notes

An implicit COMMIT may be performed under some circumstances. However, it is recommended that an explicit COMMIT or ROLLBACK be issued before the application ends.

• For the default activation group:
  • An implicit COMMIT is not performed when applications that run in the default activation group end. Interactive SQL, Query Manager, and non-ILE programs are examples of programs that run in the default activation group.
  • In order to commit work, you must issue a COMMIT.
• For nondefault activation groups when the scope of the commitment definition is to the activation group:
  • If the activation group ends normally, the commitment definition is implicitly committed.
  • If the activation group ends abnormally, the commitment definition is implicitly rolled back.
• Regardless of the type of activation group, if the scope of the commitment definition is the job, an implicit commit is never performed.

A unit of work can include the processing of up to 4 million rows, including rows retrieved during a SELECT or FETCH statement³³, and rows inserted, deleted, or updated as part of INSERT, DELETE, and UPDATE statements.³⁴

The commit and rollback operations do not affect the DROP COLLECTION statement, and this statement is not, therefore, allowed in an application program that also specifies COMMIT(*CHG), COMMIT(*CS), COMMIT(*ALL), or COMMIT(*RR).

The commitment definition used by SQL is determined as follows:

• If the activation group of the program calling SQL is already using an activation group level commitment definition, then SQL uses that commitment definition.
• If the activation group of the program calling SQL is using the job level commitment definition, then SQL uses the job level commitment definition.
• If the activation group of the program calling SQL is not currently using a commitment definition but the job commitment definition is started, then SQL uses the job commitment definition.
• If the activation group of the program calling SQL is not currently using a commitment definition and the job commitment definition is not started, then SQL implicitly starts a commitment definition. SQL uses the Start Commitment Control (STRCMTCTL) command with:
  • A CMTSCOPE(*ACTGRP) parameter
  • A LCKLVL parameter based on the COMMIT option specified on either the CRTSQLxxx, STRSQL, or RUNSQLSTM commands. In REXX, the LCKLVL parameter is based on the commit option in the SET OPTION statement.

Example

In a PL/I program, compiled with COMMIT (*CHG), transfer a certain amount of commission (COMM) from one employee (EMPNO) to another in the EMPLOYEE table. Subtract the amount from one row and add it to the other. Use the COMMIT WORK statement to ensure that no permanent changes are made to the database until both operations are completed successfully.

 XFRCOMM:  PROC OPTIONS(MAIN);
   EXEC SQL  BEGIN DECLARE SECTION;
     DCL  AMOUNT       FIXED DECIMAL(5,2);
     DCL  FROM_EMPNO   CHAR(6);
     DCL  TO_EMPNO     CHAR(6);
   EXEC SQL  END DECLARE SECTION;
   EXEC SQL  INCLUDE SQLCA;
   EXEC SQL  WHENEVER SQLERROR GOTO SQLERR;
   GET ...
   EXEC SQL  UPDATE EMPLOYEE
               SET COMM = COMM - :AMOUNT
               WHERE EMPNO = :FROM_EMPNO;
   EXEC SQL  UPDATE EMPLOYEE
               SET COMM = COMM + :AMOUNT
               WHERE EMPNO = :TO_EMPNO;
 FINISHED:
   EXEC SQL  COMMIT WORK;
   RETURN;
 SQLERR:
   PUT ...
   EXEC SQL  WHENEVER SQLERROR CONTINUE;    /* continue if error on rollback    */
   EXEC SQL  ROLLBACK WORK;
   RETURN;
 END XFRCOMM;

³³

This limit also includes:

• Any records accessed or changed through files opened under commitment control through high-level language file processing
• Any rows deleted, updated, or inserted as a result of a trigger or CASCADE, SET NULL, or SET DEFAULT referential integrity delete rule.

³⁴

Unless you specified COMMIT(*CHG) or COMMIT(*CS), in which case these rows are not included in this total.