none
DB2 Adapter Batch Insert Updategram RRS feed

  • Question

  • Does the DB2 adapter allow a batch insert such as follows?

    <Request>
         <synch>
               <after>FileName Id = '1' Parameter = 'ABC'</after>
               <after>FileName Id = '2' Parameter = 'DEF'</after>
               <after>FileName Id = '3' Parameter = 'GHI'</after>
         </synch>
    </Request>

    • Moved by Ben Cline1Moderator Friday, July 17, 2009 2:53 PM Related to BizTalk adapters (From:BizTalk Server General)
    Thursday, July 16, 2009 8:43 PM

Answers

All replies

  • I'm currently trying to do a similar thing w/ no results

    <Request>
         <synch>
               <after>
                         <RowToInsert Id="1" Name="A" />
                         <RowToInsert Id="2" Name="B" />
               </after>
          </synch>
    </Request>


    The adapter failed to transmit message going to send port "SendPortName" with URL "DB2://Bla/Bla/Bla". It will be retransmitted after the retry interval specified for this Send Port. Details:"Invalid index 0 for this MsDb2ParameterCollection with Count=0.".

    It works just fine when i insert only one row...

    Has anyone had any success with this?  I can't believe there is a limitaion of 1 row per insert!
    Wednesday, July 29, 2009 3:42 PM
  • Hi,

    I spiked this and managed to batch insert.
    Tuesday, August 11, 2009 1:27 PM
  • What do you mean by, "I spiked this and managed to batch insert"?  I am having the same issue as described above.
    Thursday, December 17, 2009 7:00 PM
  • I originally asked the question before testing to see if it works, a "spike". Subsequently, I performed a spike and it works. You just need to follow the datagram rules.
    Wednesday, February 3, 2010 9:55 AM
  • What's solution for "Invalid index 0 for this MsDb2ParameterCollection with Count=0." error?
    Michal Straka
    Friday, October 29, 2010 7:28 AM
  • The problem only occurs when i send messages from an orchestration.

     

    When I copy the message to a File Receive port and set the filter of the DB2 SendPort everything works fine.

     

    Anyone found a solution for this problem?

    Wednesday, November 10, 2010 12:03 PM
  • Squizay:

    Sorry, I have no solution, but wanted to share some info from my tests regarding that file receive and filter DB2 send port succeeds but fails in other scenarios.

    I've really tried to figure things out about the DB2 adapter multi insert problem, which may be another problem.

    I'm currently trying the HIS 2010 version, since it sounded like insert in multiple tables using one updategram should be solved reading this:http://msdn.microsoft.com/en-us/library/gg167635(v=bts.70).aspx

    I used the file receive setup and it worked, but only if I use passthrough receive pipeline. As soon as I use XmlReceive, with my multi table insert updategram deployed, the insert fails, with conversion error. I suspected an adapter conversion bug after MANY hours of investigation. So I made a new test by editing my updategram to contain a dummy table as the first record in the after block. Max number of columns in the real involved tables where 13 so I created 13 dummy columns(attributes) of type string in the dummy record.
    After replacing the deployed schema with this schema and using XmlReceive pipeline the multi-insert "succeeded".

    My conlusion is that if an updategram message is posted to BizTalk and passthrough is used the message is untyped. Hence the adapter have no schema information about the types of the columns. It then does "something" based on that. I don't know what, but maybe it queies the DB to get the types or it does some generic conversion from string and it works.

    If however the message is typed, it seems like the adapter uses the xml schema to do conversion to native types. I think, and it seems verified by my dummy example, that the adapter only uses the first table column types from the xml schema to set up a sql command or something that is used inserting in all the tables specified in the updategram.

    Seems like a BUG to me and not "By design" as stated by MS in other forum threads. If it is by design then an error message should be returned telling that, and it shouldn't work with passthrough either.

    I can not understand that this hasn't been solved yet?!? If it works with untyped message it works at some level so why no make a correct adapter implementation?!?!

    It would be nice to get some response on this matter from MS.

     


    Anders F
    Thursday, January 27, 2011 10:16 AM
  • Hi,

    I have the same error.

    I am trying to do multi-record insert into a single DB2 table.  I am able to insert Request message with single record.  When tried with request message having multiple records I have an error.

    Error Details:"Invalid index 0 for this MsDb2ParameterCollection with Count=0."

    But more interstingly multiple record insert works it works with DB2 static port. ( I used same configuration information to define my dynamic port and do not understand how these differ).

    Having this fourm for Benfit of Community, I find the answer marked to this theard has insuffiant information.

     

    Thank You,

    TenaliNaga.



    Friday, May 13, 2011 10:43 PM
  • I agree.  The comment marked as answer is no answer at all.

    Doug Griffin

    Tuesday, November 12, 2013 11:10 PM
  • Just wanted to relate my experience with this issue.  I had several files I needed to insert records into.  Rather than have a schema for each file that the "Add generated item" wizard creates, I manually created a schema that looked like this:

    <Request>
         <sync>
               <after>
                 <Choice>
                    <FILE01></FILE01>
                    <FILE02></FILE02>
                    <FILE03></FILE03>
                 </Choice>
               </after>
         </sync>
    </Request>

    And clever me! it worked great as long as I inserted one record at a time.  When I had a "batch" of records with one message, that is when I started getting the "MsDb2ParameterCollection" error".

    Lesson Learned:  Use the force Luke !

    Doug Griffin

    Thursday, November 14, 2013 6:34 PM