MTS/IIS interoperability with IBM DB2 using two-phase commit through MSDTC/XA

Any transactional Microsoft Transaction Server (MTS) activity (in other words, a transactional MTS component or Internet Information Server [IIS] Active Server Page) calls upon Microsoft Distributed Transaction Coordinator (MSDTC) to initiate an OLE TX transaction, which is a two-phase commit protocol. When an ODBC connection, or a connection to any other transactional resource manager via a resource dispenser, is made from that activity, it is enlisted in the transaction.

Currently, IBM DB2 does not support OLE TX directly as a two-phase commit (2PC) protocol, but it does support XA. Microsoft SQL Server supports OLE TX directly as a 2PC protocol. Therefore, the OLE TX initiated by MSDTC must be mapped to an XA transaction. For information about how to do this, see the "Mapping OLE Transactions to the XA Protocol" paper. This paper is avalable from the following link: This article provides technical information for developers who are deploying Windows DNA applications using DB2, including:

• Driver and connectivity configurations for DB2
• Recommended software for MTS/IIS server
• Application design considerations for DB2 connectivity
• How to avoid deadlocks

Driver and connectivity configurations for DB2

To get 2PC support for DB2, you must use IBM Client Connect software, including the IBM ODBC driver for DB2. For more information about this product, visit the following IBM Web site: You can use other database connectivity software for nontransactional DB2 clients, including the ODBC driver included with Microsoft SNA Server. For more information about Microsoft SNA Server, visit the following Microsoft Web site: Use the following table to determine which driver and connectivity software configuration meets your needs

Recommended software for MTS/IIS Server

For DB2 connectivity, it is recommended that you obtain the following software updates.

Windows NT 4.0 Service Pack 5 or later

MSDTC maintains two transaction log files for outstanding transactions, one for OLE TX information (Msdtc.log) and the other for XA information (Dtcxatm.log). Among other fixes, Windows NT Service Pack 5 includes bug fixes to MSDTC's XA log file management and therefore should be applied for maximum stability. For more information, click the following article number to view the article in the Microsoft Knowledge Base: For information about Windows NT 4.0 Service Pack 5 or Service Pack 6, visit the following Microsoft Web site: For more information, click the following article number to view the article in the Microsoft Knowledge Base:

DB2 UDB v5.2 Fix Pack 11 or later

There have been fixes to IBM Client Connect when using XA transaction, including:

• APAR JR13223: CALLING STORE PROCEDURE HANG OR TRAP
• APAR JR13333: EXECUTING A STORED PROCEDURE ON A LOCKED TABLE CAUSES TRAP

Note You can obtain the fixes to the previous APARs in FixPak 11 from the following IBM Web site.

Microsoft Data Access Components (MDAC) 2.1 Service Pack 2

MDAC 2.1 Service Pack 2 includes the latest updates to ActiveX Data Objects (ADO), OLE DB, and ODBC. Because your application most likely uses one or more of these technologies, you may want to install this latest version which includes many bug fixes.

Note There is no functional requirement to use a particular version of MDAC for DB2 connectivity.

To obtain MDAC 2.1, visit the following Microsoft Developer Network (MSDN) Web site:

Application design considerations

Assuming that you are using an ODBC driver, you can use one of the following approaches for DB2 connectivity from your application.

Coding to the ODBC API

Coding to the OLE DB Interfaces

ADO (ActiveX Data Objects)

The general programming practice promoted by MTS/IIS, as the "middle-tier," is to open database connections late (right before they are needed) and close them early (immediately after they are done being used). The primary reason for this is to allow those database connections to be pooled for other transactions to use, thus minimizing the concurrent connections to the database and avoiding constant creation and destruction of these connections.

Because IBM DB2 implementation of XA support is one-pipe, versus two-pipe, the physical database connection to DB2 must be kept open for the lifetime of the transaction. Therefore, some sort of database connection pooling must be used for a 2PC (XA) transaction to work. This is because after a transactional component's method call has completed, MSDTC will typically be notified to abort or commit the transaction. These transactional commands must go over the same database connection the actual database commands (SQL) were issued over; it's a one-pipe implementation.

MDAC implements two types of database connection pooling, ODBC connection pooling and OLE DB session pooling. Depending on the interface chosen to call from your application for DB2 connectivity, one or both of these may come into play. Refer to the API choices listed previously to determine which is available for your application.

Avoiding deadlocks

Depending on the isolation level used for a particular database connection, locks may be held on DB2 resources (tables, pages, rows, and so on) for the lifetime of a transaction. In the current implementation of DB2, each connection is treated as its own transaction, meaning that multiple connections enlisted in the same transaction cannot "share" locks. For example:

MTS Transaction Component 'A' To increase your chances of getting the same database connection back from the pools when connecting from the same transactional activity, do the following:

• Use ODBC connection pooling instead of OLE DB session pooling.
  
  The reason for this is that you can control the number of pools (dispensers) for ODBC connection pooling, whereas you cannot currently do this for the OLE DB session pools.
  
  Number of ODBC connection pools:
  # of CPUs (default)
  - or -
  HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling
     Value: NumberOfPools String Value (e.g. "1")
  Number of OLE DB session pools:
  # of CPUs + 1 (default; not configurable in current version of MDAC)
  By using ODBC connection pooling, you can guarantee that the same pool will always get searched for a pooled connection. If a pool was searched other than the one the connection you want was placed in, a new connection will be established, which may aggravate deadlocks in DB2.
  
  Steps to verify you are using ODBC connection pooling:
  1. Disable OLE DB session pooling for OLE DB Provider for ODBC:
     • Set the OLEDB_SERVICES key under MSDASQL (Kagera) to 0xfffffffc.
     • HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}
  2. Enable ODBC connection pooling for the ODBC driver being used:
     • CPTimeout String Value = # of seconds left in Pool
     • HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft ODBC Driver for DB2
• Design transactions so they are not at the mercy of getting the same connection back from the pool.
  
  Although the recommended approach is to acquire database connections late and release early, you may want to hold on to your DB2 connection for the lifetime of the transaction as long as it is within the scope of a method or thread of execution. For example:
  
  MTS Transaction Component 'A'

  Public Sub A_Method_Foo()
  
     ..open db2 odbc connection..   'none pooled so new one created, 
                                     and enlisted in this new transaction
     ..update account table         'lock held on some rows in the account
                                     table
  	
     ..update account table         'no problem, you still have the 
                                     connection, never went to pool
     ..close db2 odbc connection
  End Sub
  					

  
  
• Set CPTimeout (connection pool timeout) accordingly.
  
  Because MSDTC needs the opportunity to call in on the database connection in a pool to commit or abort a transaction, it is crucial that the connection remain in the pool long enough for MSDTC to make this call.
  
  So consider the following:
  
  MTS Transaction Component 'A'

  Public Sub A_Method_Foo()
  
     ..open db2 odbc connection..   'none pooled so new one created, and 
                                     enlisted in this new transaction
     ..update account table         'lock held on some rows in the account
                                     table
  	
     ..update account table         'no problem, you still have the 
                                     connection, never went to pool
     ..close db2 odbc connection
  
     .. do a bunch of work not      'physical database connection closes
        using db2 odbc connection    and MSDTC cannot commit or rollback
        that takes longer than       commit or rollback transaction 
        CPTimeout                    accordingly
  
  End Sub
  						

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.

Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.