When to call the CLI SQLEndTran() function
In manual-commit mode,
you must call the
SQLEndTran() function before calling SQLDisconnect(). In
auto-commit mode, even if you do not specify SQLEndTran(),
a commit is issued implicitly at the end of each statement execution
or when a cursor is closed.
If a distributed unit of work is involved, additional rules might apply.
Consider the following behavior when deciding where in the application
to end a transaction:
- Each connection cannot have more than one current transaction at any given time, so keep dependent statements within the same unit of work. Note that statements must always be kept on the same connection under which they were allocated.
- Various resources may be held while the current transaction on a connection is running. Ending the transaction will release the resources for use by other applications.
- Once a transaction has successfully been committed or rolled back, it is fully recoverable from the system logs. Open transactions are not recoverable.
Effects of calling SQLEndTran()
When a
transaction ends:
- All locks on DBMS objects are released, except those that are associated with a held cursor.
- Prepared statements are preserved from one transaction to the next. Once a statement has been prepared on a specific statement handle, it does not need to be prepared again even after a commit or rollback, provided the statement continues to be associated with the same statement handle.
- Cursor names, bound parameters, and column bindings are maintained from one transaction to the next.
- By default, cursors are preserved after a commit (but not a rollback). All cursors are by default defined with the WITH HOLD clause, except when the CLI application is running in a Distributed Unit of Work environment.