Micro Focus Enterprise Developer 3.0 for Visual Studio 2013 > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > COMMIT

COMMIT

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.