none
Polling data from multiple tables supported in HIS 2010? RRS feed

Answers

  • Modifying my answer - and my apologies.

    When polling data, no, we can only return 1 result set. Again, my apologies. If you were not doing polling, multiple result sets should work.


    Charles Ezzell - MSFT
    • Marked as answer by Sargantana Thursday, September 30, 2010 2:54 PM
    Wednesday, September 29, 2010 4:14 PM

All replies

  • From the recently posted HIS 2010 Documentation:

    http://msdn.microsoft.com/en-us/library/gg167635(v=BTS.70).aspx#DatIntegration

    BizTalk Adapter for DB2 Multiple Table Update

    To integration line-of-business systems across the enterprise, IT professionals deploy BizTalk Server, which offers efficient XML document interchange and flexible business process orchestration. Previously, when using the BizTalk Adapter for DB2 to update (INSERT, UPDATE, DELETE, or CALL) rows across multiple DB2 tables, developers had to use separate XML documents, which required use of additional pipeline processing to disassemble XML documents before submitting them to the adapter send port. In HIS 2010, developers can use a single XML document instance to reference multiple DB2 tables, improving scalability and performance, while allowing for greater flexibility in specifying batches and transactions scope.

    Thanks...

     


    Stephen Jackson - MSFT
    Wednesday, September 29, 2010 3:22 AM
  • Stephen,

    Yes I understand that it is now possible to update rows in multiple tables in DB2, my question was whether we can now poll data from multiple DB2 tables and send to BizTalk as a single message.

    An example scenario is polling for new sales orders, where the information comes from a table with the sales order header and another table containing many detail lines, the question is whether we can make the new HIS2010 DB2 adapter poll and retrieve the sales order information (header+details) as a single message.

    In HIS2009 we could use poll calls to a stored procedure, however we could return only one result set to the adapter.

    Thanks.

     

    Wednesday, September 29, 2010 7:08 AM
  • No, we can not issue 2 queries and do a join on them to present 1 message back. that you should be able to do in the query itself, or better yet in s stored procedure or view.

    As for returning multiple result sets, I've not tried this. In theory it should work, but possibly does not due to how things are handled with cursors. I say in theory, because I know in SQL I could call a stored procedure in DB2 and get back 2 result sets, and even using the managed data provider do the same. I assume your stored procedure is created with "DYNAMIC RESULT SETS 2" similar to this:

    CREATE PROCEDURE CNWIND.SOMEPROCEDURE()
    DYNAMIC RESULT SETS 2
       BEGIN 
         DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM CNWIND . SALES ; 
         DECLARE C2 CURSOR WITH RETURN FOR SELECT PRODID , PRODNAME FROM CNWIND . PRODUCTS WHERE OLDSTOCK = 0 ;
         OPEN C1 ; 
         OPEN C2 ;
       END ;
    
    

    Charles Ezzell - MSFT
    Wednesday, September 29, 2010 4:11 PM
  • Modifying my answer - and my apologies.

    When polling data, no, we can only return 1 result set. Again, my apologies. If you were not doing polling, multiple result sets should work.


    Charles Ezzell - MSFT
    • Marked as answer by Sargantana Thursday, September 30, 2010 2:54 PM
    Wednesday, September 29, 2010 4:14 PM
  • Thank you Charles, we will look for other ways to poll data from many tables.
    Thursday, September 30, 2010 2:54 PM