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.

using System.Transactions;

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

Back to top

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.

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.

Back to top

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.

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.

Back to top

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.

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.

Back to top

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.

Back to top

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

Back to top

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.

Back to top

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.

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

Back to top

Troubleshooting

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

Back to top

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

• Refer to the online help for the DB2 .NET provider for more information about the list of servers supported by the DB2 .NET provider.
  
  
• Browse through the appropriate Microsoft documentation for how to configure MSDTC to enable XA transaction.
  
  
• See the DB2 online help for details about configuring TCP/IP communications for a DB2 instance.
  
  
• Learn about the enableDirectXA setting in the DB2 online help.
  
  
• Find the DB2 online help for more information about the testconn utility.
  
  
• Read "Enable C++ applications for Web services using XML-RPC" (developerWorks, Jun 2006) for a step-by-step guide to exposing C++ methods as services.
  
  
• In the XML area on developerWorks, get the resources you need to advance your XML skills, including DTDs, schemas, and XSLT.
  
  
• Watch the developerWorks on-demand demos, which range from product installation and setup demos for beginners to advanced function for experienced developers.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Follow developerWorks on Twitter.
  
  

Get products and technologies

• Download IBM DB2 9.7 for Linux, UNIX, and Windows and explore the core features in order to build and deploy your applications.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Kanchana 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.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Before you begin
• Using the automatic enlistment method
• Using the manual enlistment method
• Switching between global and local transactions
• Understanding pooling
• Running global transactions against DB2 for z/OS
• Controlling whether the connection performs global transaction directly
• Using the testconn utility
• Troubleshooting
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers