Db2 as transaction manager in CLI applications

In CLI/ODBC applications, you can use the Db2 transaction manager to coordinate distributed transactions against all IBM database servers.

Configuration of Db2 as transaction manager

The Db2 Transaction Manager must be set up according to the information in the Db2 transaction manager configuration documentation.

To use Db2 as the transaction manager in CLI/ODBC applications, the following configurations must be applied:
  • The SQL_ATTR_CONNECTTYPE environment attribute must be set. This attribute controls whether the application is to operate in a coordinated or uncoordinated distributed environment. Commits or rollbacks among multiple database connections are coordinated in a coordinated distributed environment. The two possible values for this attribute are:
    • SQL_CONCURRENT_TRANS - supports single database per transaction semantics. Multiple concurrent connections to the same database and to different databases are permitted. Each connection has its own commit scope. No effort is made to enforce coordination of transactions. This is the default and corresponds to a Type 1 CONNECT in embedded SQL.
    • SQL_COORDINATED_TRANS - supports multiple databases per transaction semantics. A coordinated transaction is one in which commits or rollbacks among multiple database connections are coordinated. Setting SQL_ATTR_CONNECTTYPE to this value corresponds to Type 2 CONNECT in embedded SQL.

    It is recommended that the application set this environment attribute with a call to SQLSetEnvAttr(), if necessary, as soon as the environment handle has been allocated. However, since ODBC applications cannot access SQLSetEnvAttr(), they must set this using SQLSetConnectAttr() after each connection handle is allocated, but before any connections have been established.

    All connections on an environment handle must have the same SQL_ATTR_CONNECTTYPE setting. An environment cannot have a mixture of concurrent and coordinated connections. The type of the first connection will determine the type of all subsequent connections. SQLSetEnvAttr() will return an error if an application attempts to change the connect type while there is an active connection.

  • If SQL_ATTR_CONNECTTYPE is set to SQL_COORDINATED_TRANS, two-phase commit is used to commit the work done by each database in a multiple database transaction. This requires the use of a Transaction Manager to coordinate two-phase commits amongst the databases that support this protocol. Multiple readers and multiple updaters are allowed within a transaction.
  • The function SQLEndTran() must be used in a multisite update environment when Db2 is acting as the transaction manager.

Application flows in concurrent and coordinated transactions

Figure 1 shows the logical flow of an application executing statements on two SQL_CONCURRENT_TRANS connections ('A' and 'B'), and indicates the scope of the transactions.

Figure 2 shows the same statements being executed on two SQL_COORDINATED_TRANS connections ('A' and 'B'), and the scope of a coordinated distributed transaction.

Figure 1. Multiple connections with concurrent transactions
Multiple connections with concurrent transactions

Figure 2. Multiple connections with coordinated transactions
Multiple connections with coordinated transactions

Restrictions

Mixing embedded SQL and CLI/ODBC calls in a multisite update environment is supported, but all the same restrictions of writing mixed applications are imposed.