msa4004
New User
Joined: 25 Feb 2022
Posts: 6
Location: Malaysia
|
|
|
|
Hi all,
I am not sure if this is the correct forum i apologize. I am trying to execute SQL db2 store procedure using DSNTEP2 via JCL.
| Code: |
// 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 store procedure is successful as shown in below code
| 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 store procedure step. result below
| Code: |
**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 store procedure. I am trying to implement store procedure for first time in my company. Appreciate any assistance. Thank you in advance |
|
