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 order of operations in an application that executes statements on two SQL_CONCURRENT_TRANS connections (A and B) and indicates the scope of the transactions.

Figure 1 shows the operations in an application that executes statements on two SQL_CONCURRENT_TRANS connections (A and B) and indicates the scope of the transactions.

Figure 1. Multiple connections with concurrent transactions (order in an application)
Begin figure summary. This figure shows the basic steps in an application that makes concurrent transactions on multiple connections.

Figure 2 shows the order in which the operations in an application that executes statements on two SQL_CONCURRENT_TRANS connections (A and B) are executed.

Figure 2. Multiple connections with concurrent transactions (order of execution)
Begin figure summary. This figure shows the order in which the basic steps in the application shown in the previous figure are executed.

In Figure 2, the transactions are interleaved on multiple concurrent connections. If an application specifies SQL_CONCURRENT_TRANS, the ODBC model supports one transaction for each active connection. The transactions are managed and committed independently.

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 that was previously described.

If an application specifies MULTICONTEXT=0 in the concurrent environment that Figure 2 portrays, the Db2 ODBC driver executes the first transaction as three separate implicit transactions. The Db2 ODBC driver performs these three implicit transactions with the following actions. (You do not issue these actions explicitly in your application).
  • First transaction
    1. Executes statement B2
    2. Commits1
  • Second transaction
    1. Reconnects to data source B (after committing a transaction on data source A)
    2. Executes statement B2
    3. Commits1
  • Third transaction
    1. Reconnects to data source B (after committing a transaction on data source A)
    2. Executes statement B1
    3. Commits1
Note:
  1. 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 using SQLEndTran() 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 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 statement processing that Figure 1 and Figure 2 depict occurs in a coordinated distributed environment. The following figure shows statements on two SQL_COORDINATED_TRANS connections (A and B) and the scope of a coordinated distributed transaction.

Figure 3. Multiple connections with coordinated transactions
Begin figure summary. This figure shows the basic steps an application might take to make coordinated transactions on multiple connections. Detailed description available.