shorti wrote:
> db2 V8.2
>
> I have been looking for a good way to log the 'reason code' when we hit
> an sqlcode that uses them. From what I can tell the reason code is
> stored in sqlca.sqlerrmc. But, there seems to be other information
> stored in this string besides the reason code. The DB2 Info Center
> website states that sqlca.sqlerrml identifies whether sqlerrmc contains
> valid data and the length of the data so I added code that would copy
> the content of sqlerrmc for the length of sqlerrml if sqlerrml is > 0.
> If sqlerrml <= 0 then it just logs "0".
>
> What I am seeing occasionally is junk (or at least it looks like junk)
> in the string even though the sqlerrml says there is valid data. Here
> is a cut of what I am seeing in the log:
>
> sqlcode (-803) reason code (1 MYINST.TABLE1-¦M¦CONTX EX±µáa
> 0
>
> The question is, how to log the 'reason code' only if there is valid
> data in the field? BTW, this function is called when any sqlcode
> occurs so I it would be difficult to log the reason code only for the
> sqlcodes that use them since there are so many of them.
>
> There must be a way to do it because db2 logs the reason code in the
> db2diag log and I never see junk in them.
>
> Thanks for the help!


I am not sure if this is what you want. However i'll provide a "this
works for me". I use it in almost all my PROCEDUREs. It captures all
errors (and continues anyway) but returns the (last) error to the
CALLer.

In the argument list:

OUT OUT_SQLCODE INTEGER,
OUT OUT_SQLSTATE CHAR(0005)

in the variable declare list (the names are hardcoded, so these must be
their names):

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(0005) DEFAULT '00000';

the error handler (declare after variables but before code):

DECLARE CONTINUE HANDLER FOR
SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN
SET OUT_SQLCODE = SQLCODE;
SET OUT_SQLSTATE = SQLSTATE;
END;

At the end make sure something is RETURNed:

IF OUT_SQLSTATE IS NULL THEN

SET OUT_SQLCODE = 0;
SET OUT_SQLSTATE = '00000';

END IF;

END

SQLSTATE and SQLCODE if DECLAREd in a block are filled after each
statement automatically. So, the error handler must snatch their values
and store them elsewhere, otherwise the return from the error handler
itself will likely set them back to success.

B.