0

it's possible to define an alias for a stored procedure (sql-native)? In the documentation, reference is made only to: SEQUENCE and TABLES

1) CREATE PROCEDURE OWXXCOLL.STORED1()
2) CREATE ALIAS DB2C.STORED1 FOR OWXXCOLL.STORED1;
3) CALL DB2C.STORED1();

EDIT 2021-05-14

The original question arises for the following problem (I was hoping to get away with using aliases)

Intro

  1. I have defined a native SP The OWXXCOLL schema is the same one I also use for tables/index... (I noticed that the tables also have different aliases)
    CREATE PROCEDURE OWXXCOLL.STORED1(...)
        LANGUAGE SQL
        ISOLATION LEVEL CS
        WLM ENVIRONMENT FOR DEBUG MODE WLMENV1
        ALLOW DEBUG MODE
    BEGIN 
       ...
    END@
  1. I also modified the cobol program (name:PGMSTO1) to call the Stored with the CALL statement (without qualifier)
    EXEC SQL                                        
        CALL STORED1 (...)             
    END-EXEC.

The problem

The various table accesses (SELECT) work correctly BUT When I run the PGMSTO1 the call to the Stored ends with sqlcode -440

NO AUTHORIZED PROCEDURE BY THE NAME STORED1 HAVING COMPATIBLE ARGUMENTS WAS FOUND<

The error comes from the fact that it is not using the owner OWXXCOLL but DB2C (DB2C is user who scheduled the jcl/batch)

If I enter the qualifier (OWXXCOLL) the call it's OK.

I don't understand what to check and what configurations are missing.

Thanks

killer
  • 11
  • 6
  • No, it's not. But why would you need it, when the code or config can dynamically adjust CURRENT PATH to address the relevant schema for unqualified procedure or function names? – mao May 13 '21 at 14:31
  • I still have to understand the problem because I have no access in the customer environment. It seems that the cobol program is executed with the DB2C user and the tables and Stored Procedure are defined on the OWXXCOLL schema. For tables everything works correctly because I have an alias (DB2C.TABLE1 --> OWXXCOLL.TABLE1). The problem occurs when the stored procedure is called. I need time to clearly think about it :) – killer May 13 '21 at 15:47
  • you've not stated the programming language in which the procedure is written. Check the bind parameters, and know whether the routine is invoked unqualified. – mao May 13 '21 at 15:51
  • Programming language: SQL - native – killer May 13 '21 at 16:22

1 Answers1

0

The cobol program (PGMSTO1) that calls the stored procedure has the following BIND parameters:

COLLID     NAME    OWNER CREATOR QUALIFIER DYNAMICRULES    PATHSCHEMAS
OWXXCOLL   PGMSTO1 DB2C  DB2C    FPXX       B               "DB2C"

"DB2C" path is used to resolve unqualified stored procedure

I will need to modify the bind parameter "PATH". I will try to ask to add my schema as well (OWXXCOLL)

PATH("OWXXCOLL","DB2C")
killer
  • 11
  • 6