Hello,
Could anybody please explain me why DB2 z/OS 8.1 fails on the simple
procedure below:
Error: DSNHSMS2 LINE 12 COL 6 NESTED COMPOUND STATEMENTS NOT ALLOWED
It failes on
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE L_SYSMSG VARCHAR(512);
GET DIAGNOSTICS EXCEPTION 1 L_SYSMSG = MESSAGE_TEXT;
CALL DB2_RAISERROR (SQLCODE,VAR_PI_COD_EOD_PROCESS||'failed',13,
L_SYSMSG);
END;
Does not DB2 for z/OS allow multiple statements in exception block?
Thank you,
Best regards, Dmitry Tolpeko
Ispirer Systems Ltd, Product Manager
su*****@ispirer.com
---
CREATE PROCEDURE test_sp2 (
VAR_PI_COD_EOD_PROCESS VARCHAR ( 40 ) ,
VAR_PI_COD_PROC_CATEGORY SMALLINT,
OUT VAR_PO_RESTART_POSTN VARCHAR ( 256 ) ,
OUT VAR_PO_CTR_UPDAT_SRLNO INTEGER,
OUT RETURN_VAL INTEGER )
LANGUAGE SQL
BEGIN
DECLARE VAR_SHELL_STATUS SMALLINT;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE L_SYSMSG VARCHAR(512);
GET DIAGNOSTICS EXCEPTION 1 L_SYSMSG = MESSAGE_TEXT;
CALL DB2_RAISERROR (SQLCODE,VAR_PI_COD_EOD_PROCESS||'failed',13,
L_SYSMSG);
END;
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
DECLARE L_SYSMSG VARCHAR(512);
GET DIAGNOSTICS EXCEPTION 1 L_SYSMSG = MESSAGE_TEXT;
CALL DB2_RAISERROR (SQLCODE,VAR_PI_COD_EOD_PROCESS||'failed',13,
L_SYSMSG);
END;
DECLARE EXIT HANDLER FOR NOT FOUND
BEGIN
DECLARE L_SYSMSG VARCHAR(512);
GET DIAGNOSTICS EXCEPTION 1 L_SYSMSG = MESSAGE_TEXT;
CALL DB2_RAISERROR (SQLCODE,VAR_PI_COD_EOD_PROCESS||'failed',13,
L_SYSMSG);
END;
SELECT CTR_UPDAT_SRLNO,
RESTART_POSTN
INTO VAR_PO_CTR_UPDAT_SRLNO, VAR_PO_RESTART_POSTN
FROM BA_SHELL_RESTART
WHERE COD_EOD_PROCESS = VAR_PI_COD_EOD_PROCESS
AND COD_PROC_CATEGORY = VAR_PI_COD_PROC_CATEGORY ;
COMMIT;
SET RETURN_VAL = 0;
RETURN 0;
END