Topic
  • No replies
MurtyTammineedi
MurtyTammineedi
1 Post

Pinned topic DB2 Z/OS V10 NFM - Native SQL SP CALL statement not accepting host variable as SP Name

‏2013-05-28T02:32:15Z | db2 native nfm question sp sql v10 z/os

 

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:

SQLCODE: -440

SQLSTATE: 42884

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?

>>-CALL--+-
procedure-name-+------------------------------------->
         '-
host-variable--'  

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: