We are trying to create a native SP that calls another SP and we are trying pass the called SP name dynamically with a host variable, but the compiler at the deploy time is giving the following warning; it is givina a warning that SP is not available with the host variable.
Create stored procedure returns SQLCode = 440 - NO PROCEDURE BY THE NAME V_SP_NAME HAVING COMPATIBLE ARGUMENTS WAS FOUND.
SQLCODE=440, SQLSTATE=0168I, DRIVER=3.63.108.IPXWTBT.IPXSQMN2 - Create stored procedure completed.IPXWTBT.IPXSQMN2 - Deploy for debug completed with warnings.
Run time of the SP is also giving a similar error:
NO AUTHORIZED PROCEDURE NAMED V_SP_NAME HAVING COMPATIBLE ARGUMENTS WAS FOUND
As per DB2 call statement syntax, it should allow the host variable but it is not working in native SP and it is working in COBOL SP or if we hard code the SP name instead of host variable.
Could somebody advise whether I need to add any specific complier or run time option with Create Procedure statement?
Create statement of the SP is like below:
CREATE PROCEDURE IPXSQMN2 ( IN ISP_NAME CHAR(254) ,IN IML_AC_NO CHAR(8) ,OUT ORETURN_CODE INTEGER ,OUT ORETURN_MESSAGE CHAR(100) ,OUT OSQLCODE INTEGER ,OUT OSQLSTATE CHAR(5) ,OUT OSQL_MESSAGE VARCHAR(32704) ) VERSION V1 QUALIFIER IPXWTBT PACKAGE OWNER IPXWTBT ISOLATION LEVEL CS VALIDATE RUN RESULT SETS 9 LANGUAGE SQL MODIFIES SQL DATA WITH EXPLAIN
DECLARE V_SP_NAME CHAR(254);
SET V_SP_NAME = 'IPXSQCL1'
CALL V_SP_NAME(IML_AC_NO, ORETURN_CODE,ORETURN_MESSAGE,OSQLCODE,OSQLSTATE,OSQL_MESSAGE);
I would appreciate if any help on this problem about calling the SP using host variable name so that we can pass the SP name dynamically: