none
Send Port Error while making DB2 Stored Procedure Call RRS feed

  • Question

  • My UAT Environment Details - Windows 2012 64-bit Server, SQL Server 2008 R2 SP1, BizTalk 2013, HIS 2013 Beta, AS400

    I have an EDI Application to process EDI-214 files and saving into DB2 via HIS 2013 Beta. This worked fine in Development Server (Only difference is Windows 2008 R2 SP1 Server), but fails in UAT Server with the following message.

    A message sent to adapter "BiztalkMessagingEngine" on send port "XXXRRPORT" with URI "DB2://XX.XXX.XXX.XXX:446/XXXX/XXXX" is suspended.
     Error details: Unknown Error Description 
     MessageId:  {2D5A04F3-A2BD-4800-82AF-974A35D1FF29}
     InstanceID: {80EDA730-F519-4753-9FDB-5657E082875A}

    I used MSI to install from Development to UAT Server.. Please help.

    Monday, April 22, 2013 2:30 AM

Answers

  • It appears that when the HSI DB2 Providers (including the DB2 Adapter) are used to call Stored Procedures, the following occurs:

    - A query is issued to obtain the list of Stored Procedures in the specific Schema.

    - After that list is obtained, subsequent queries are issued for each Stored Procedure to obtain the list of parameters for the SP.

    When doing this against DB2/400, the second query includes SPECIFIC_NAME=xxxxxx where the xxxxxx is the procedure name returned in the initial query.

    The SPECIFIC Name parameter is not used in these same metadata queries for Stored Procedures when the DB2 Provider is connecting to other DB2 Platforms (z/OS, Linux, Windows, etc.)

    The problem arises when the SPECIFIC name is not the same as the Procedure Name. Since no one else has reported this behavior, it appears that most DB2/400 customers likely used the default setting where the SPECIFIC name is the same as the Procedure Name.

    If you need to see about having this behavior changed, you would need to open a support case to pursue this. I can log a bug to see about having this looked at and possibly changed but without customer backing, it would take longer for this to be looked into and considered.

    I can't promise that the behavior would be changed as there may be valid reasons why it works this way for DB2/400 systems.

    Per the IBM docs that describe the SPECIFIC parameter, it appears that this is something to use when you have multiple Stored Procedures with the same name and schema. If each SP has a different name and/or schema, there really would be no need to add a different SPECIFIC name. This is likely why it may not be a common configuration.

    Thanks...


    Stephen Jackson - MSFT

    Friday, April 26, 2013 3:57 PM

All replies

  • Not really much to go on with just that error message. You might want to enable the DB2 Network Library traces using histrace.exe to capture the error. This might help to see if the error is happening because DB2 is sending back an error or if the problem occurs before anything is sent to DB2.

    You might also try re-creating the application (since you ran the MSI to install it on the UAT server) to see if it is something related to installing the application from the MSI.

    Thanks...


    Stephen Jackson - MSFT

    Monday, April 22, 2013 7:19 PM
  • Thanks Stephen for the reply.

    Upon further review, I found out that it was not able to recognize the Stored Procedure Name, if the Specific Name of the Stored Procedure is not the same as the long name. There are no special characters in the Stored Procedure Name, just the long name was more than 10 characters and so the Create Stored Procedure Script had a different specific name, less than 10 characters. While doing the mapping, the long name of the Stored Procedure is used. So, it looks like this mapping call goes against the Specific Name of the Stored Procedure.

    Is it possible to go against the long name?

    Monday, April 22, 2013 7:46 PM
  • I haven't tried with HIS 2013, but I have tested long SP names with HIS 2010 and the DB2 adapter against DB2/400 V6R1.

    What version of DB2 are you going against on your AS/400?

    Can you include a sample of a Create SP script that leads to the problem?


    Stephen Jackson - MSFT

    Tuesday, April 23, 2013 1:31 PM
  • Stephen,

    DB2/400 is V7R1. Please see sample script below -

    CREATE PROCEDURE HW_PGM.BOLNUMBERCHECK (
     IN LVC_BOL_NUMBER VARCHAR(30) ,
     OUT LVC_BOLINFO VARCHAR(50) )
     LANGUAGE SQL
     SPECIFIC HW_PGM.BOLNUMCHK
     NOT DETERMINISTIC
     MODIFIES SQL DATA

    If I change the SPECIFIC to  have same name as SQL Name (Long Name), then it works, but I can't do this because they are existing Stored Procedures in the system and changing all of them would be difficult.

    Thanks

    Venky

    Tuesday, April 23, 2013 4:00 PM
  • The problem isn't the long name, it is the fact that the specific name and the procedure name values are different.

    Correct?


    Stephen Jackson - MSFT

    Tuesday, April 23, 2013 4:55 PM
  • Sorry for any confusion. Yes. That's correct.

    The problem is the Specific Name and Procedure Names are different.

    Tuesday, April 23, 2013 5:37 PM
  • I may have reproduced the problem (or a similar one) using the DB2 Adapter for HIS 2010. I created a Stored Procedure with 3 parameters (2 In and 1 Out) with a long name and no SPECIFIC clause in the CREATE command. When I run through the DB2 Adapter Schema Generation Wizard in Visual Studio, the SP is returned and all of the parameters are listed.

    When you run through the DB2 Adapter Schema Generation Wizard, the SP is returned, but it doesn’t list the parameters for the SP.

    If I create another SP in the same way, but I add the SPECIFIC value so that it is different than the Procedure name, the DB2 Adapter Schema Generation Wizard does not list the 3 parameters.

    If the SPECIFIC clause specifies the same name as the Procedure Name, all of the parameters are displayed.

    If I try to call the SP with the different SPECIFIC name and pass it parameters that the SP does support, I get an error from DB2 indicating that SP was not found with the specified parameters.

    In looking at the network traces, the DB2 Adapter attempts to get the schema for each of the SPs that are defined. In the case of the SP with the different SPEICFIC name, DB2 is not returning the parameter information.

    Still looking into it.


    Stephen Jackson - MSFT

    Tuesday, April 23, 2013 7:00 PM
  • Stephen,

    Exactly. This is what is happening when the Specific Name is different from SQL Name (Long Name) for the Stored Procedure.

    Thank You for following up on this. Please let me know what you find on this.

    Venky

    Wednesday, April 24, 2013 2:55 AM
  • It appears that when the HSI DB2 Providers (including the DB2 Adapter) are used to call Stored Procedures, the following occurs:

    - A query is issued to obtain the list of Stored Procedures in the specific Schema.

    - After that list is obtained, subsequent queries are issued for each Stored Procedure to obtain the list of parameters for the SP.

    When doing this against DB2/400, the second query includes SPECIFIC_NAME=xxxxxx where the xxxxxx is the procedure name returned in the initial query.

    The SPECIFIC Name parameter is not used in these same metadata queries for Stored Procedures when the DB2 Provider is connecting to other DB2 Platforms (z/OS, Linux, Windows, etc.)

    The problem arises when the SPECIFIC name is not the same as the Procedure Name. Since no one else has reported this behavior, it appears that most DB2/400 customers likely used the default setting where the SPECIFIC name is the same as the Procedure Name.

    If you need to see about having this behavior changed, you would need to open a support case to pursue this. I can log a bug to see about having this looked at and possibly changed but without customer backing, it would take longer for this to be looked into and considered.

    I can't promise that the behavior would be changed as there may be valid reasons why it works this way for DB2/400 systems.

    Per the IBM docs that describe the SPECIFIC parameter, it appears that this is something to use when you have multiple Stored Procedures with the same name and schema. If each SP has a different name and/or schema, there really would be no need to add a different SPECIFIC name. This is likely why it may not be a common configuration.

    Thanks...


    Stephen Jackson - MSFT

    Friday, April 26, 2013 3:57 PM
  • Thanks Stephen. We'll try to re-create the Stored Procedures with the same Specific Names as the SQL Name to resolve this.

    I think using Specific Name makes sense, since you could have the same SQL Name Procedure, overloaded, with different Parameters and different Specific name.

    Just that, I wished the first query that brings the list would have used the Specific Name as well, then this problem would not have occur.

    How can I open a support case for this issue? Please let me know and I can do that.

    Thanks for your time on investigating this.

    Venky

    Saturday, April 27, 2013 8:54 PM
  • Venky,

    You can access support options from http://support.microsoft.com. From there, click Get Live Help. From there, click More products and then type Host Integration Server and then click Find. Select the HIS version and then follow the wizard.

    The direct link to the HIS version list is https://support.microsoft.com/oas/default.aspx?Gprid=782&st=1&wfxredirect=1&sd=gn&wfxredirect=1.

    If your company has a Premier Support contract, you should go through your internal process for opening a case against that contract.

    If not, then you will be prompted to pay for the support case. If this is determine to be a bug or something that we should fix, you would be reimbursed for the support case cost.

    Thanks...


    Stephen Jackson - MSFT

    Monday, April 29, 2013 2:24 PM