Inserting records into DB2 table from Oracle table


  • We are trying to select all records from an Oracle table and insert them into a DB2 table. Here is what has been accomplished.

    BizTalk Visual Studio Project:

      1. Generated and tested schema for Oracle table (using adapter wizard).

      2. Generated and tested schema for DB2 table (using adapter wizard).

      3. Created map and mapped between Oracle and DB2 schemas the fields needed.

      4. Implemented the orchestration that was generated with the DB2 schema when the "Add Generated Items" for "Add Adapter Metadata" wizard was run for  the DB2 adapter. During this wizard the port type was set to a send port, the statement type was set to Updategram and the updategram was set to Insert for the selected DB2 table and columns to include were checked.

      5. Build Solution was successful.

      6. Deploy solution was successful.

    BizTalk Server Application:

      1. Added a receive port and location for WCF-OracleDB. Set the receive pipeline to PassThruReceive.

      2. Added a send port and set the type to "File" - used for testing - and set the send pipeline to PassThruReceive.

      3. Added a send port and set the type to "DB2". Set the DB2 transport property for Document Target Namespace to http://DB2rcvProject, the DB2 schema setting for targetNamespace.

      4. Set the bindings for the orchestration to the receive port for WCF-OracleDB and the send port for DB2.

    After turning on all tracking and starting the application, the following observation were made:

      1. The file was created for send port - item 2 above - and held the information for the records in the Oracle table.   

      2. Found no suspended items.

      3. Found no errors in windows application log.

      4. checked the DB2 table and found that no records were add.

    Does anyone have a suggestion as to what can be checked further?

    Bob Syren

    Wednesday, August 22, 2012 3:14 PM

All replies

  • I would say that there is a subscription issue : have you double checked that send port with type DB2 is subscribed to message with right filters conditions ?

    With tracking option enabled you could known if message is managed by the send port or not.

    Christophe BRANDO...If this answers your question, please Mark as the Answer. If this post is helpful, please vote as helpful.

    Thursday, August 23, 2012 12:09 PM
  • In step 2 are you using orchestartion or just receiveport-sendport setting

    Thursday, August 23, 2012 12:44 PM
  • Anand_PS: I only configured the send port - option 2 - to test that records were being received from the WCF-OracleDB adapter. They are.

    Bob Syren

    Thursday, August 23, 2012 7:23 PM
  • Hi Bob,

    Might be my understanding about you implementation is wrong but here what i think

    Working scenario: (Context based routing )

    BizTalk Receive Location -> passthough pipeline

    BizTalk Send Port-> Passthough pipeline

    Non Working scenario: (orchestration inbetween)

    BizTalk Receive Location -> passthough pipeline

    Orchestration for processing

    BizTalk Send Port-> Passthough pipeline

    Since you are saying you are not getting any error when you go with second scenrio and you are also not getting any error is quite wierd. What i suggest is stop all send ports and do the testing you will definatley get some suspended instance.

    Second thing you are using passthough pipeline and from this receive location message was subscribed by orchestration (based on message type). But you are using here passthough pipeline so i wonder if orchestration actually receiving any message. Can you test this by making modification in pipeline(xmlreceive)


    • Marked as answer by LeoTangModerator Wednesday, August 29, 2012 8:25 AM
    • Unmarked as answer by bsyren Wednesday, August 29, 2012 11:31 AM
    Friday, August 24, 2012 3:31 AM