Distributed Transaction from .NET to DB2

Question

I have a problem where in I have to call into the AS400 db2 to call a series of insert/update stored procedures from my .net and then update a few SQL2005 tables if everything in the AS400 db goes ahead fine. The driver I am using does not have distributed transactions so I was wondering if you can use any of IBMs iSeries Drivers for DB2 to create a COM+/.NET Component which would participate in a distributed transaction. Pls let me know if any one out there has done anything like this before or any pointers would be greatly appreciated.

Answer 1

Which driver are you using?

IBM's DB2 Connect supports distributed transactions managed by Windows DTC.

The DB2 driver from Microsoft, which is packaged in Microsoft Host Integration Server, also does transactions.

I am hearing two different things from you. You want to do a transaction that spans SQL and DB2. and also you want to build a component that participates in a distributed transaction. These can be done together but the former does not require the latter.

Using the System.Transactions classes in .NET 2.0, you can do this:

  TransactionOptions options = new TransactionOptions();
  options.IsolationLevel = IsolationLevel.Serializable;
  options.Timeout = TransactionManager.DefaultTimeout;
  using(var scope = new TransactionScope(TransactionScopeOption.Required, 
        options, 
        EnterpriseServicesInteropOption.Full) )
  {
      TransactionalWorkinDb2();
      TransactionalWorkinSql();
      scope.Complete();
  }

The result will be a distributed transaction that spans DB2 and SQL, but it is not a component that participates in a distributed transaction, if you see what I mean.

The other alternative is to use the EnterpriseServices/COM+ model of defining a component that does something, and marking that component as transactional. This works too, but is not required to do distributed transactions in .NET.