Connection to a DB2 database using a linked server with the following connection string:

Provider=DB2OLEDB;User ID=XXX;Password=XXX;Initial Catalog=XXX;Network Transport Library=TCP;Host CCSID=28591;PC Code Page=1252;Network Address=XXX;Network Port=3710;Package Collection=dbm;Default Schema=dbm;Process Binary as Character=False;Units of Work=DUW;DBMS Platform=DB2/NT;Persist Security Info=True;Cache Authentication=False;Connection Pooling=False;Derive Parameters=False;

Everything works fine when not running inside of an distributed transaction and using "Units Of Work=RUW". But as we need to run inside of an distributed transaction we're using DUW and receives an error saying: 

OLE DB provider "DB2OLEDB" for linked server "XXX" returned message "Distributed units of work are not supported on this version of Host Integration Server.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "XXX".

The need for distributed transaction is because the query is initialized by BizTalk server that wraps the query within a distributed tran.

Richard,

What version of the Microsoft OLE DB Provider for DB2 are you using?

If you are using one of the standalone OLE DB Providers for DB2 that are included with SQL Server Feature Packs, then they didn't support distributed transactions until the most current release. The current version is Microsoft OLE DB Provider for DB2 5.0 included with the SQL Server 2014 Feature Pack.

All previous versions (4.0 and earlier) only support remote units of work (RUW). Here is an excerpt from the version 4.0 online docs:

http://msdn.microsoft.com/en-us/library/hh872998(v=sql.110).aspx

This property is disabled in the Microsoft OLE DB Provider for DB2 v4.0. It is enabled with the version of the provider that is used with Host Integration Server 2010.

The version 5.0 docs state the following:

http://msdn.microsoft.com/en-us/library/dn539005.aspx

Distributed transactions

Optionally, to enlist the Data Provider in distributed transactions, you can select this property to support two-phase commit protected DB2 DUW (distributed unit of work). You must enable MSDTC to support XA transactions. For more information, see http://go.microsoft.com/fwlink/p/?linkid=217339.

If you are using the OLE DB Provider for DB2 included with Host Integration Server, then those versions support distributed transactions.

Thanks...

Thanks for the great and thorough reply Stephen! I do however hope that you still monitor the thread as it didn't really solve things for us.

It did however help us clarify that this should work as we're now using the provider from HIS 2010 and CU9.

As we're now executing the store procedure from the 64 bit BizTalk process we're getting the following response.

System.Data.SqlClient.SqlException (0x80131904): Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

Server stack trace: 
   at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

There are suggesting on the web that this could be caused by a timeout in the DTC. I don't think the is the case for us as our procedure is a simple insert/update one that should be done in seconds.

Some also suggest that could actually be caused by an error thrown by the procedure and that the error text in that case is a bit misleading. But we tested the procedure a number a times to make sure that isn't the case.

Other thoughts what might give us this error?

I our testing scenario the BizTalk runtime, the database with the linked server and the BizTalk databases are run one same machine using the local DTC so DTC communication errors etc shouldn't really be the problem here either. Also everything is active and wide open in the DTC so I can really see that it should be a security or restriction problem there.

Again, really appreciate all help we can get here!

Just to make sure I understand the scenario correctly. The DB2 Provider is being used in a Linked Server in SQL Server. You are not using the BizTalk Adapter for DB2 to query DB2. Correct?

You also mention a Stored Procedure being called from BizTalk Server.

If you are using a Linked Server to connect to DB2, can you share the format of the queries (INSERT/UPDATES) that you are using and if they are wrapped in a transaction?

For example:

SET XACT_ABORT ONBEGIN DISTRIBUTED TRANINSERT INTO DB2TEST.PRODDB2.USER1.TABLE1 VALUES (99999, 'Everywhere', 999)COMMIT TRANSET XACT_ABORT OFF

Thanks...

Yes. The provider from HIS is used in a linked server that connects to a DB2. we're not using the standalone BizTalk adapter. We do however call a stored procedure in the Sql Server that references the linked DB2 server using BizTalk.

As BizTalk calls the procedure it wraps it in a distributed transaction as it also makes a few calls to the BizTalk databases to make sure everything goes through before it releases the message from BizTalk - this is just normal procedure in BizTalk as you know. 

The store procedure is a plain vanilla sql update and insert. No transaction or anything is used within the procedure (even though BizTalk wraps the call as mentioned above).

Any chance you can test the Linked Server query directly without the SP call from BizTalk?

I'm curious to know if you get an error when doing the query when the connection string has Units of Work=DUW when the query is not in an explicit "BEGIN DISTRIBUTED TRAN" block. Typically, this will fail with an error indicating that the provider doesn't support the required transaction interface.

I've never tried calling a SQL SP from a BizTalk Server application that uses the DB2 Provider to call DB2, so I'm not sure offhand how the distributed transaction from BizTalk Server translates to the Linked Server query kicked off by the SP.

I created a SP that does an INSERT into a DB2 table with distributed transactions enabled. If I don't include the "Begin Distributed Tran", "Commit Tran" block around the INSERT, I get the error I mentioned previously.

Of course, I haven't tried calling the SP from BizTalk where that call is in a distributed transaction. 

Hi Richard,

Im receiving pretty much the same issue where a stored procedure being executed  by a WCF-Custom (Sql Binding) from BizTalk Server to a DB2 Linked Server.

The Linked Server is using the following Provider

IBM DB2 for I IBMDA400 OLE DB Provider

The stored procedure is wrapped in SET XACT_ABORT ON/SET XACT_ABORT OFF and it has two operations in DB2 through a Microsoft SQL stored procedure -retrieves data and updates those records so they wont be retrieved again. 

It only works if I set UseAmbientTransaction=False BUT have noticed some message loss when retrieving records with no trace of errors in BizTalk. Typically this is due to UseAmbientTransaction=False.  Has anybody noticed this as well AND is there a way to do this with UseAmbientTransaction=True without getting the following error?

If you can assist it would be greatly appreciated!

Thanks,