Calling a stored procedures within the same schema from a SP

Question

How can I call a stored procedure in the same schema without specifying the full schema name when coding another stored procedure. These are SQL PL procedures within DB2.

First SP:

CREATE PROCEDURE MYSCHEMA.SP_TEST
  LANGUAGE SQL
  BEGIN
  END

Creating a SP calling this SP directly without a schema name causes a compilation error:

CREATE PROCEDURE MYSCHEMA.SP_TEST2
  LANGUAGE SQL
  BEGIN
    CALL SP_TEST();
  END

It will throw:

No authorized routine named "SP_TEST" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.53.71

Directly giving the full schema name works:

CREATE PROCEDURE MYSCHEMA.SP_TEST2
  LANGUAGE SQL
  BEGIN
    CALL MYSCHEMA.SP_TEST();
  END

However if I ever move to a different schema I will have to replace that references all over the place. Is there a suitable workaround or nicer solution to the problem?

Answer 1

The CURRENT PATH special register is used to resolve calls to unqualified stored procedures and functions. CURRENT SCHEMA is used to resolve unqualified object names.

By default, CURRENT PATH has IBM functions plus your AUTHID:

$ db2 "values substr(current path,1,60)"

1
------------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","IBJORHOV"

  1 record(s) selected.

You can modify this with the SET CURRENT PATH statement.

When you create a stored procedure, DB2 takes note of the value of CURRENT PATH at compilation time and uses them to resolve unqualified stored procedure and function calls within the stored procedure. The same logic applies for CURRENT SCHEMA and unqualified table names.

So the proper way to allow unqualified procedure and function calls within a stored procedure is to set the CURRENT PATH register and then creating the procedure.