Coordinated connections in a Db2 ODBC application
In distributed units of work, commits and rollbacks among multiple data source connections are coordinated. To establish coordinated connections in a Db2 ODBC application, set the SQL_ATTR_CONNECTTYPE attribute to SQL_COORDINATED_TRANS or set the CONNECTTYPE keyword to 2.
Coordinated connections are equivalent to connections that are established as CONNECT (type 2) in IBM® embedded SQL. All the connections within an application must have the same connection type. In a distributed unit of work, you must establish all connections as coordinated. The default commit mode for coordinated connections is manual-commit mode.
The following figure shows the logical flow of an application that executes statements on two SQL_CONCURRENT_TRANS connections ('A' and 'B') and indicates the scope of the transactions. (This figure shows the logical flow and transaction scope of an application that executes the same statements on two SQL_COORDINATED_TRANS connections.)

In Figure 1, the third and fourth transactions are interleaved on multiple concurrent connections. If an application specifies SQL_CONCURRENT_TRANS, the ODBC model supports one transaction for each active connection. In Figure 1, the third transaction and the fourth transaction are managed and committed independently. (The third transaction consists of statements A1 and A2 at data source A and the fourth transaction consists of statements B2, B2 again, and B1 at data source B.) The transactions at A and B are independent and exist concurrently.
If you set the SQL_ATTR_CONNECTTYPE attribute to SQL_CONCURRENT_TRANS and specify MULTICONTEXT=0 in the initialization file, you can allocate any number of concurrent connection handles. However, only one physical connection to Db2 can exist at any given time with these settings. This behavior precludes support for the ODBC connection model. Consequently, applications that specify MULTICONTEXT=0 differ substantially from the ODBC execution model was previously described.
- First transaction
- Executes statement B2
- Commits1
- Second transaction
- Reconnects to data source B (after committing a transaction on data source A)
- Executes statement B2
- Commits1
- Third transaction
- Reconnects to data source B (after committing a transaction on data source A)
- Executes statement B1
- Commits1
- In applications that run with MULTICONTEXT=0, you must always
commit before changing data sources. You can specify AUTOCOMMIT=1
in the initialization file or call
SQLSetConnectAttr()with SQL_ATTR_AUTOCOMMIT set to SQL_AUTOCOMMIT_ON to include these commit statements implicitly in your application. You can also explicitly include commits by usingSQLEndTran()calls or SQL commit statements in your application.
From an application point of view, the transaction at data source B, which consists of statements B2, B2, and B1, becomes three independent transactions. The statements B2, B2, and B1 are each executed as independent transactions. Similarly, the fourth transaction at data source A, which consists of statements A1 and A2 becomes two independent transactions: A1 and A2.
The following figure shows how the statements that Figure 1 depicts are executed in a coordinated distributed environment. This figure shows statements on two SQL_COORDINATED_TRANS connections ('A' and 'B') and the scope of a coordinated distributed transaction.
