none
BizTalk 2010 DB2 Adapter Receive for XML RRS feed

  • Question

  • Hi<o:p></o:p>

    I am using BizTalk (2010) DB2 adapter to poll the records from a table (DB2 database). I have created a Receive Location and have configured it properly. Upon enabling the receive location, the records are fetched by the DB2 adapter. However, I am receiving proper data for all of the table columns but one and that column has data type = XML. Instead of receiving an XML as text or XML I get some alpha numeric value as 3E4R56TY.<o:p></o:p>

    To put an example, we have a table in DB2 database defined as<o:p></o:p>

    Col1 (int), Col2 (XML), Col3 (varchar)<o:p></o:p>

    BizTalk DB2 receive port (say name = recvPortDB2) and receive location is configured and the SQL command property is Select Col1, Col2, Col3 from Table1. I also have a send port (transport type = file) who has a subscription to the messages received on the above mentioned receive port (recvPortDB2). Done this to actually see the message written on some file location. Once receive location is enabled, message is copied to the desired file location. While the values for fields col1 and col3 are OK, however the values for col2 which should have been an XML is actually something garbish.<o:p></o:p>

    I have tried by changing the data type for col2 from XML to CLOB. Still the result is same. <o:p></o:p>

    I have also tried to cast the CLOB as varchar in the select statement. In that case, the value for Col2 is returned as encoded XML. I am trying to avoid this solution because of size limitations on varchar. <o:p></o:p>

    I will appreciate any suggestions or help?<o:p></o:p>

    Thanks

    Wednesday, June 12, 2013 9:03 PM

Answers

  • The DB2 Providers included with HIS 2010 (including the BizTalk Adapter for DB2) do not support the DB2 XML data type (which looks like DB2 BLOB). The upcoming release of HIS 2013 does include updated to supporting reading and writing XML data types for DB2 for z/OS V10.

    HIS 2013 is scheduled to be release in Q3 of 2013.

    Thanks...


    Stephen Jackson - MSFT

    Tuesday, June 25, 2013 8:09 PM

All replies

  • The DB2 Providers included with HIS 2010 (including the BizTalk Adapter for DB2) do not support the DB2 XML data type (which looks like DB2 BLOB). The upcoming release of HIS 2013 does include updated to supporting reading and writing XML data types for DB2 for z/OS V10.

    HIS 2013 is scheduled to be release in Q3 of 2013.

    Thanks...


    Stephen Jackson - MSFT

    Tuesday, June 25, 2013 8:09 PM
  • Hi Stephen,

    One more question on the BizTalk Adapter for DB2, Is there  a way we can execute SELECT query with where condition variables dynamically assign to the statement..

    I observed that none of the examples are passing parameter to the SELECT query.  For some reason, my client doesn't want to use stored procedures.

    My requirement is, search conditions are passed into the orchestration  in the receive location and orchestratration in turn executes the query by passing search parameter and expecting a resultset of multiple records..

    Is this possible by using Biztalk DB2 adapter (without using stored procedure)?  I assume,  worst case this requirement will work with stored procedure.

    Please clarify.

    Thanks and regards


    Thanks Vinod

    Thursday, August 1, 2013 1:09 AM
  • Vinod,

    The DB2 Adapter can be used to run SELECT queries against DB2 databases. It can also be used to do INSERTs, DELETEs, UPDATEs, and for calling Stored Procedures.

    For example, the following is a simple XML file that I drop into a folder that gets picked up by the DB2 Adapter to issue a SELECT statement with a WHERE clause:

    <ns0:DB2Test1Request xmlns:ns0="DB2Test1">
      <sync>
        <Select>SELECT * FROM SPTEST1 WHERE COL2 = '300'</Select>
      </sync>
    </ns0:DB2Test1Request>

    As you can see the query is a SELECT state from a table named SPTEST1 where I want to get all rows that have COL2 = 300.

    The following documents are dated as they were written for the DB2 Adapter included in HIS 2006, but the information still applies in the current versions. They may be of use to you.

    Setting Up the BizTalk Adapter for DB2 Using Send Ports

    Setting Up the BizTalk Adapter for DB2 Using Receive Ports

    Thanks...


    Stephen Jackson - MSFT

    Thursday, August 1, 2013 1:59 PM
  • Thanks Stephen


    Thanks Vinod

    Thursday, August 1, 2013 7:48 PM