INFO: MTS/IIS Interoperability with IBM DB2, Using Two-Phase Commit Through MSDTC/XA (246870)

MORE INFORMATION

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 on this product, please see 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 on Microsoft SNA Server, please see the following Microsoft Web site: Use the following table to determine which driver and connectivity software configuration meets your needs:

Driver	DB2 connectivity	2PC support
MS ODBC driver	MS SNA Server via APPC	No
MS ODBC driver	TCP/IP	No
IBM ODBC driver	IBM Client Connect via APPC	Yes
IBM ODBC driver	IBM Client Connect via TCP/IP	Yes

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 additional information, click the article number below to view the article in the Microsoft Knowledge Base: Please see the following sites to obtain Windows NT 4.0 Service Pack 5 or 6: 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 Web site. For more information, read the Aparlist.txt file from the appropriate download for your configuration: 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, see the following page on the Microsoft Universal Data Access 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
  
  

  Application -> ODBC API -> ODBC Driver

  Advantages	Performance, API Maturity, and any language/tool that supports calling API's (Visual Basic, Visual C++, and so forth)
  Disadvantages	Non-COM interface, amount of code, lack of rich constructs like Recordsets and other objects
  Connection Pooling	ODBC connection pooling

• Coding to the OLE DB Interfaces
  
  

  Application -> OLE DB Interfaces -> OLE DB Provider for ODBC -> ODBC API -> ODBC Driver

  Advantages
  Disadvantages	OLE DB interfaces cannot be called directly from Visual Basic.
  Connection Pooling	OLE DB session pooling or ODBC connection pooling

• ADO (ActiveX Data Objects)
  
  

  Application -> ADO -> OLE DB Interfaces -> OLE DB Provider for ODBC -> ODBC API -> ODBC Driver

  Advantages	Rich COM-Based interfaces callable from any automation compatible tool (Visual Basic, ASP/Visual Basic Script, Visual C++, and so forth), recordsets and other value-added constructs available for data management.
  Disadvantages	Performance/cost; while ADO is a very rich construct to quickly develop database applications, it does not come without cost. The ease of development and added functionality must be measured against performance and scalability requirements. This is true of any generic solution, and those trade-offs need to be considered accordingly.
  Connection Pooling	OLE DB session pooling or ODBC connection pooling

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'

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
   ..close db2 odbc connection    'db2 odbc connection gets put into pool

   ..open db2 connection..        'hopefully you get the same connection
                                   back from the pool, but not guaranteed
   ..update account table         'if you get the same connection that was
                                   just in the pool, no problem; if you
                                   don't then you will be locked out of 
                                   locks held by the first active 
                                   connection
   ..close db2 odbc connection
End Sub
				

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 are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding 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.