BizTalk Adapter for DB2 unable to call DB2 Stored Procedure on AS/400 if Specific Name doesn’t match Procedure Name

If you have created DB2 Stored Procedures on an IBM iSeries (AS/400) that have a SPECIFIC name that differs from the PROCEDURE name, the BizTalk Adapter for DB2 does not correctly enumerate these Stored Procedures in the DB2 Adapter Schema Generation Wizard.

For example, I created the following Stored Procedure on an iSeries V7R1 system:

- CREATE PROCEDURE SP_TEST1LONGSTOREDPROCNAME2 (

                IN I_COL1 VARCHAR(8000) , 
                IN I_COL2 CHAR(160) , 
                OUT O_COL2 CHAR(160) ) 
                DYNAMIC RESULT SETS 1 
                LANGUAGE SQL
                SPECIFIC TEST1NAME2

                BEGIN

                            DECLARE R1 INT;
                            DECLARE C1 CURSOR FOR
                                             SELECT COL2 FROM TESTLIB.SPTEST1 WHERE COL1 = I_COL1 ; 
                            DECLARE C2 CURSOR WITH RETURN FOR 
                                             SELECT * FROM TESTLIB.SPTEST1 ; 
                            DECLARE C3 CURSOR for
                                             SELECT COUNT(*) FROM TESTLIB.SPTEST1;
                            INSERT INTO TESTLIB.SPTEST1 ( COL1 , COL2 ) 
                                             VALUES ( I_COL1 , I_COL2 ) ; 
                            OPEN C1 ; 
                            OPEN C2 ; 
                            OPEN C3;
                            FETCH C1 INTO O_COL2 ; 
                            FETCH C3 INTO R1;
                            RETURN 10;
                  END')

In this example, the Procedure Name is SP_TEST1LONGSTOREDPROCNAME2 and the SPECIFIC name is TEST1NAME2. The Stored Procedure has 3 parameters (2 IN and 1 OUT). If I run the DB2 Adapter Schema Generation Wizard from within Visual Studio while working on my BizTalk Adapter for DB2 Application project, SP_TEST1LONGSTOREDPROCNAME2  is returned as one of the Stored Procedures in my library, but it doesn’t list the parameters.

If I recreate this Stored Procedure, but I make the SPECIFIC name the same as the PROCEDURE name (or if I leave it blank), the DB2 Adapter Schema Generation Wizard shows the Stored Procedure and it lists all of the parameter for the procedure.

Upon further investigation, I found that the query issued by the BizTalk Adapter for DB2 to obtain the metadata (e.g. parameter information) for the Stored Procedure passes the PROCEDURE name as the SPECIFIC name when connecting to DB2/400 (iSeries) systems. The metadata query is shown here for this test scenario:

SELECT '' AS PROCEDURE_CAT, RTRIM(SPECIFIC_SCHEMA) AS PROCEDURE_SCHEM, RTRIM(SPECIFIC_NAME) AS PROCEDURE_NAME, RTRIM(PARAMETER_NAME) AS COLUMN_NAME, ORDINAL_POSITION AS ORDINAL_POSITION, RTRIM(PARAMETER_MODE) AS COLUMN_TYPE, RTRIM(DATA_TYPE) AS TYPE_NAME, CHARACTER_MAXIMUM_LENGTH AS COLUMN_SIZE, NUMERIC_PRECISION AS PRECISION, NUMERIC_SCALE AS DECIMAL_DIGITS, RTRIM(IS_NULLABLE) AS NULLABLE, CCSID AS CCSID, RTRIM(LONG_COMMENT) AS REMARKS FROM QSYS2.SYSPARMS WHERE SPECIFIC_SCHEMA = 'TESTLIB' AND SPECIFIC_NAME = 'SP_TEST1LONGSTOREDPROCNAME' ORDER BY PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, ORDINAL_POSITION FOR FETCH ONLY

In this case, DB2 does not have a Stored Procedure with a SPECIFIC name = SP_TEST1LONGSTOREDPROCNAME so it is not located and no metadata is returned to the DB2 Adapter Schema Generation Wizard.

If the DB2 Adapter is unable to obtain the correct metadata for the Stored Procedure, you will encounter errors if the BizTalk Server application uses the DB2 Adapter to call the Stored Procedure.

This problem was just recently reported by a customer on the HIS Forum, so it appears that having Stored Procedures on IBM iSeries (AS/400) systems with different PROCEDURE and SPECIFIC names is not very common. However, it is something to keep in mind when using the BizTalk Adapter for DB2 to call Stored Procedures on iSeries systems.