COMMIT statement

Invocation for COMMIT

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 for COMMIT

None required.

Description for 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. For more information see Statements.

Notes for COMMIT

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.

Static and dynamic INSERT, UPDATE, DELETE, and MERGE statements that reference declared global temporary tables that were defined without ON COMMIT DROP TABLE and are bound with or use the RELEASE(DEALLOCATE) option are kept past commit points. The statement is not kept across the commit point if one of the following conditions is true:

• The declared global temporary table is defined with the ON COMMIT DROP TABLE option.
• The statement uses the RELEASE(COMMIT) bind option.
• The statement also references a Db2 base object (for example, a table or view), and one of the following statements is true:
  • The base object reference is for a Db2 catalog table.
  • At the commit point, Db2 determines that another Db2 thread is waiting for an X-lock on the base object's database descriptor (DBD).
  • The statement references an XML function or operation, and at the commit point Db2 determines that the base object DBD S-lock for the XML operation must be released.
  • At the commit point, Db2 determines that a base object DBD S-lock that is used by the statement must be released and cannot be maintained across the commit point.
• Db2 determines that another Db2 thread is waiting for an X-lock on the Db2 package that contains the statement.

Prepared dynamic statements are kept past commit points if one of the following conditions is true:

• 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 point.
• The statements reference a declared global temporary table that was defined without ON COMMIT DROP TABLE, and the package was bound with or uses the RELEASE(DEALLOCATE) option. In that case, all prepared INSERT, UPDATE, DELETE, and MERGE statements that reference the declared global temporary table are kept across the commit point.

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.
• Bind option DISCONNECT(CONDITIONAL) was used and there are no open WITH HOLD cursors for that site.
• The statement references a declared global temporary table, has no open WITH HOLD cursor, and is in a package that is bound with the RELEASE(COMMIT) option.
• The statement references a declared global temporary table that was defined with the ON COMMIT DROP TABLE option. The statement also has no open WITH HOLD cursor, and the statement’s package is bound with or uses the RELEASE(DEALLOCATE) option.

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 The duration of a lock.

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 special register.

Effect of commit on global variables: Global variables are not controlled at the transaction level. Issuing a COMMIT statement does not effect the contents of a global variable.

Example for COMMIT

  COMMIT WORK;