Developing heterogeneous database applications using the IBM Data Server Provider for .NET

Working with distributed transactions and IBM Data Server Provider for .NET

Transactional logic that works across heterogeneous databases needs to use global transactions. The IBM Data Server Provider for .NET supports the distributed transaction model from the System.Transactions namespace, which has the capability to create and manage global transactions. The code examples in this article show how to use the IBM Data Server Provider for .NET to create and manage global transactions on the IBM data servers.

Share:

Kanchana Padmanabhan (kanchana@us.ibm.com), Advisory Software Engineer, IBM

Kanchana PadmanabhanKanchana Padmanabhan has been working on client connectivity tools for IBM Data Servers for the past 11 years. She is the technical lead for the IBM Data Server Providers for .NET team.



02 February 2012

Also available in Chinese

Introduction

Enterprise systems are typically comprised of heterogeneous databases. The transactional logic very frequently would need to make multiple connections to one or more of these heterogeneous databases. To maintain the atomicity of the transactional operation, applications need to use the distributed transaction model. The implementation of this model in the .NET framework simplifies the use. The code examples in this article show how to use the IBM Data Server Provider for .NET to create and manage global transactions on the IBM data servers.

The System.Transactions namespace in the .NET framework includes capabilities to create and manage global transactions that operate across multiple connections over one or more databases. The Microsoft Distributed Transaction Coordinator service (MSDTC) coordinates these global transactions. The IBM Data Server Provider for .NET extends the capabilities of the System.Transactions namespace to support global transactions against the IBM database servers. For more information about the list of servers supported by the DB2® .NET provider, refer to the online help for the DB2 .NET provider (see Resources).

This article illustrates the use of the IBM Data Server Provider for .NET to create and manage global transactions. The code samples show the different ways to enlist the connections to participate in a global transaction. The samples are written in C++, and they use IBM Data Server Provider for .NET. The transactional logic is performed across two databases: one named salesDB, which has information about customers and their sales orders, and the other named invDB, which has information about the products and their inventory. The transaction performed is to process an order that involves inserting into the sales table and updating the available quantity in the products table.

Before you begin

The DB2 .NET Provider uses the XA protocol to implement the support for the distributed transaction model. Hence MSDTC should be configured to enable XA transaction. For more information about the configuration, refer to the appropriate Microsoft documentation (see Resources).

The DB2 .NET Provider supports global transaction only over TCP/IP connections. If you are using your local database server, ensure that you have configured your instance for TCP/IP communication (see Resources).

The System.Transactions namespace is comprised of the classes that provide the capabilities for the distributed transactional model. Use this namespace in your application by adding the code in Listing 1 to the System.Transactions namespace in the System.Transactions.dll assembly.

Listing 1. Using the namespace
using System.Transactions;

Your project should add a reference to the System.Transactions.dll assembly.


Using the automatic enlistment method

Automatic enlistment is the default and recommended method of doing global transactions. The steps are as follows:

  1. Create an ambient transaction.
  2. Establish the database connections.
  3. Do work on each connection.
  4. Complete the transaction.

Listing 2 shows an example of code to complete the steps for automatic enlistment.

Listing 2. Sample code for automatic enlistment
using (TransactionScope ts = new TransactionScope())
    { 
        DB2Connection conn1 = new DB2Connection(
                                  "Database=salesdb; Server=salesServer:446;" +
                                  "UserID=user1; Password=xxxxxx"); 
        conn1.Open(); 

        DB2Command cmd1 = conn1.CreateCommand(); 
        cmd1.CommandText =                                              //do a sale
            "INSERT INTO sales (custID, prdID, quantity) VALUES(100, 10001, 50)"; 
        cmd1.ExecuteNonQuery(); 

        DB2Connection conn2 = new DB2Connection(
                                   "Database=invDB; Server=invServer:50000;" +
                                   "UserID=user2; Password=xxxxxx"); 
        conn2.Open(); 

        DB2Command cmd2 = conn2.CreateCommand(); 
        cmd2.CommandText =                                             //reduce inventory
            "UPDATE products SET quantity = quantity - 50 WHERE prdid = 10001"; 
        cmd2.ExecuteNonQuery(); 

        conn1.Close();
        conn2.Close();

        ts.Complete();
    }

In Listing 2, the ambient transaction is created when the TransactionScope object is instantiated. An ambient transaction is indicated by Transaction.Current not being null. The connections opened within the scope of the ambient transaction, which in the example is the using block, will automatically enlist in the global transaction. The call to Complete commits the ambient transaction. If for any reason (most commonly an exception) the Complete method is not called, the global transaction is rolled back.


Using the manual enlistment method

The manual enlistment method does not have an ambient transaction. The application explicitly creates a transaction object and uses it to manually enlist the connection in the transaction. You must set enlist=false in the connection string to do a manual enlistment. The steps involved in this method are as follows:

  1. Create a transaction object.
  2. Enlist connections with the transaction object.
  3. Do work on the enlisted connections.
  4. Complete the transaction.

Listing 3 shows an example of code to complete the steps for manual enlistment.

Listing 3. Sample code for manual enlistment
DB2Connection conn1 = new DB2Connection(
                                "Database=salesdb; Server=salesServer:446;" + 
                                "UserID=user1; Password=xxxxxx; enlist=false"); 
    conn1.Open(); 
    DB2Connection conn2 = new DB2Connection(
                                "Database=invDB; Server=invServer:50000;" + 
                                "UserID=user2; Password=xxxxxx; enlist=false"); 
    conn2.Open(); 
    CommittableTransaction cmtxn = new CommittableTransaction(); 
    try 
    { 
        conn1.EnlistTransaction(cmtxn); 

        DB2Command cmd1 = conn1.CreateCommand(); 
        cmd1.CommandText =                                              //do a sale
            "INSERT INTO sales (custID, prdID, quantity) VALUES(100, 10001, 50)"; 
        cmd1.ExecuteNonQuery(); 

        conn2.EnlistTransaction(cmtxn); 
        DB2Command cmd2 = conn2.CreateCommand(); 
        cmd2.CommandText =                                             //reduce inventory
            "UPDATE products SET quantity = quantity - 50 WHERE prdid = 10001"; 
        cmd2.ExecuteNonQuery(); 

        cmtxn.Commit(); 
    } 
    catch (Exception) 
    { 
        cmtxn.Rollback(); 
    }

In Listing 3, note that the connection need not be opened after the transaction is created. This enables the application to have a bit more modularity in its code. This method also involves a bit more coding, but there are some advantages to it, which are described in the next section.


Switching between global and local transactions

When using the manual enlistment method, the application can use the connection to do work outside of a global transaction. The application can use the connection to do local transactions either before the enlistment or after un-enlistment. Listing 4 shows how to switch between global and local transactions.

Listing 4. Sample code that switches between global and local transactions
DB2Connection conn1 = new DB2Connection(
                                "Database=salesdb; Server=salesServer:446;" + 
                                "UserID=user1; Password=xxxxxx; enlist=false"); 
    conn1.Open(); 
    DB2Connection conn2 = new DB2Connection(
                                "Database=invDB; Server=invServer:50000;" + 
                                "UserID=user2; Password=xxxxxx; enlist=false"); 
    conn2.Open(); 
    CommittableTransaction cmtxn = new CommittableTransaction(); 
    try 
    { 
        conn1.EnlistTransaction(cmtxn); 

        DB2Command cmd1 = conn1.CreateCommand(); 
        cmd1.CommandText =                                              //do a sale
            "INSERT INTO sales (custID, prdID, quantity) VALUES(100, 10001, 50)"; 
        cmd1.ExecuteNonQuery(); 

        conn2.EnlistTransaction(cmtxn); 
        DB2Command cmd2 = conn2.CreateCommand(); 
        cmd2.CommandText =                                             //reduce inventory
            "UPDATE products SET quantity = quantity - 50 WHERE prdid = 10001"; 
        cmd2.ExecuteNonQuery(); 

        cmtxn.Commit(); 
    } 
    catch (Exception) 
    { 
        cmtxn.Rollback(); 
    } 
    conn2.EnlistTransaction(null);
    //call stored procedure place more order to replenish inventory
    cmd2.CommandText = "CALL ReplenishStock(?)"; 
    cmd2.Parameters.Add("@prdid", 10001");
    cmd2.ExecuteNonQuery();
   
    conn1.EnlistTransaction(null);
    DB2Transaction txn = conn1.BeginTransaction();
    cmd1.Transaction = txn;
    try
    {
        cmd1.CommandText =           //increase the count of sales given by this customer
             "UPDATE customers SET salesCount = salesCount + 1 WHERE custID = 100"; 
        cmd1.ExecuteNonQuery();
        cmd1.CommandText =           //make him eligible for 10% discount for future
            "INSERT INTO discounts (custID, prdid, discount) VALUES (100, 10001, 10)"; 
        cmd1.ExecuteNonQeury();
        txn.Commit();
    }
    catch
    {
        txn.Rollback();
    }

In Listing 4, conn2 does not need the overhead of a transaction when it wants to call the ReplenishStock stored procedure. The application calls the EnlistTransaction method with a null transaction ID to unenlist the connection. The work on conn1 to update the customers table and insert into the discounts table needs to be done within a transaction. But because the operations are on the same connection, a local transaction would suffice. The application unenlists the connection and creates a local transaction by calling the BeginTransaction method.

Note that the code in Listing 4 requires the fix for APAR IC77576. The fix for it is available in DB2 for z/OS 9.7 Fixpack 5 or later versions of IBM Data Server Client Package. Without the APAR fix, you will receive a DB2Exception exception. Refer to the APAR for more details.


Understanding pooling

The distributed transaction model implementation requires connection pooling to be turned on. Pooling is on by default. Turning off pooling explicitly results in a TransactionAbortedException exception during the application run.


Running global transactions against DB2 for z/OS

The following database servers support global transactions only when connected using a DB2 Connect gateway.

  • DB2 Universal Database for z/OS, Version 8
  • DB2 Universal Database for z/OS, Version 9 without APAR PK69659

Controlling whether the connection performs global transaction directly

DB2 for z/OS, Version 10, and DB2 for z/OS, Version 9 with APAR PK69659, introduce the support to perform global transactions without the DB2 Connect gateway. The enableDirectXA setting in the db2dsdriver.cfg file can be used to control whether the connection performs global transaction directly on the database server or using a DB2 Connect gateway. See Resources for more information about the enableDirectXA setting.


Using the testconn utility

The testconn utility can be used to validate whether the IBM Data Server Driver Package installation and the MSDTC are configured correctly to perform global transactions against the IBM data servers. The -dtc switch should be used for the validation. See Resources for information about the testconn utility. Listing 5 shows the output of the testconn utility reporting an error and suggesting a possible solution to the problem.

Listing 5. Validation with the testconn utility
Step 6: Creating XA connection
        DB2TransactionScope: Failed to open connection to database!
IBM.Data.DB2.DB2Exception: ERROR [58005] [IBM][DB2/NT] SQL0998N  Error occurred
during transaction or heuristic processing.  Reason Code = "16". Subcode = "2-80
04D026".
   at IBM.Data.DB2.DB2Connection.Open()
   at TestConn.DB2TransactionScope.Open(String connstr)

        Looking for XADLL key for E:\SQLLIB\bin\db2app.dll
           Correct entry found
           Check if XA Transactions are enabled:
              Windows 7, 2008, Vista -
                Component Services, Computers, My Computer,
                Distributed Transaction Coordinator, Local DTC
                Properties, Security tab, Enable XA Transactions
              Other windows versions -
                Component Services, Computers, My computer, Properties
                MSDTC tab, Security configuration, Enable XA Transactions

Troubleshooting

Table 1 lists the errors frequently encountered by applications when performing global transactions and their possible solutions.

Table 1. Errors, scenarios, and possible corrective actions
OperationError detailsCorrective action
Project compilationUnable to resolve CommittableTransaction or TransactionScopeAdd System.Transactions.dlli to the project references. Include using System.Transactions; in the code.
DB2Connection.OpenDB2Exception, SQLState - 58005, SQL0998N Error occurred during transaction or heuristic processing. Reason Code = "16". Subcode = "2-8004D026".Ensure that the Enable XA transaction box in checked for the MSDTC security configuration tab.
DB2Exception, SQLState - 58005, SQL0998N Error occurred during transaction or heuristic processing. Reason Code = "16". Subcode = "2-8004D025".Verify if the XADLL entry is valid.
DB2Exception, SQLState - 580051. If you are connecting to a server that supports XA directly, and if you using the IBM Data Server Client Package (and not the IBM Data Server Driver Package), set enableDirectXA to true in the db2dsdriver.cfg file. 2. If you are using the IBM Data Server Driver Package, ensure enableDirectXA is not disabled. 3. If you are connecting to a server that does not support XA directly, connect through a DB2 Connect gateway.
Completing the global transactionTransactionAbortedException, Message - The transaction has aborted.Make sure you have set Pooling=false in the connection string. Pooling must be enabled on connections participating in global transactions.
DB2Connection.BeginTransactionDB2Exception, SQLState - HY011, Message CLI0126E Operation invalid at this time.Because the connection is enlisted in a global transaction, the call to BeginTransaction is not allowed.
DB2Connection.EnlistTransactionSystem.InvalidOperationException, Message - Connection currently has transaction enlisted.Because the connection is enlisted automatically, the call to EnlistTransaction is not allowed.

Conclusion

The article explained that the implementation of the distributed transaction model in .NET simplifies the use of the model in .NET applications. Apart from creating a transaction object and writing logic to complete the transaction, your application would look the same with or without global transactions. This is because all the complex logic to create and manage the global transactions is handled within the IBM Data Server Provider for .NET and the .NET Framework. You are left with an application that is simple yet powerful enough to meet the needs of your enterprise.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=790287
ArticleTitle=Developing heterogeneous database applications using the IBM Data Server Provider for .NET
publish-date=02022012