COMMIT

The COMMIT transaction management statement requests that changes to the database made by the SQL session be made permanent. Optionally, the SQL session continues or terminates.

Authorization

None required.

Syntax

►►─── COMMIT work ─┬────────────┬─────────────────────────────────────────────►◄
                   ├─ CONTINUE ─┤
                   └─ RELEASE  ─┘

Parameters

CONTINUE

Directs CA IDMS to maintain the state of the SQL session after committing changes to the database. This has the effect of maintaining the position of open cursors, retaining temporary tables and associating a new transaction with the session after terminating the current one.

If you do not specify CONTINUE, CA IDMS terminates the current transaction and does not start a new one. It also closes all open cursors and drops all temporary tables. The CONTINUE parameter is a CA IDMS extension of the SQL standard.

RELEASE

Directs CA IDMS to end the current SQL session as well as the current transaction after committing the changes to the database. The RELEASE parameter is a CA IDMS extension of the SQL standard.

Usage

Effect of a COMMIT on the SQL Session's Transaction

If the SQL session's database transaction is not shared, a COMMIT statement has the following impact on its transaction:

Commits all changes made by the SQL session
Releases all exclusive locks
Terminates the transaction
Associates a new transaction with the SQL session if CONTINUE is specified

Effect of a COMMIT on an SQL Session

A COMMIT statement without the CONTINUE option has the following impact on the SQL session:

Releases all locks used to protect cursor currencies
Closes all open cursors
Drops all temporary tables
Deletes all dynamically compiled statements
Replaces the access module in the dictionary if the module was recreated during transaction execution
Terminates the SQL session if CA IDMS connected it automatically or if RELEASE is specified

If CONTINUE is specified, the COMMIT statement impacts the SQL session's transaction but has no impact on the session itself. Its state remains as it was before the COMMIT statement was issued.

Effect of Transaction Sharing

A COMMIT statement requests that changes made by an SQL session be committed. However, if more than one database session is sharing the session's transaction, those changes might not be committed immediately. All sharing sessions that have had activity since the last commit, rollback or session start must signal their willingness to commit by issuing a COMMIT statement before changes are actually made permanent. The last one to do so causes the transaction to be committed.

A teleprocessing commit statement such as a COMMIT TASK can be used to cause the immediate committing of a shared transaction, since it impacts all of the associated sessions. A COMMIT issued by an encompassing session automatically commits all of its subordinate sessions.

Example

Committing Changes to the Database

The following COMMIT statement commits changes made during the current transaction to the database, but does not end the transaction:

EXEC SQL
   COMMIT CONTINUE
END-EXEC

More Information

For more information about ending a transaction without committing changes to the database, see ROLLBACK.
For more information about ending an SQL session, see RELEASE.
For more information about managing transactions, see the CA IDMS SQL Programming Guide.
For more information about establishing an SQL session, see CONNECT.