How to Perform a Two-Phase Commit Transaction over TCP/IP

Two-phase commit (2PC) is a host server-installed protocol that ensures that updates to multiple instances of a database on a network either succeed or fail in their entirety. Host Integration Server supports 2PC over TCP/IP, enabling you to gain the security of a 2PC connection over the Internet.

Host Integration Server supports 2PC works using two components: the Microsoft Distributed Transaction Coordinator (DTC), and the transaction log. The DTC governs the normal DTC transaction flow: enlist, prepare, commit, and abort. Also, DTC handles transaction recovery in case of any failure or disconnection, while the transaction log maintains a log of information that is needed in case of recovery.

You can perform a 2PC transaction with ADO.NET and the Managed Provider for DB2 by using the System.Transactions namespace. Using a 2PC transaction is automatic, when you configure the connection property Units of Work=DUW.

Example

The following code example demonstrates how to use 2PC in a DB2 transaction.

Caution

This example or guidance references sensitive information, such as a connection string or a username and password. Never hardcode these values in your code, and make sure that you protect confidential data by using the most secure authentication available. For more information, see the following documentation:

• Security in BizTalk Server
• Security for sending and receiving messages
• Access control and data security

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Data;  
using System.Transactions;  
using Microsoft.HostIntegration.MsDb2Client;  
  
namespace Db2DistributedTransaction  
{  
    class Program  
    {  
        static void Main(string[] args)  
        {  
            Db2Tx();  
        }  
  
        static void Db2Tx()  
        {  
            string connectionStringDb2 = "Password=HISDEMO;User ID=HISDEMO;Initial Catalog=DSN1D037;Data Source=DSN8910;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1208;Network Address=123.34.45.57;Network Port=446;Package Collection=HISDEMO;Default Schema=DSN8910;Default Qualifier=DSN8910;Units of Work=DUW;Defer Prepare=True;AutoCommit=False";  
            System.Transactions.CommittableTransaction transaction = new System.Transactions.CommittableTransaction();  
            MsDb2Connection connection = new MsDb2Connection(connectionStringDb2);  
            MsDb2Command command = new MsDb2Command();  
            try  
            {  
                connection.Open();  
                connection.EnlistTransaction(transaction);  
                command.Connection = connection;  
                command.CommandText = "INSERT INTO DSN8910.AREAS (AREAID, AREADESC, REGIONID) VALUES ('11111', 'Area 11111', 111)";  
                command.ExecuteNonQuery();  
                command.CommandText = "INSERT INTO DSN8910.AREAS (AREAID, AREADESC, REGIONID) VALUES ('22222', 'Area 22222', 222)";  
                command.ExecuteNonQuery();  
                while (true)  
                {  
                    Console.Write("Commit or Rollback? [C|R] ");  
                    ConsoleKeyInfo c = Console.ReadKey();  
                    Console.WriteLine();  
                    Console.ReadKey();  
  
                    if ((c.KeyChar == 'C') || (c.KeyChar == 'c'))  
                    {  
                        transaction.Commit();  
                        break;  
                    }  
                    else if ((c.KeyChar == 'R') || (c.KeyChar == 'r'))  
                    {  
                        transaction.Rollback();  
                        break;  
                    }  
                }  
                connection.Close();  
                transaction = null;  
            }  
            catch (Exception ex)  
            {  
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());  
                Console.WriteLine("  Message: {0}", ex.Message);  
                Console.ReadKey();  
            }  
            Console.WriteLine("Program end.");  
            Console.ReadKey();  
        }  
    }
}

See Also

Working with the Managed Provider for DB2