You use COMMIT WORK to terminate an SQL transaction and commit the modifications made to the database during that transaction. The updated SQL data is then available to all other transactions.
A new transaction is started by the first SQL statement after COMMIT WORK that initiates an SQL transaction.
COMMIT [WORK]
SQL transaction
You start an SQL transaction with any SQL statement that initiates a transaction. All subsequent SQL statements up to the next COMMIT WORK or ROLLBACK WORK statement are part of one transaction. COMMIT WORK or ROLLBACK WORK terminates the transaction.
Transaction under openUTM
You cannot use the COMMIT WORK statement if you are working with openUTM. In this case, transaction management is performed using only UTM language resources. openUTM ensures the synchronization of SESAM/SQL and UTM transactions. A UTM transaction ends when the next synchronization point is set.
Initiating a transaction
The following SQL statements do not initiate a transaction:
DECLARE CURSOR (not executable)
PERMIT
SET CATALOG
SET SCHEMA
SET SESSION AUTHORIZATION
SET TRANSACTION
WHENEVER (not executable)
Utility statements
The statements EXECUTE and EXECUTE IMMEDIATE only initiate an SQL transaction if the dynamic statement to be executed initiates a transaction.
All other SQL statements initiate an SQL transaction if no transaction is open when they are executed.
Statements within a transaction
The following statements cannot be executed within a transaction:
SET SESSION AUTHORIZATION
SET TRANSACTION
Utility statements
You may not execute or prepare an SQL statement that manipulates data (query, update) in a transaction in which an SQL statement for defining or managing schemas, storage structures or user entries is executed.
CALL DML transaction
The SQL statement COMMIT WORK is not permitted within a CALL DML transaction (see section "SQL statements in CALL DML transactions").
Effects of COMMIT WORK
COMMIT WORK affects the subsequent transactions, as well as the open cursors and the defaults in the transaction.
Effect on subsequent transactions
COMMIT WORK work sets the isolation or consistency level and the transaction mode, which were set for the transaction with the SET TRANSACTION statement, back to their default values. Any subsequent transaction therefore works the default isolation or consistency level and transaction mode if they are not changed again with SET TRANSACTION.
Repercussions on cursors (not applicable for local cursors in procedures)
COMMIT WORK closes all the cursors opened in the transaction. If you want to save the cursor position beyond the end of the transaction, you can save the position with the STORE statement and restore it later with RESTORE.
It is possible to define a cursor using the WITH HOLD clause. A cursor defined in this way will remain open even after COMMIT WORK is executed (successfully). It can then be positioned in a follow-up transaction using FETCH.
Effect on defaults
Default values defined with SET CATALOG, SET SCHEMA and SESSION AUTHORIZATION are committed after COMMIT WORK.
Behavior of SESAM/SQL in the event of an error
If an SQL transaction cannot be completed normally because of an error, SESAM/SQL rolls back the complete transaction. Refer to ROLLBACK WORK for information on which database objects are affected.
See also
ROLLBACK WORK, SET TRANSACTION