SQL Reference

COMMIT

The COMMIT statement terminates 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 through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

             .-WORK--.
>>-COMMIT----+-------+-----------------------------------------><
 

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 ON, CREATE, DELETE, DROP, GRANT, INSERT, LOCK TABLE, REVOKE, SET INTEGRITY, SET transition-variable, and UPDATE.

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

• SET CONNECTION,
• SET CURRENT DEFAULT TRANSFORM GROUP
• SET CURRENT DEGREE,
• SET CURRENT EXPLAIN MODE,
• SET CURRENT EXPLAIN SNAPSHOT,
• SET CURRENT PACKAGESET,
• SET CURRENT QUERY OPTIMIZATION,
• SET CURRENT REFRESH AGE,
• SET EVENT MONITOR STATE,
• SET PASSTHRU,
• SET PATH,
• SET SCHEMA,
• SET SERVER OPTION.

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. ⁶³ 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.

All savepoints set within the transaction are released.

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. Refer to Application Development Guide for implicitly ending a transaction in different application environments.
• See EXECUTE for information on the impact of COMMIT on cached dynamic SQL statements.
• See DECLARE GLOBAL TEMPORARY TABLE for information on potential impacts of COMMIT on declared temporary tables.

Example

Commit alterations to the database made since the last commit point.

   COMMIT WORK

⁶³

A FETCH must be performed before a Positioned UPDATE or DELETE statement is issued.