I am trying to check error handling , but here if I run the below procedure and if the sqlcode out for the select within insert is 0 , it workd fine , there is not a problem, but when the select sql code is <>0 i.e for unsuccesful execution , it gives me error message out but I am getting the sqlcode_ out output parameter as 0 it should give 100 or anything other than 0.

DECLARE P_VAR INTEGER;
DECLARE SQLERROR_VAR VARCHAR(50) DEFAULT 'SUCCESSFUL EXECUTION';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000' ;
DECLARE SQLCODE INT DEFAULT 0;


-- Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT FROM SYSIBM.SYSDUMMY1;

-- INITIALIZE OUTPUT SQL PAAMAETERS
SET P_VAR = P_PLAN;

INSERT INTO SESN.TE_DEL
SELECT A.DOC_SY, A.BAN_SY,B.MNO, B.FORM_ID,P_VAR FROM SESN.SET_DOC AS A, SESN.SET2_DOC AS B
WHERE A.DOC_SY = B.DOC_SY
AND A.UP_DTTM is NOT NULL
AND A.START = 'UPED';
IF SQLCODE <> 0 THEN
-- SET SQLCODE_OUT = SQLCODE;
SET SQLERROR_VAR = 'NO STATS FOUND UPED FOR P_VAR';
SET MESSAGE_OUT = SQLERROR_VAR;
-- SET SQLSTATE_OUT = SQLSTATE;
-- SET SQLCODE_OUT = SQLCODE;
LEAVE P1;
ELSE
COMMIT;
END IF;
.
.
.
.
.
END P1

NEE, your Exit Handler doesn't look right to me. I have never seen a Select statement in one before.

According to the SQL Reference manual Vol 2 for V9.5, the syntax is:

DECLARE EXIT HANDLER FOR ...

Specific-condition value:

SQLSTATE string-constant
or
condition-name

general-condition:

SQLEXCEPTION
SQLWARNING
NOT FOUND
(or any combination)

Try something like

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlcode_out = SQLCODE, sqlstate_out = SQLSTATE;

deleting my post....

Thanks Stealth_DBA

I have tried this way even , but it makes no difference, if you use Select from Sysibm tables or use this way of declaration.

But basically what I see here is in Declaration part I had Set the SqlCOde_out as default to be zero, then after unsuccesful exceution of Insert , my sqlcode is <> 0 so the same should get stored into SqlCode_out in the next satement where I am using SET SQLCODE_OUT = SQLCODE; as mentioned below.


INSERT INTO SESN.TE_DEL
SELECT A.DOC_SY, A.BAN_SY,B.MNO, B.FORM_ID,P_VAR FROM SESN.SET_DOC AS A, SESN.SET2_DOC AS B
WHERE A.DOC_SY = B.DOC_SY
AND A.UP_DTTM is NOT NULL
AND A.START = 'UPED';
IF SQLCODE <> 0 THEN
-- SET SQLCODE_OUT = SQLCODE;
SET SQLERROR_VAR = 'NO STATS FOUND UPED FOR P_VAR';
SET MESSAGE_OUT = SQLERROR_VAR;
SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;
LEAVE P1;
ELSE
COMMIT;
END IF;

But strangely when i use instead statement as
INSERT INTO SESN.TE_DEL
SELECT A.DOC_SY, A.BAN_SY,B.MNO, B.FORM_ID,P_VAR FROM SESN.SET_DOC AS A, SESN.SET2_DOC AS B
WHERE A.DOC_SY = B.DOC_SY
AND A.UP_DTTM is NOT NULL
AND A.START = 'UPED';
IF SQLCODE <> 0 THEN
-- SET SQLCODE_OUT = SQLCODE;
SET SQLERROR_VAR = 'NO STATS FOUND UPED FOR P_VAR';
SET MESSAGE_OUT = SQLERROR_VAR;
SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = 100;
LEAVE P1;
ELSE
COMMIT;
END IF;

; it gives me output as 100. so why it is not reading sqlcode for execution of the insert statement

Because it is reset by subsequent statements. What you are returning is SQLCODE after "SET SQLSTATE_OUT = SQLSTATE", which is 0, since that statement completed successfully.

In other words, saving the value of SQLCODE in SQLCODE_OUT must be the very first statement in the exception handler. If you need both SQLCODE and SQLSTATE you must set both OUT variables in a single statement.