The COMMIT statement is used to mark the end of a transaction (unit of work) and preserve all of the database changes made
by the transaction.
Invocation
COMMIT is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared. COMMIT
cannot be used in CICS or IMS environments.
Authorization
No special authorization is required.
Syntax
COMMIT [WORK]
Description
A transaction begins when a previous transaction ends or when logging begins (see the
SET LOG command). A transaction is terminated by a COMMIT or ROLLBACK command.
Only the results of SQL commands executed within a single, uncommitted transaction are affected by the COMMIT command. When
the COMMIT command is issued, all table changes caused by EXPLAIN, GRANT, REVOKE, COMMENT ON, LABEL ON, INSERT, UPDATE, and
DELETE commands in the transaction are made permanent, and can no longer be rolled back (the CREATE, DROP and ALTER commands
can never be rolled back).All savepoints that were set within the transaction are released
In DB2 mode, open cursors declared without the WITH HOLD option are closed and any statements prepared for those cursors are
destroyed. All open cursors declared with the WITH HOLD option are preserved, along with any statements that were prepared
for these cursors. In XDB mode, all cursors remain open at commit time.
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 implicitly acquired locks are released, except:
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
An implicit COMMIT is always performed when exiting from the current session. All locks acquired during a transaction are
released by the COMMIT command.
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 atttribute 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.
The optional keyword WORK is provided for compatibility with the SAA definition of SQL, and is available in DB2. COMMIT and
COMMIT WORK perform identically.
Description
A transaction begins when a previous transaction ends or when logging begins (see the SET LOG command). A transaction is terminated by a COMMIT or ROLLBACK command.
Only the results of SQL commands executed within a single, uncommitted transaction are affected by the COMMIT command. When the COMMIT command is issued, all table changes caused by EXPLAIN, GRANT, REVOKE, COMMENT ON, LABEL ON, INSERT, UPDATE, and DELETE commands in the transaction are made permanent, and can no longer be rolled back (the CREATE, DROP and ALTER commands can never be rolled back).All savepoints that were set within the transaction are released
In DB2 mode, open cursors declared without the WITH HOLD option are closed and any statements prepared for those cursors are destroyed. All open cursors declared with the WITH HOLD option are preserved, along with any statements that were prepared for these cursors. In XDB mode, all cursors remain open at commit time.
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 implicitly acquired locks are released, except:
An implicit COMMIT is always performed when exiting from the current session. All locks acquired during a transaction are released by the COMMIT command.
All rows of every declared temporary table of the application process are deleted with these exceptions:
The optional keyword WORK is provided for compatibility with the SAA definition of SQL, and is available in DB2. COMMIT and COMMIT WORK perform identically.