Knowledge Base

INFO: IBM DB2 ODBC Driver and Microsoft Transaction Server

Article ID: 269735

Article Last Modified on 12/5/2003

APPLIES TO

Microsoft Data Access Components 2.1
Microsoft Data Access Components 2.5
Microsoft Data Access Components 2.6
Microsoft Transaction Services 2.0
Microsoft ActiveX Data Objects 2.1
Microsoft ActiveX Data Objects 2.5
Microsoft ActiveX Data Objects 2.6

This article was previously published under Q269735

SUMMARY

This article describes the steps that you should use while performing distributed transactions under Microsoft Transaction Server (MTS) when you use the IBM DB2 ODBC driver version 6.01 or later.

This article assumes that you are developing COM components by using ActiveX Data Objects (ADO) which are registered under MTS, and that you are using the IBM DB2 driver version 6.01 or later to connect to your DB2 back end database.

MORE INFORMATION

The following are required to enable "Connection Pooling" and "Auto Enlistment" when you use the IBM DB2 driver under MTS with ADO:

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.

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.

REFERENCES

For additional information about turning on connection pooling from a Visual Basic ADO application, click the article number below to view the article in the Microsoft Knowledge Base:

237844 HOWTO: Enable ODBC Connection Pooling in a Visual Basic ADO Application

For additional information about the connection pooling counter in Perfmon, click the article number below to view the article in the Microsoft Knowledge Base:

245543 INFO: ODBC Connection Pooling Counters in Performance Monitor

For more information about the IBM DB2 driver, please see the following Web site:

http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7d0db2d056.htm#HDRMICROSOFTXATRANS

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.

Keywords: kbinfo kbmts200 kbcodesnippet kb3rdparty kbdb2 kbgrpdsvcdb kbgrpdsmdac kbmts kbodbc KB269735

Microsoft KB Archive/269735

Contents