Commit and rollback in Db2 ODBC

Db2 ODBC supports two commit modes: autocommit and manual-commit. A transaction is a recoverable unit of work or a group of SQL statements that can be treated as one atomic operation. This means that all the operations within the group are guaranteed to be completed (committed) or undone (rolled back), as if they were a single operation.

A transaction can also be referred to as a unit of work or a logical unit of work. When the transaction spans multiple connections, it is referred to as a distributed unit of work.

In autocommit mode, every SQL statement is a complete transaction, which is automatically committed. For a non-query statement, the commit is issued at the end of statement execution. For a query statement, the commit is issued after the cursor is closed. Given a single statement handle, the application must not start a second query before the cursor of the first query is closed.

In manual-commit mode, transactions are started implicitly with the first access to the data source using SQLPrepare(), SQLExecDirect(), SQLGetTypeInfo(), or any function that returns a result set. At this point a transaction begins, even if the call failed. The transaction ends when you use SQLEndTran() to either rollback or commit the transaction. This means that any statements executed (on the same connection) between these are treated as one transaction.

The default commit mode is autocommit, except when participating in a coordinated transaction. An application can switch between manual-commit and autocommit modes by calling SQLSetConnectAttr(). Typically, a query-only application might want to stay in autocommit mode. Applications that need to perform updates to the data source should turn off autocommit as soon as the data source connection is established.

When multiple connections exist, each connection has its own transaction (unless CONNECT (type 2) is specified). Special care must be taken to call SQLEndTran() with the correct connection handle to ensure that only the intended connection and related transaction is affected. Unlike distributed unit of work connections, transactions on each connection do not coordinate.