I am trying to execute SQL db2 stored procedure using DSNTEP2 via JCL.
// SET DATE=241122
/*
//***********************************************************
//* CREATE STORED PROCEDURE DCSTEP2F IS USING DSNTEP2
//***********************************************************
//CATDEL2 EXEC CATDEL,
// INNAME=WW.W.DCD.D&DATE..BUILDSPR.KLI30105.TR
//BUILD01 EXEC DCSTEP2F,
// DB2SYS=DBB0,
// SQLLIB=TEST.RIZ.SP,
// SQLNAME=BUILD@SP,
// OUTDSN=WW.W.DCD.D&DATE..BUILDSPR.KLI30105.TR
//***********************************************************
//* EXECUTE STORED PROCEDURE DCSTEP2F IS USING DSNTEP2
//***********************************************************//CATDEL2 EXEC CATDEL,
// INNAME=WW.W.DCD.D&DATE..CALL.KLI30105.TR
//EXECU01 EXEC DCSTEP2F,
// DB2SYS=DBB0,
// SQLLIB=TEST.RIZ.SP,
// SQLNAME=CALL@SP,
// OUTDSN=WW.W.DCD.D&DATE..CALL.KLI30105.TR
The JCL step to create the simple stored procedure is successful as shown in below code
***INPUT STATEMENT:
SET CURRENT SQLID = 'SIWRO1'~
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 00000 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFF
INFORMATION
SET SUCCESSFUL
1PAGE 1
***INPUT STATEMENT:
CREATE PROCEDURE DWR000A.HELLO_WORLD1(
OUT MSG_OUT VARCHAR(35)
)
LANGUAGE SQL
READS SQL DATA
BEGIN
SET MSG_OUT = 'HELLO WORLD';
END
~
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 00000 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFF
INFORMATION
CREATE SUCCESSFUL
However, I am having issue with the executing the stored procedure step. result below
**INPUT STATEMENT:
EXEC SQL
HELLO_WORLD1()
END-EXEC
;
QLERROR ON EXEC COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "HELLO_WORLD1". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <ERR_STMT> <WNG_STMT>
TRANSFER GET SQL SAVEPOINT HOLD FREE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 10 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
INFORMATION
I am not sure what the issue is since the procedure is there in SYSIBM.SYSROUTINES where I ran SPUFI in the DB2.
I am really new at this stored procedure. Appreciate any assistance. Thank you in advance.
updated after comments. 11/26/2024
I rerun again to execute the stored procedure but get this error
***INPUT STATEMENT:
EXEC SQL
CALL HELLO_WORLD1()
END-EXEC
;
SQLERROR ON EXEC COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -84, ERROR: UNACCEPTABLE SQL STATEMENT
DSNT418I SQLSTATE = 42612 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHAPL2 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 1 0 0 -1 10 101 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000001' X'00000000' X'00000000' X'FFFFFFFF' X'000
INFORMATION
My stored procedure is a simple hello world as below. My company is using stored procedure for first time and we trying to learn. We using standard IBM DB2
CREATE PROCEDURE DWR000A.HELLO_WORLD1(
OUT MSG_OUT VARCHAR(35)
)
LANGUAGE SQL
READS SQL DATA
BEGIN
SET MSG_OUT = 'HELLO WORLD';
END
~
COMMIT;
Appreciate any feedback and advice
