With .NET, you use the TransactionScope class to handle 2-phase commit.

.NET uses Microsoft Distributed Transaction Coordinator (MSDTC) to coordinate the transactions across databases. DB2 uses XA transactions for distributed transactions, and there is some configuration you have to do on the machine running the code for MSDTC. Search "Component Services" to load the Control Panel, then go Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC. Right-click on it and hit properties, then go to the Security tab and check "Enable XA Transactions".

Some of the gotchas:

• The connection must be opened inside of the TransactionScope, that is how .NET is aware to enroll it in the distributed transaction
• `scope.Complete()`does not commit the code in the block, it simply says that block was successful. The commit happens when the last "outer" scope block is disposed of. If there are child scopes inside that scope, then all scopes must have been marked as complete and disposed for it to commit.
  
  • You should pretty much use a using statement for all TransactionScopes, it just makes the code easier to ensure it's doing the correct thing.

Here's a short example of some code using TransactionScope to bulk-load a table. (I'd post it as a code block, but the community page errors out if I do that...)

-------------------------------------------------------------

public void DoDatabaseWork()
{
    using (var scope = GetTransactionScope())
    using (var conn = GetConnection("dbName"))
    {
        var curTstamp = DateTime.Now;
        var tableName = "DBA.TEST_TABLE";
    
        using (var adapter = new DB2DataAdapter($"SELECT * FROM {tableName}", conn))
        using (var table = new DataTable())
        {
            adapter.FillSchema(table, SchemaType.Source);
            for (int i = 0; i < 1000; i++)
            {
                var row = table.NewRow();
                row["COL1"] = "ABC";
                row["COL2"] = "123";
                row["TS_COL"] = curTstamp;
                row["NULL_COL"] = DBNull.Value;
                table.Rows.Add(row);
            }
            
            using (var bc = new DB2BulkCopy(conn))
            {
                bc.DestinationTableName = tableName;
                bc.WriteToServer(table);
            }
        }

        scope.Complete();
    } // using scope/conn
}

-------------------------------------------------------------

I also recommend having a function that creates the scope and sets some options. The defaults are pretty bad for concurrency. It uses Serializable by default, which is equivalent to DB2 WITH RR (Repeatable Read).

-------------------------------------------------------------

public static TransactionScope GetTransactionScope(
    TransactionScopeOption scopeOption = TransactionScopeOption.Required,
    IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
    return new TransactionScope(scopeOption, new TransactionOptions
    {
        Timeout = TransactionManager.MaximumTimeout,
        IsolationLevel = isolationLevel,
    });
}

-------------------------------------------------------------

TransactionScope is kinda magic (in the good and bad senses), but hopefully this will get you on the right path!

Thank you for the answer. 

I am still confused (probably because I believe that .net is for spiders only)...

What I am trying to do is something called multisite update - something like this:

image001.gif@01DA52E3.92FB0870" class="img-responsive" data-mce-hlimagekey="a02c4b7c-dcc3-bf64-6563-55085baa31ee" data-mce-hlselector="#ReplyInline_0b752e6a469147a98d4c7f51937eaf09-tinyMce">

This I coded in CLP - was trivial and straightforward - I don't understand how can I set connection to each of the participating database in single UOW in .net -

Fragment of my CLP proof of concept:

------------------------------
Jan Nelken

Original Message:
Sent: Tue January 30, 2024 09:27 AM
From: Bryan Hamby
Subject: 2 Phase Commint from .Net code?

With .NET, you use the TransactionScope class to handle 2-phase commit.

.NET uses Microsoft Distributed Transaction Coordinator (MSDTC) to coordinate the transactions across databases. DB2 uses XA transactions for distributed transactions, and there is some configuration you have to do on the machine running the code for MSDTC. Search "Component Services" to load the Control Panel, then go Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC. Right-click on it and hit properties, then go to the Security tab and check "Enable XA Transactions".

Some of the gotchas:

• The connection must be opened inside of the TransactionScope, that is how .NET is aware to enroll it in the distributed transaction
• `scope.Complete()`does not commit the code in the block, it simply says that block was successful. The commit happens when the last "outer" scope block is disposed of. If there are child scopes inside that scope, then all scopes must have been marked as complete and disposed for it to commit.
  
  • You should pretty much use a using statement for all TransactionScopes, it just makes the code easier to ensure it's doing the correct thing.

Here's a short example of some code using TransactionScope to bulk-load a table. (I'd post it as a code block, but the community page errors out if I do that...)

-------------------------------------------------------------

public void DoDatabaseWork()
{
    using (var scope = GetTransactionScope())
    using (var conn = GetConnection("dbName"))
    {
        var curTstamp = DateTime.Now;
        var tableName = "DBA.TEST_TABLE";
    
        using (var adapter = new DB2DataAdapter($"SELECT * FROM {tableName}", conn))
        using (var table = new DataTable())
        {
            adapter.FillSchema(table, SchemaType.Source);
            for (int i = 0; i < 1000; i++)
            {
                var row = table.NewRow();
                row["COL1"] = "ABC";
                row["COL2"] = "123";
                row["TS_COL"] = curTstamp;
                row["NULL_COL"] = DBNull.Value;
                table.Rows.Add(row);
            }
            
            using (var bc = new DB2BulkCopy(conn))
            {
                bc.DestinationTableName = tableName;
                bc.WriteToServer(table);
            }
        }

        scope.Complete();
    } // using scope/conn
}

-------------------------------------------------------------

I also recommend having a function that creates the scope and sets some options. The defaults are pretty bad for concurrency. It uses Serializable by default, which is equivalent to DB2 WITH RR (Repeatable Read).

-------------------------------------------------------------

public static TransactionScope GetTransactionScope(
    TransactionScopeOption scopeOption = TransactionScopeOption.Required,
    IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
    return new TransactionScope(scopeOption, new TransactionOptions
    {
        Timeout = TransactionManager.MaximumTimeout,
        IsolationLevel = isolationLevel,
    });
}

-------------------------------------------------------------

TransactionScope is kinda magic (in the good and bad senses), but hopefully this will get you on the right path!

Yes, TransactionScope is the way you do multi-database updates in .NET and have the commit/rollback as a unit (2-phase commit). 

Basically, you just create the TransactionScope block, open a connection to the databases involved (one connection object per database) and perform the work needed, then call scope.Complete() at the end.

.NET coordinates the commits across all participating databases for you.

------------------------------
Bryan Hamby

Original Message:
Sent: Tue January 30, 2024 10:46 AM
From: Jan Nelken
Subject: 2 Phase Commint from .Net code?

Thank you for the answer. 

I am still confused (probably because I believe that .net is for spiders only)...

What I am trying to do is something called multisite update - something like this:

image001.gif@01DA52E3.92FB0870" class="img-responsive" data-mce-hlimagekey="a02c4b7c-dcc3-bf64-6563-55085baa31ee" data-mce-hlselector="#ReplyInline_0b752e6a469147a98d4c7f51937eaf09-tinyMce">

This I coded in CLP - was trivial and straightforward - I don't understand how can I set connection to each of the participating database in single UOW in .net -

Fragment of my CLP proof of concept:

------------------------------
Jan Nelken

Original Message:
Sent: Tue January 30, 2024 09:27 AM
From: Bryan Hamby
Subject: 2 Phase Commint from .Net code?

With .NET, you use the TransactionScope class to handle 2-phase commit.

.NET uses Microsoft Distributed Transaction Coordinator (MSDTC) to coordinate the transactions across databases. DB2 uses XA transactions for distributed transactions, and there is some configuration you have to do on the machine running the code for MSDTC. Search "Component Services" to load the Control Panel, then go Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC. Right-click on it and hit properties, then go to the Security tab and check "Enable XA Transactions".

Some of the gotchas:

• The connection must be opened inside of the TransactionScope, that is how .NET is aware to enroll it in the distributed transaction
• `scope.Complete()`does not commit the code in the block, it simply says that block was successful. The commit happens when the last "outer" scope block is disposed of. If there are child scopes inside that scope, then all scopes must have been marked as complete and disposed for it to commit.
  
  • You should pretty much use a using statement for all TransactionScopes, it just makes the code easier to ensure it's doing the correct thing.

Here's a short example of some code using TransactionScope to bulk-load a table. (I'd post it as a code block, but the community page errors out if I do that...)

-------------------------------------------------------------

public void DoDatabaseWork()
{
    using (var scope = GetTransactionScope())
    using (var conn = GetConnection("dbName"))
    {
        var curTstamp = DateTime.Now;
        var tableName = "DBA.TEST_TABLE";
    
        using (var adapter = new DB2DataAdapter($"SELECT * FROM {tableName}", conn))
        using (var table = new DataTable())
        {
            adapter.FillSchema(table, SchemaType.Source);
            for (int i = 0; i < 1000; i++)
            {
                var row = table.NewRow();
                row["COL1"] = "ABC";
                row["COL2"] = "123";
                row["TS_COL"] = curTstamp;
                row["NULL_COL"] = DBNull.Value;
                table.Rows.Add(row);
            }
            
            using (var bc = new DB2BulkCopy(conn))
            {
                bc.DestinationTableName = tableName;
                bc.WriteToServer(table);
            }
        }

        scope.Complete();
    } // using scope/conn
}

-------------------------------------------------------------

I also recommend having a function that creates the scope and sets some options. The defaults are pretty bad for concurrency. It uses Serializable by default, which is equivalent to DB2 WITH RR (Repeatable Read).

-------------------------------------------------------------

public static TransactionScope GetTransactionScope(
    TransactionScopeOption scopeOption = TransactionScopeOption.Required,
    IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
    return new TransactionScope(scopeOption, new TransactionOptions
    {
        Timeout = TransactionManager.MaximumTimeout,
        IsolationLevel = isolationLevel,
    });
}

-------------------------------------------------------------

TransactionScope is kinda magic (in the good and bad senses), but hopefully this will get you on the right path!

Just a quick question: if I want to have all my multisite updates rolled back - do I call:

scope.Dispose()

and all updates will be rolled back?

------------------------------
Jan Nelken

Original Message:
Sent: Tue January 30, 2024 12:19 PM
From: Bryan Hamby
Subject: 2 Phase Commint from .Net code?

Yes, TransactionScope is the way you do multi-database updates in .NET and have the commit/rollback as a unit (2-phase commit). 

Basically, you just create the TransactionScope block, open a connection to the databases involved (one connection object per database) and perform the work needed, then call scope.Complete() at the end.

.NET coordinates the commits across all participating databases for you.

------------------------------
Bryan Hamby

Original Message:
Sent: Tue January 30, 2024 10:46 AM
From: Jan Nelken
Subject: 2 Phase Commint from .Net code?

Thank you for the answer. 

I am still confused (probably because I believe that .net is for spiders only)...

What I am trying to do is something called multisite update - something like this:

image001.gif@01DA52E3.92FB0870" class="img-responsive" data-mce-hlimagekey="a02c4b7c-dcc3-bf64-6563-55085baa31ee" data-mce-hlselector="#ReplyInline_0b752e6a469147a98d4c7f51937eaf09-tinyMce">

This I coded in CLP - was trivial and straightforward - I don't understand how can I set connection to each of the participating database in single UOW in .net -

Fragment of my CLP proof of concept:

------------------------------
Jan Nelken

Original Message:
Sent: Tue January 30, 2024 09:27 AM
From: Bryan Hamby
Subject: 2 Phase Commint from .Net code?

With .NET, you use the TransactionScope class to handle 2-phase commit.

.NET uses Microsoft Distributed Transaction Coordinator (MSDTC) to coordinate the transactions across databases. DB2 uses XA transactions for distributed transactions, and there is some configuration you have to do on the machine running the code for MSDTC. Search "Component Services" to load the Control Panel, then go Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC. Right-click on it and hit properties, then go to the Security tab and check "Enable XA Transactions".

Some of the gotchas:

• The connection must be opened inside of the TransactionScope, that is how .NET is aware to enroll it in the distributed transaction
• `scope.Complete()`does not commit the code in the block, it simply says that block was successful. The commit happens when the last "outer" scope block is disposed of. If there are child scopes inside that scope, then all scopes must have been marked as complete and disposed for it to commit.
  
  • You should pretty much use a using statement for all TransactionScopes, it just makes the code easier to ensure it's doing the correct thing.

Here's a short example of some code using TransactionScope to bulk-load a table. (I'd post it as a code block, but the community page errors out if I do that...)

-------------------------------------------------------------

public void DoDatabaseWork()
{
    using (var scope = GetTransactionScope())
    using (var conn = GetConnection("dbName"))
    {
        var curTstamp = DateTime.Now;
        var tableName = "DBA.TEST_TABLE";
    
        using (var adapter = new DB2DataAdapter($"SELECT * FROM {tableName}", conn))
        using (var table = new DataTable())
        {
            adapter.FillSchema(table, SchemaType.Source);
            for (int i = 0; i < 1000; i++)
            {
                var row = table.NewRow();
                row["COL1"] = "ABC";
                row["COL2"] = "123";
                row["TS_COL"] = curTstamp;
                row["NULL_COL"] = DBNull.Value;
                table.Rows.Add(row);
            }
            
            using (var bc = new DB2BulkCopy(conn))
            {
                bc.DestinationTableName = tableName;
                bc.WriteToServer(table);
            }
        }

        scope.Complete();
    } // using scope/conn
}

-------------------------------------------------------------

I also recommend having a function that creates the scope and sets some options. The defaults are pretty bad for concurrency. It uses Serializable by default, which is equivalent to DB2 WITH RR (Repeatable Read).

-------------------------------------------------------------

public static TransactionScope GetTransactionScope(
    TransactionScopeOption scopeOption = TransactionScopeOption.Required,
    IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
    return new TransactionScope(scopeOption, new TransactionOptions
    {
        Timeout = TransactionManager.MaximumTimeout,
        IsolationLevel = isolationLevel,
    });
}

-------------------------------------------------------------

TransactionScope is kinda magic (in the good and bad senses), but hopefully this will get you on the right path!