none
Two-Phase Commit between DB2 (on AS400) and SQL Server (on Windows Server) using BizTalk Server Adapters RRS feed

  • Question

  • I want to achieve a two phase commit between a DB2 database (hosted on a AS400 system) and a SQL Server (hosted on a Windows server) using BizTalk Server Adapters. Is this possible ? If yes, is there some article/walkthrough that can explain how it is done  ?
    Monday, March 30, 2015 9:31 AM

Answers

  • Hi Ioakim

    Performing an atomic transaction(ACID) that spans a source system, BizTalk and then a different destination system is not possible. This is due to the nature of distributed transactions - it may not be practical to hold locks on resources(needed for atomic transactions) that are spread across different networks. Given that there is no guarantee that the resources may be released in a reasonable amount of time, transactions in BizTalk can only be scoped to and from the BizTalk MessageBoxDb. Or in other words, you can read a record to BizTalk transactionally and on the send side, send a message from BizTalk transactionally.


    However, you can simulate/fake atomic transaction characteristics(although tricky) using compensation logic within BizTalk orchestrations. So, lets say you read+delete a record from SystemA into BizTalk, and that record has to be updated in SystemB. If for some reason the insert to SystemB fails, you would have to write custom logic in a compensation block that then performs the reverse operation in SystemA - i.e., it inserts back the record in SystemA in this case. So, using compensating actions, it is possible to ensure that all the systems involved are transactionally consistent at the end of the day.

    There's an article from C.Young that discusses this in some detail-

    http://geekswithblogs.net/cyoung/archive/2006/12/06/100424.aspx

    Thanks

    Arindam

    • Proposed as answer by Angie Xu Monday, April 6, 2015 6:28 AM
    • Marked as answer by Angie Xu Wednesday, April 8, 2015 1:59 AM
    Friday, April 3, 2015 1:25 PM
    Moderator

All replies

  • According to my experience, I'm afraid this is not feasible.
    Thursday, April 2, 2015 2:31 AM
  • Hi Ioakim

    Does the scenario involve reading/deleting a record from DB2 and then inserting/updating that in SQL Server?

    In this case, you have to break this up as two different transactions/two-phase commits. One between DB2 and BizTalk MessageBoxDB to read the data. And the second transaction on the BizTalk Send Port between BizTalk MessageBoxDB and your custom SQL DB.

    Regards

    Arindam

    Thursday, April 2, 2015 7:59 PM
    Moderator
  • What I want to achieve is :

    a) update/insert/delete in DB2 database

    b) update/insert/delete in SQL database

    both actions implemented on the same transaction, and if something goes wrong on either end, to rollback both actions (that's the whole meaning of two phase commit). If everything goes fine, then commit on both ends. If the DB2 database were located on a windows environment, I wouldn't be posting anything anywhere ...

    It seems that no one knows (on any forum here in MSDN) how to implement 2PC in my situation.

    Someone (from another forum) suggested that my problem may be solved with BizTalk ... and that is the reason why I posted my question here.

    Friday, April 3, 2015 7:21 AM
  • Hi Ioakim

    Performing an atomic transaction(ACID) that spans a source system, BizTalk and then a different destination system is not possible. This is due to the nature of distributed transactions - it may not be practical to hold locks on resources(needed for atomic transactions) that are spread across different networks. Given that there is no guarantee that the resources may be released in a reasonable amount of time, transactions in BizTalk can only be scoped to and from the BizTalk MessageBoxDb. Or in other words, you can read a record to BizTalk transactionally and on the send side, send a message from BizTalk transactionally.


    However, you can simulate/fake atomic transaction characteristics(although tricky) using compensation logic within BizTalk orchestrations. So, lets say you read+delete a record from SystemA into BizTalk, and that record has to be updated in SystemB. If for some reason the insert to SystemB fails, you would have to write custom logic in a compensation block that then performs the reverse operation in SystemA - i.e., it inserts back the record in SystemA in this case. So, using compensating actions, it is possible to ensure that all the systems involved are transactionally consistent at the end of the day.

    There's an article from C.Young that discusses this in some detail-

    http://geekswithblogs.net/cyoung/archive/2006/12/06/100424.aspx

    Thanks

    Arindam

    • Proposed as answer by Angie Xu Monday, April 6, 2015 6:28 AM
    • Marked as answer by Angie Xu Wednesday, April 8, 2015 1:59 AM
    Friday, April 3, 2015 1:25 PM
    Moderator