I am using DB2 12 (Z/OS). I have a cobol Db2 program (name: TESTSS) This program calls an SQL Stored Prodecure (SP_TEST1) The Stored Procedure is defined on the STXXAA scheme.
During BIND I get the sqlcode -440.
BIND command (the job that performs the binding is executed with the DB2C user (Master DB))
DSN S(A4KK)
BIND PACKAGE(CCA4PBT) OWNER(DB2C) QUALIFIER(CA4FX) -
MEMBER(TESTSS) CURRENTDATA(NO) DEGREE(1) -
DYNAMICRULES(BIND) EXPLAIN(YES) ISOLATION(CS) -
RELEASE(COMMIT) VALIDATE(BIND) PATH('DB2C')
RUN PROGRAM(DSNTIAD) PLAN(DSNTIAD)
END
Error
$TEP002I STEPLIB Activated
$TEP006I STEPLIB Dataset List - DDName($TEP0001):
$TEP007I - SYS4.DB2.VCR1M0A.SDSNEXIT
$TEP007I - SYS2.DB2.VC.TSETA2.SDSNLOAD
DSNX200I +A4KK BIND SQL ERROR
USING DB2C AUTHORITY
PLAN=(NOT APPLICABLE)
DBRM=TESTSS
STATEMENT=1746
SQLCODE=-440
SQLSTATE=42884
TOKENS=PROCEDURE.SP_TEST1
CSECT NAME=DSNXOSTP
RDS CODE=1277956
DSNT233I +A4KK UNSUCCESSFUL BIND FOR
PACKAGE =
LOA4KK.CCA4PBT.TESTSS.(2021-05-28-07.45.02.577283)
Why is Stored searched on the PROCEDURE schema? I specified the PATH(DB2C) parameter in BIND. I would have expected the DB2C schema to be used to qualify the stored procedure.
The binding would have gone into error anyway because the Stored is defined on another scheme. But I would have asked the DBAs to modify the PATH parameter to add the STXXAA schema.
Where am I wrong?
EDIT 01/07/2021 Change Approach to the problem.
The customer does not want to change the "PATH" bind parameter. So I changed the "VALIDATE" bind parameter to "RUN". Later I modified the program and at the beginning I put "SET CURRENT PATH = CURRENT PATH, "STXXAA" "
At runtime, the call to the Stored Procedure is failed (SQLCODE -440). The DBAs have assured me they have set GRANT EXECUTE PUBLIC.
Here is an excerpt from the cobol program.
01 VARIABLES.
03 WS-SQLCODE PIC S9(04).
03 WS-STMSQL.
49 WS-STMSQL-L PIC S9(004) COMP.
49 WS-STMSQL-T PIC X(500).
03 WS-STMPATH.
49 WS-STMPATH-L PIC S9(004) COMP.
49 WS-STMPATH-T PIC X(2048).
*
03 WS-QUALIFIER-TEMP PIC X(50).
*
03 WSP-P1 PIC X(02).
03 WSP-P2.
49 WSP-P2-L PIC S9(004) COMP.
49 WSP-P2-T PIC X(50).
03 WSP-P3.
49 WSP-P3-L PIC S9(004) COMP.
49 WSP-P3-T PIC X(50).
03 WSP-P4.
49 WSP-P4-L PIC S9(004) COMP.
49 WSP-P4-T PIC X(3926).
*
MOVE '"STXXAA"' TO WS-QUALIFIER-TEMP
*
MOVE 50 TO WS-STMSQL-L
STRING 'SET CURRENT PATH = CURRENT PATH, WS-QUALIFIER-TEMP
DELIMITED BY SIZE INTO WS-STMSQL-T.
*
EXEC SQL
EXECUTE IMMEDIATE
:WS-STMSQL
END-EXEC.
*
MOVE SQLCODE TO SEA-SQLCODE
PERFORM A9999-CTRL-ERROR.
EXEC SQL
SELECT CURRENT PATH
INTO :WS-STMPATH
FROM SYSIBM.SYSDUMMY1
END-EXEC.
*
DISPLAY 'WS-STMPATH...' WS-STMPATH
*
MOVE SQLCODE TO SEA-SQLCODE
PERFORM A9999-CTRL-ERROR.
*
MOVE '04' TO WSP-P1
MOVE 6 TO WSP-P2-L
MOVE 'TESTSP' TO WSP-P2-T
MOVE 24 TO WSP-P3-L
MOVE 'TEST CALL BATCH 01-07-21' TO WSP-P3-T
MOVE 04 TO WSP-P4-L
MOVE 'TEST' TO WSP-P4-T
*
EXEC SQL
CALL SP_WRITE_LOG
(
:WSP-P1
,:WSP-P2
,:WSP-P3
,:WSP-P4
)
END-EXEC.
*
MOVE SQLCODE TO SEA-SQLCODE
PERFORM A9999-CTRL-ERROR.
Stored Procedure
CREATE PROCEDURE STXXAA.SP_TEST1 (
IN P1 CHAR(2)
,IN P2 VARCHAR(50)
,IN P3 VARCHAR(50)
,IN P4 VARCHAR(3926)
) VERSION V1
LANGUAGE SQL
WLM ENVIRONMENT FOR DEBUG MODE XXYY
ALLOW DEBUG MODE
QUALIFIER STXXAA
AUTONOMOUS
BEGIN
INSERT INTO ...
COMMIT;
END
Error
DSNT408I SQLCODE = -440, ERROR: NO AUTHORIZED PROCEDURE NAMED SP_TEST1
HAVING COMPATIBLE ARGUMENTS WAS FOUND
DSNT418I SQLSTATE = 42884 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOSTP SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 229380 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00038004' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
BOTTOM OF LIST ----------------------------------------------------------------
Sysout - Display "WS-STMPATH"
WS-STMPATH..."DB2C","STXXAA"
If (without modifying the program) I use the qualifier the call statement it works fine.
example
EXEC SQL
CALL STXXAA.SP_WRITE_LOG
(
:WSP-P1
,:WSP-P2
,:WSP-P3
,:WSP-P4
)
END-EXEC.
Have you got any advice?
Thanks,