Hi All,

I don't have access to SYSIBM schema and SYSIBM>SYSDUMMY1. I want to get value of SQLSTATE & SQLCODE into exit handler and store into output parameters.

I tried following code:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN NOT ATOMIC
SET SQLCODE_OUT = SQLCODE; ---1
SET SQLSTATE_OUT = SQLSTATE; ---2
END;

but for second statement (--2) it always gives value '00000' as previous one executed successfully.

Can anyone let me know how to get value of SQLSTATE AND SQLCODE in single statement without using SYSIBM.SYSDUMMY1.

Thanks in Advance,

Try:
SET (SQLCODE_OUT,SQLSTATE_OUT) = (SQLCODE,SQLSTATE);

Andy

or
VALUES(SQLCODE,SQLSTATE) INTO SQLCODE_OUT,SQLSTATE_OUT

Thanks a lot for quick reply.

"VALUES(SQLCODE,SQLSTATE) INTO SQLCODE_OUT,SQLSTATE_OUT" will work but "SET (SQLCODE_OUT,SQLSTATE_OUT) = (SQLCODE,SQLSTATE);" won't work. To use SET statement in above manner you have to use TableName.ColumnName as per my today's understanding. Please correct me in case.

"I don't have access to SYSIBM schema and SYSIBM>SYSDUMMY1". Tell your DBA that access to this table (actually in some versions it is a view) is a basic requirement of writing good SQL. Not granting select on it to PUBLIC is an indication of someone who doesn't understand what they are doing.

What version/FP of DB2 are you using? A few years ago there were some fixes in this area to change the processing of SET SQLCODE_OUT = SQLCODE from an SQL statement (which destroyed SQLSTATE) into a C language statement.

James Campbell