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
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 ;
FETCH C1 INTO O_COL2 ;
FETCH C3 INTO R1;
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.