Microsoft KB Archive/246870

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

Article ID: 246870

Article Last Modified on 1/17/2007

-

APPLIES TO


 * Microsoft Transaction Services 2.0
 * Microsoft Internet Information Server 4.0
 * Microsoft Internet Information Services 5.0
 * Microsoft Windows DNA

-



This article was previously published under Q246870



We strongly recommend that all users upgrade to Microsoft Internet Information Services (IIS) version 6.0 running on Microsoft Windows Server 2003. IIS 6.0 significantly increases Web infrastructure security. For more information about IIS security-related topics, visit the following Microsoft Web site:

http://www.microsoft.com/technet/security/prodtech/IIS.mspx



SUMMARY
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:

ftp://ftp.microsoft.com/bussys/viper/docs/OLETxToXa.Doc

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:

http://www-306.ibm.com/software/data/db2/db2connect/index.html

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:

http://www.microsoft.com/hiserver/snaserver/default.mspx

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:

229613 Dtcxatm.log file grows unexpectedly large

For information about Windows NT 4.0 Service Pack 5 or Service Pack 6, visit the following Microsoft Web site:

http://www.microsoft.com/technet/archive/downloads/winnt.mspx

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

241211 List of bugs fixed in Windows NT 4.0 Service Pack 6/6a (part 1)

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. For more information, read the Aparlist.txt file from the appropriate download for your configuration.

http://www-306.ibm.com/software/data/db2/support/db2_9

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:

http://msdn2.microsoft.com/en-us/data/aa937730.aspx

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

Coding to the OLE DB Interfaces
Application -> OLE DB Interfaces -> OLE DB Provider for ODBC -> ODBC API -> ODBC Driver

ADO (ActiveX Data Objects)
Application -> ADO -> OLE DB Interfaces -> OLE DB Provider for ODBC -> ODBC API -> ODBC Driver

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:


 * 1) 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:


 * 1) 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:  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}  Enable ODBC connection pooling for the ODBC driver being used:  CPTimeout String Value = # of seconds left in Pool</li> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft ODBC Driver for DB2</li></ul> </li></ol> </li>  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

</li>  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 </li></ul>

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.

Additional query words: LE TX transaction 2PC 2-phase

Keywords: kbdatabase kbdb2 kbinfo KB246870

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.