INFO: IBM DB2 ODBC Driver and Microsoft Transaction Server (269735)

MORE INFORMATION

• Install Microsoft Windows NT 4.0 Service Pack 5 or Higher
  
  The IBM DB2 driver does not support OLE Transactions (OLE TX). Instead, the IBM DB2 driver supports XA Transactions. The Microsoft Distributed Transaction Coordinator (MSDTC) maintains two transaction log files for outstanding transactions: one for OLE TX information (the Msdtc.log file) and the other for XA information (the Dtcxatm.log file). Among other fixes, Microsoft Windows NT 4.0 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:

  229613 Dtcxatm.log File Grows Unexpectedly Large

• Turn Off Session Pooling for the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL)
  
  By default, starting with Microsoft Data Access Components (MDAC) 2.1, OLE DB Session Pooling is turned on. To work with the IBM DB2 ODBC driver under MTS, you must turn off OLE DB Session Pooling for MSDASQL. To turn off OLE DB Session Pooling for MSDASQL, perform the following steps:
  
  
  1. Start the Registry Editor (Regedt32.exe).
  2. Locate the OLEDB_SERVICES value under the following key in the registry:

     HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}\

  3. On the Edit menu, click DWORD, type: 0xfffffffc, and then click OK.
  4. Quit the Registry Editor.
  NOTE: If you change the registry, it will affect all other applications that are using the MSDASQL provider. To avoid this, you can also set this value in your application by adding the value "OLE DB Services=-4" in your connection string to turn off session pooling and autoenlistment. This setting turns off these properties for the OLE DB provider, and allows the pooling and autoenlistment to occur at the ODBC driver level.
  
  
• Turn On ODBC Connection Pooling on a Per-Driver Basis
  
  By default, MTS turns on Connection Pooling on a per-process basis.
  
  When you use the IBM DB2 driver and want to turn on connection pooling, you need to do so on a per-driver basis.
  
  For more information, see the "References" section.
  
  The following is a short code example that explains how to turn on connection pooling from a Microsoft Visual Basic DLL that is using ADO. Please note that the client that is using this MTS object should first call the Initialize function before calling any other functions that use ADO. This is because you must call the ODBC API SQLSetEnvAttr before any ADO initialization takes place. SQLSetEnvAttr is used to turn on connection pooling.
  
  
  1. Start Visual Basic and create a new ActiveX DLL project.
  2. This creates a new class named Class1. Set the MTSTransactionMode property of Class1 to "2 - RequiresTransaction".
  3. Add the following code to Class1:

        Option Explicit
        Dim rc As Long
        Const SQL_ATTR_CONNECTION_POOLING = 201
        Const SQL_CP_ONE_PER_DRIVER = 1
        Const SQL_IS_INTEGER = -6
        Const SQL_CP_OFF = 0
     
        Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal phenv&, ByVal    fOption%, ByVal vParam&, ByVal strlen%) As Integer
     
     
        Public Sub Initialze()
           'Enable connection pooling; this must be done before any ADO calls
           'are made. Only needs to occur one time per process.
           'You have to call SQLSetEnvAttr before initializing ADO.
           'So, call this function BEFORE calling TestPool.
            
            rc = SQLSetEnvAttr(0&, _
                         SQL_ATTR_CONNECTION_POOLING, _
                         SQL_CP_ONE_PER_DRIVER, _
                         SQL_IS_INTEGER)
       End Sub
     
       Public Sub Release()
            Call SQLSetEnvAttr(0&, _
     
                        SQL_ATTR_CONNECTION_POOLING, _
                        SQL_CP_OFF, _
                        SQL_IS_INTEGER)
                        
       End Sub
     
       Public Sub TestPool()
         Dim conn As ADODB.Connection
         Dim i As Long
         Dim henv As Long
         Dim hdbc As Long
     
         Dim retvalue As Integer
     
         For i = 0 To 100
            Set conn = CreateObject("ADODB.CONNECTION")
            conn.Open "DSN=YourDSN;uid=YourUid;pwd=YourPassword"
            conn.Close
            Set conn = Nothing
         Next i
       End Sub
     							

  4. From the Project menu, click References, and then add a reference to the following type library:

     Microsoft ActiveX Data Objects 2.5 Library

     NOTE: You can also use ADO 2.1 or 2.6.
  5. Compile the ActiveX DLL and register it under MTS. You can then call this COM component's methods from any client.
     
     
  6. You can then call this COM component's methods from any client.Open PerfMon and add ODBC Connection Pooling Counter for soft Connect/Disconnect. You should be able to see some activity under PerfMon as the DLL methods run. This ensures that you have connection pooling working with the IBM DB2 driver.
  
  
  
• Set Properties in the Db2cli.ini file on Your Client Computer
  
  If you try to run the preceding COM component by calling the Initialize and TestPool methods, you may see the following error messages:
  DIAG [25000] [Microsoft][ODBC Driver Manager] Failed to enlist on calling object's transaction (0)
  
  DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0)
  The preceding error messages can occur due to improper settings in the Db2cli.ini file. In particular, improperly setting the AUTOCOMMIT value can cause problems. By default, AUTOCOMMIT is set to 1, which is ON. You may think that in a distributed transaction processing environment you need to set this to 0, or OFF. That is not true. If you set AUTOCOMMIT to 0, the preceding error messages occur. When your object is registered under MTS as Requires Transaction, the IBM DB2 driver internally sets this value to 0. The driver causes the preceding error messages if you explicitly try to set the value to 0. Therefore, do not try to set the AUTOCOMMIT value to 0 in the Db2cli.ini file, and do not call any ODBC API function to set this value to 0. IBM drivers internally change this value as required.
  
  Additionally, you must not change the following configuration keywords (as set in the Db2cli.ini file) from their default values when you run DB2 CLI/ODBC applications:
  
  
  • CONNECTYPE keyword (default 1).
    
    
  • MULTICONNECT keyword (default 1).
    
    
  • DISABLEMULTITHREAD keyword (default 1).
    
    
  • CONNECTIONPOOLING keyword (default 0).
    
    
  • KEEPCONNECTION keyword (default 0).
  
  DB2 CLI applications written to make use of MTS support must not change the attribute values corresponding to the preceding keywords. Also, the application must not change the default values of the following ODBC attribute:

  SQL_ATTR_CONNECT_TYPE attribute (default SQL_CONCURRENT_TRANS)

Other Related Issues

• Case 1
  
  Consider the following scenario:
  
  You have two COM components registered under MTS named ComA and ComB. Both are enlisted in the same transaction and both are marked Requires Transaction. ODBC Connection pooling is turned on and OLE DB session pooling is turned off.
  
  
  1. ComA connects to the database.
  2. ComA performs a SELECT, UPDATE or DELETE statement, and then disconnects.
  3. ComA creates an instance of ComB.
  4. ComB connects, performs some work, and then disconnects.
  5. ComB calls SetComplete.
  6. ComA calls SetComplete.
  Question: Is it possible that ComB gets the same connection as ComA? That is, after ComA closes the connection without calling SetComplete, ComB then requests a connection. Is there any chance that ComB gets the same connection as ComA? Will MTS do anything to guarantee this?
  
  Answer: It is not guaranteed that ComB will get back the same connection. ComB may or may not get back the same connection. MTS does not do anything special here. You may or may not get the same connection back.
  
  NOTE: In Microsoft Windows 2000 using COM+, there is a supported fix for this problem. Please contact Microsoft for additional information.
  
  A requirement of the IBM DB2 driver is that the same transaction should work on the same connection. This is a very special requirement for DB2. To guarantee this requirement, the application developer must implement their own mechanism. One way to do this is to pass the connection handle from ComA to ComB without closing it inside ComA.
  
  For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

  275455 INFO: Post Windows 2000 Service Pack 1 COM+ Rollup Hotfix 3

  277814 FIX: COM+ Dispenser Manager Does Not Efficiently Reuse Open Enlisted Database Connections

• Consider the following scenario:Case 2
  
  You have two COM components named ComA and ComB registered under MTS. Each component is enlisted in different transactions and is marked as Requires Transaction. Connection pooling is turned on and session pooling is turned off.
  1. ComA initiates a transaction (Transaction1) and connects to DB2 by using the IBM DB2 ODBC driver.
  2. The connection is enlisted on a transaction in MTS (and therefore within DB2) to run a command. ComA then runs some SQL statements.
  3. ComA closes the connection, but Transaction1 is not complete yet.
  4. ComB starts a new transaction (Transaction2).
  5. ComB requests a connection by using the same connection string that ComA used.
  Question: Will MTS guarantee that Transaction2 will not get back the connection created in Transaction1?
  
  Answer: MTS should handle this case, and Transaction2 should not get back the connection that was enlisted for Transaction1.