COMMIT statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Description

The unit of work in which the COMMIT statement is executed is terminated and a new unit of work is initiated. All changes made by the following statements executed during the unit of work are committed: ALTER, COMMENT, CREATE, DROP, GRANT, LOCK TABLE, REVOKE, SET INTEGRITY, and the data change statements (INSERT, DELETE, MERGE, UPDATE), including those nested in a query.

The following statements, however, are not under transaction control and changes made by them are independent of the COMMIT statement:

• SET CONNECTION
• SET ENCRYPTION PASSWORD
• SET EVENT MONITOR STATE
• SET PASSTHRU
  Note: Although the SET PASSTHRU statement is not under transaction control, the passthru session initiated by the statement is under transaction control.
• SET SERVER OPTION
• SET variable
• Assignments to updatable special registers

All locks acquired by the unit of work subsequent to its initiation are released, except necessary locks for open cursors that are declared WITH HOLD. All open cursors not defined WITH HOLD are closed. Open cursors defined WITH HOLD remain open, and the cursor is positioned before the next logical row of the result table. (A FETCH must be performed before a positioned UPDATE or DELETE statement is issued.) All LOB locators are freed. Note that this is true even when the locators are associated with LOB values retrieved via a cursor that has the WITH HOLD property.

Dynamic SQL statements prepared in a package bound with the KEEPDYNAMIC YES option are kept in the SQL context after a COMMIT statement. This is the default behavior. The statement might be implicitly prepared again, as a result of DDL operations that are rolled back within the unit of work. Inactive dynamic SQL statements prepared in a package bound with KEEPDYNAMIC NO are removed from the SQL context after a COMMIT. The statement must be prepared again before it can be executed in a new transaction.

All savepoints set within the transaction are released.

The following statements behave differently than other data definition language (DDL) and data control language (DCL) statements. Changes made by these statements do not take effect until the statement is committed, even for the current connection that issues the statement. Only one of these statements can be issued by any application at a time, and only one of these statements is allowed within any one unit of work. Each statement must be followed by a COMMIT or a ROLLBACK statement before another one of these statements can be issued.

• CREATE SERVICE CLASS, ALTER SERVICE CLASS, or DROP (SERVICE CLASS)
• CREATE THRESHOLD, ALTER THRESHOLD, or DROP (THRESHOLD)
• CREATE WORK ACTION, ALTER WORK ACTION, or DROP (WORK ACTION)
• CREATE WORK CLASS, ALTER WORK CLASS, or DROP (WORK CLASS)
• CREATE WORKLOAD, ALTER WORKLOAD, or DROP (WORKLOAD)
• GRANT (Workload Privileges) or REVOKE (Workload Privileges)

Notes

• It is strongly recommended that each application process explicitly ends its unit of work before terminating. If the application program ends normally without a COMMIT or ROLLBACK statement then the database manager attempts a commit or rollback depending on the application environment.
• For information about the impact of COMMIT on cached dynamic SQL statements, see EXECUTE.
• For information about potential impacts of COMMIT on created temporary tables, see CREATE GLOBAL TEMPORARY TABLE.
• For information about potential impacts of COMMIT on declared temporary tables, see DECLARE GLOBAL TEMPORARY TABLE.
• The following dynamic SQL statements may be active during COMMIT:
  • Open WITH HOLD cursor
  • COMMIT statement
  • CALL statements under which the COMMIT statement was executed

Example

   COMMIT WORK