none
Issue getting Stored Procedure List in Biztalk 2010/HIS 2010 using DB2 Adapter connecting to AS400 V7R1 RRS feed

  • Question

  • I have a Biztalk 2010 EDI Application where I have to connect to DB2/AS400 V7R1 using DB2 Adapter. It does connect, but fails to load Stored Procedure List. I get a wierd error "System.Exception" with no message description. If I do Table list, it brings all the tables.

    Connection is through HIS 2010.

    Please help.

    Thanks

    Venky

    Friday, March 15, 2013 4:53 PM

Answers

  • First, The data providers in HIS 2010 (including the BizTalk Adapter for DB2) are supported with DB2/400 V7R1 with the latest HIS 2010 CU applied. If you were getting the error and didn't have HIS 2010 CU6 applied, that might explain the error. HIS 2010 CU6 can be downloaded via a link in the following KB Article:

    2752426 Cumulative update package 6 for Host Integration Server 2010
    http://support.microsoft.com/kb/2752426/EN-US

    As for the issue with the HIS 2013 Beta, I'm not sure. Can you provide more details on the specific scenario? Are they any errors returned (also check Application Event log)?, what is the format of the SP call?

    With just this information, I don't know if anything is being sent to the AS/400 and what (if anything) is being returned by the AS/400. This makes it difficult to know where or what the problem is. I couldn't find any known issues with the HIS 2013 Beta that matches description.

    Thanks...


    Stephen Jackson - MSFT

    • Marked as answer by Venky Pai68 Wednesday, March 27, 2013 2:08 AM
    Monday, March 25, 2013 3:11 PM

All replies

  • Venky,

    Does the error comes up when selecting Stored procedures in the DB2 Adapter Schema Generation Wizard in Visual Studio?

    If not, what steps lead you to the error?

    Also, what is the format and lengths of the Stored Procedure Names that are defined in the library that the DB2 connection string is connecting to on the AS/400?

    Finally, you might want to apply the latest HIS 2010 Cumulative Update, just to make sure you have the latest HIS 2010 updates applied. You can download HIS 2010 CU6 from the following KB Article:

    2752426 Cumulative update package 6 for Host Integration Server 2010
    http://support.microsoft.com/kb/2752426/EN-US

    Thanks...


    Stephen Jackson - MSFT

    Thursday, March 21, 2013 5:43 PM
  • Thank You Stephen.

    Yes I was getting it when selecting Stored Procedures in DB2 Adapter Schema Generation Wizard in VS 2010. I realized that this was possibly because AS400 ws V7R1 because this was fine for AS400 V6R1. So, I upgraded to Biztalk 2013 - Beta, HIS 2013 - Beta, VS 2012 and I'm now able to get the Stored Procedure List, but I have another issue. I'm not able to get the DB2 Stored Procedure to return XML Output to generate XSD with Result Set Column. Not sure, if this is AS400 DB2 issue or Biztalk/HIS issue. Any ideas on this?

    Thank You for your help on this

    Venky

    Monday, March 25, 2013 12:00 PM
  • First, The data providers in HIS 2010 (including the BizTalk Adapter for DB2) are supported with DB2/400 V7R1 with the latest HIS 2010 CU applied. If you were getting the error and didn't have HIS 2010 CU6 applied, that might explain the error. HIS 2010 CU6 can be downloaded via a link in the following KB Article:

    2752426 Cumulative update package 6 for Host Integration Server 2010
    http://support.microsoft.com/kb/2752426/EN-US

    As for the issue with the HIS 2013 Beta, I'm not sure. Can you provide more details on the specific scenario? Are they any errors returned (also check Application Event log)?, what is the format of the SP call?

    With just this information, I don't know if anything is being sent to the AS/400 and what (if anything) is being returned by the AS/400. This makes it difficult to know where or what the problem is. I couldn't find any known issues with the HIS 2013 Beta that matches description.

    Thanks...


    Stephen Jackson - MSFT

    • Marked as answer by Venky Pai68 Wednesday, March 27, 2013 2:08 AM
    Monday, March 25, 2013 3:11 PM
  • Thank You Again Stephen. I'll try out HIS 2010 Cumulative Update. Now that I have installed Biztalk 2013 Beta and the RTM version is available, I wouldn't mind sticking with Biztalk 2013. I do believe it's going to be a while before HIS 2013 RTM would be available. In the meantime, do you expect any issues using Biztalk 2013 RTM with HIS 2010 with the Cumulative Update Package 6. We are still going to connect to AS400 V7R1.

    Regarding Stored Procedure return value from DB2, I have a stored procedure that returns an Output Parameter. Typically, if this was from SQL Server, I would have received an output like below -

    <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
    <ElementType name="dbo.ABCTBL" content="eltOnly" model="closed" order="many">
    <element type="ABCINFO"/>
    </ElementType>
    <ElementType name="ABCINFO" content="textOnly" model="closed" dt:type="string"/>
    </Schema>
    <dbo.ABCTBL xmlns="x-schema:#Schema1">
    <ABCINFO>NO DATA</ABCINFO>
    </dbo.ABCTBL>

    But, when I call the same or similar Stored Procedure from DB2 that returns a Result Set, I get the below as response.

    Success, RowsAffected & ResultSets Tags

    Wednesday, March 27, 2013 2:19 AM
  • It is a bit dated, but have you read through the following white paper?

    http://www.microsoft.com/en-us/download/details.aspx?id=20439

    It includes examples for calling Stored Procedures using the DB2 Adapter. I suggest this just so you can compare the configuration with what you have done to see if that might explain what you are seeing.

    Thanks...


    Stephen Jackson - MSFT

    Wednesday, March 27, 2013 8:24 PM
  • Thanks Stephen. This document came in handy in resolving this issue.
    Saturday, March 30, 2013 2:54 PM