COMMIT

The COMMIT statement ends the unit of recovery in which it is executed and a new unit of recovery is started for the process. The statement commits all changes made by SQL schema statements and SQL data change statements during the 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. It cannot be used in the IMS™ or CICS® environment.

Authorization

None required.

Syntax

Read syntax diagram
           .-WORK-.   
>>-COMMIT--+------+--------------------------------------------><

Description

The COMMIT statement ends the unit of recovery in which it is executed and a new unit of recovery is started for the process. The statement commits all changes made by SQL schema statements and SQL data change statements during the unit of work. For more information see Statements.

Notes

Recommended coding practices: Code an explicit COMMIT or ROLLBACK statement at the end of an application process. Either an implicit commit or rollback operation will be performed at the end of an application process depending on the application environment. Thus, a portable application should explicitly execute a COMMIT or ROLLBACK statement before execution ends in those environments where explicit COMMIT or ROLLBACK is permitted.

Effect of COMMIT: All savepoints that are set within the unit of recovery are released, and all changes are committed for the following statements that are executed during the unit of recovery:

  • ALTER
  • COMMENT
  • CREATE
  • DELETE
  • DROP
  • EXPLAIN
  • GRANT
  • INSERT
  • LABEL
  • MERGE
  • RENAME
  • REVOKE
  • UPDATE
  • SELECT INTO with an SQL data change statement
  • subselect with an SQL data change statement

SQL connections are ended when any of the following conditions apply:

  • The connection is in the release pending status
  • The connection is not in the release pending status but it is a remote connection and:
    • The DISCONNECT(AUTOMATIC) bind option is in effect, or
    • The DISCONNECT(CONDITIONAL) bind option is in effect and an open WITH HOLD cursor is not associated with the connection.

For existing connections, all LOB locators are disassociated, except for those locators for which a HOLD LOCATOR statement has been issued without a corresponding FREE LOCATOR statement. All open cursors that were declared without the WITH HOLD option are closed. All open cursors that were declared with the WITH HOLD option are preserved, along with any SELECT statements that were prepared for those cursors. All other prepared statements are destroyed unless dynamic caching is enabled for your system. In that case, all prepared SELECT and data change statements that are bound with KEEPDYNAMIC(YES) are kept past the commit.

Prepared statements cannot be kept past a commit if any of the following conditions is true:

  • SQL RELEASE has been issued for that site.
  • Bind option DISCONNECT(AUTOMATIC) was used.
  • Start of changeBind option DISCONNECT(CONDITIONAL) was used and there are no open WITH HOLD cursors for that site.End of change

All implicitly acquired locks are released, except for the following locks:

  • Locks that are required for the cursors that were not closed
  • Table and table space locks when the RELEASE parameter on the bind command was not RELEASE(COMMIT)
  • LOB locks and LOB table space locks that are required for held LOB locators

For an explanation of the duration of explicitly acquired locks, see Managing DB2 Performance.

All rows of every created temporary table of the application process are deleted with the exception that the rows of a created temporary table are not deleted if any program in the application process has an open WITH HOLD cursor that is dependent on that table. In addition, if RELEASE(COMMIT) is in effect, the logical work files for the created temporary tables whose rows are deleted are also deleted.

All rows of every declared temporary table of the application process are deleted with these exceptions:

  • The rows of a declared temporary table that is defined with the ON COMMIT PRESERVE ROWS attribute are not deleted.
  • The rows of a declared temporary table that is defined with the ON COMMIT DELETE ROWS attribute are not deleted if any program in the application process has an open WITH HOLD cursor that is dependent on that table.

Implicit commit operations: In all DB2® environments, the normal termination of a process is an implicit commit operation.

Restrictions on the use of COMMIT: The COMMIT statement cannot be used in the IMS or CICS environment. To cause a commit operation in these environments, SQL programs must use the call prescribed by their transaction manager. The effect of these commit operations on DB2 data is the same as that of the SQL COMMIT statement.

The COMMIT statement cannot be used in a stored procedure if the procedure is in the calling chain of a user-defined function or a trigger or DB2 is not the commit coordinator.

Effect of commit on special registers: Issuing a COMMIT statement may cause special registers to be re-initialized. Whether one of these special registers is affected by a commit depends on whether the special register has been explicitly set within the application process. For example, assume that the PATH special register has not been explicitly set with a SET PATH statement in the application process. After a commit, the value of PATH is re-initialized. For information on the initialization of PATH, which can take the current value of CURRENT SQLID into consideration, see CURRENT PATH.

Example

Commit all DB2 database changes made since the unit of recovery was started.
  COMMIT WORK;