I am new to store procedure and trying to create a simple store procedure to read from table. I am using IBM DB2 SQL.
--#SET MAXERRORS 0
--#SET TERMINATOR ~
SET CURRENT SQLID = 'SIWRO1'~
CREATE OR REPLACE PROCEDURE DWR000A.SELECT_PSID_RIZ(
IN P_CONN INT
,IN P_PLNN INT
)
LANGUAGE SQL
RESULT SETS 1
READS SQL DATA
BEGIN
DECLARE C1 CURSOR FOR
SELECT PSID,
JOBINFO
FROM DWR000A.TEST
WHERE CONN = P_CONN
AND PLNN = P_PLNN;
OPEN C1;
END
~
--#SET TERMINATOR ;
COMMIT;
Getting error below when executing it.
SQLERROR ON CREATE COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -4743, ERROR: ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL
DSNT418I SQLSTATE = 56038 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5R SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 50 0 0 -1 1 2721 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF'
INFORMATION
I am not able to figure what I am doing wrong or how to fix it. I appreciate some expert advice. Thanks
Update 11/28/2024 i manage to bypass the error by doing 1 function which is to create. Thanks
This error is specific to your Db2-for-Z/OS subsystem configuration. The description of the error code is here
In short it means you are trying to use some functionality that is not yet enabled for your target Db2-subsystem.
Speak with your DBA for Db2-for-Z/OS to learn about the compatibity level ( APPLCOMPAT system parameter ) that is configured for your target subsystem, and which functionality is enabled for that subsystem.
With that information, refer to the Db2 Knowledge Centre for Db2 for Z/OS for the exact version that the compatibility level specifies.
The DBA might also enable a higher compatibility level if it is a testing subsystem, which may allow your procedure to compile and run.