You receive a "Server: Msg 7391" error message when you try to perform a distributed transaction by using a Microsoft OLE DB Provider for DB2 linked server in SQL Server (899607)


The information in this article applies to:

  • Microsoft Host Integration Server 2004

SYMPTOMS

When you try to perform a distributed transaction by using the Microsoft OLE DB Provider for DB2 (DB2OLEDB) that is included with Microsoft Host Integration Server 2004, you receive the following error message from SQL Server:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'DB2OLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'DB2OLEDB' ITransactionJoin::JoinTransaction returned 0x80040e14].
This problem occurs when the following conditions are true:
  • You are performing a two-phase commit (2PC) distributed transaction.
  • The OLE DB Provider for DB2 data source has been configured to use Connection Pooling in Host Integration Server 2004.

CAUSE

This problem occurs if OLE DB Provider for DB2 is configured to use both connection pooling and Distributed Transactions (2PC) on the same instance.

RESOLUTION

To resolve this problem, you must disable connection pooling. To do this, use one of the following methods.

Method 1: Connection string

If you are using a connection string, add the Connection Pooling=false property to the connection string. For example, use a connection string that is similar to the following:
Provider=DB2OLEDB;User ID=TESTUSER;Password=TESTPASS;
Initial Catalog=BIGBLUE;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;
Network Address=TESTHOST;Network Port=446;Package Collection=TESTCOL;
Default Schema=TESTSCHEMA;Process Binary as Character=False;Units of Work=DUW;
DBMS Platform=DB2/AS400;Persist Security Info=True;Connection Pooling=false;

Method 2: UDL file

If you are using a Universal data link (UDL) file, follow these steps:
  1. Click Start, click All Programs, click Microsoft Host Integration Server 2004, and then click Data Access Tool.
  2. In the Data Access Tool, right-click the data source that you want to change, and then click Edit Data Source.
  3. Click Next five times.
  4. On the Advanced Options screen, click to clear the Connection Pooling check box, and then click Next three times.
  5. Click Finish to complete the change.

MORE INFORMATION

Connection Pooling and Distributed Transactions (2PC) should not be used together. A connection that uses Distributed Transactions is not closed when an application calls the Close method. This behavior occurs because of the way that the 2PC works with DB2. Essentially, the connection must remain open in an asynchronous way until the last phase of the 2PC transaction has completed.

Sometimes, in the Distributed Relational Database Architecture (DRDA) implementation of 2PC, the last phase of the 2PC transaction is not processed until the reply to the next command is received. Therefore, a transaction of a 2PC connection is linked to the work of another 2PC connection.

Note Host Integration Server 2004 introduced Distributed Transactions (2PC) over TCP/IP. To enable Distributed Transactions, Units of Work must equal Distributed Unit of Work (DUW). By default, Units of Work equals Remote Units of Work (RUW). In the example connection string in the "Resolution" section, the Data Source property Units of Work is correctly set to DUW.
Modification Type:MajorLast Reviewed:6/9/2005
Keywords:kbDB2 kbinfo kbtshoot kbprb KB899607 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.