none
DB2 adapter and transaction spanning multiple table RRS feed

  • Question

  • Guys,

    I have this  requirement ... I need to insert into 6 different tables in Db2 all residing in the same DB2 database.

    I have a BizTalk orchestration which reads the message from the MsgBox and inserts it into this six tables .All the inserts should be part of one transaction and either succed or fail .

    I am exploring various design options :

    Design 1 :

    In my orchestration I have a long running transaction and below that I have six atomic transactions which insert into six tables

    DB2 adapter  doesnot  support multiple table inserts ..By design we can only insert/update/delete one table at a time
       
            1. I need to to have   six   insert shceamas and maps
            2. I need to have six compensation blocks ,schemas and maps   (to   delete  )


           
    Design 2 :

          From the Orchestratio call a custom  .net component  and pass in the seiralized input message
          Custom .net component will use the Managed provider for Db2 and use the transaction features           
          You will have to write the insert statement and manage connections





    I am kind of inclined to go with Design 2 ....What you guys recommend ?



    Wednesday, December 2, 2009 4:59 PM

Answers

  • In such a scenaio, I am more inclined to Design 2.
    This posting is provided "AS IS" with no warranties, and confers no rights. Microsoft Online Community Support
    Tuesday, December 8, 2009 8:49 AM
    Moderator

All replies

  • In such a scenaio, I am more inclined to Design 2.
    This posting is provided "AS IS" with no warranties, and confers no rights. Microsoft Online Community Support
    Tuesday, December 8, 2009 8:49 AM
    Moderator
  • Thanks Andrew..  just one question about the  Managed provider for Db2 that comes with the  BizTalk Adapters for host system..

    Since  all the tables are in the same database  I should be able to wrap it in a transaction like a standard .NET transaction and commit or roll back together  using the managed provider for Db2 ? Or do I need to implement this a as serviced component ?  ( From the documentation what I under stood was only when you have distributed transaction spanning multiple databases u need to  implement this as a serviced component to take  part in a 2 phase commit )

    Tuesday, December 8, 2009 7:54 PM
  • Hi,

    I would rather implement it as a sevice component. Esp. BizTalk is born for distributed applications.
    Implement it as a service so that we can scale out, change component easily.
    for example, maybe some day,you change the database to SQL Server from DB2. if the component implemented as a service.
    the migration will be easier.

    Regards
    This posting is provided "AS IS" with no warranties, and confers no rights. Microsoft Online Community Support
    Thursday, December 10, 2009 6:47 AM
    Moderator

  • I understand this part, Thanks for your explanation!
    Thursday, January 13, 2011 6:23 AM