Microsoft KB Archive/246870

From BetaArchive Wiki

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:

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:

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


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

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

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.

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

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