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'm not completely clear on what is going on in your routine.

According to the Information Center:
---------------------------------------------------------------------------------------------------------------------

sqlerrmc VARCHAR (70) Contains one or more tokens, separated by
X'FF', which are substituted for variables in the descriptions of error
conditions.
This field is also used when a successful connection is completed.

When a NOT ATOMIC compound SQL statement is issued, it may contain
information on up to seven errors.

The last token might be followed by X'FF'. 7 The sqlerrml value will
include any trailing X'FF'.


---------------------------------------------------------------------------------------------------------------------
Therefore, it is quite reasonable to see message tokens in the
sqlca.sqlerrmc field; that's what is supposed to be there.

But the specific information you are seeing has me confused. According to
the example you gave, the message you are seeing doesn't make a lot of
sense: SQL0803N should not have a reason code!! What it should have,
according to the manual are two message tokens, the first of which should be
an index-id and the second of which should be a table. But in your case, you
appear to be getting something rather different. I'm pretty sure you can't
create an index named '1' (or an index that _starts_ with a '1'), and I'm
pretty sure that you can't put vertical bars or plus/minus signs, Greek
letters or accented letters in the names of either indexes or tables.). I'm
at a loss to understand how you came up with those characters unless maybe
you are using a foreign language code page. Even then, I didn't think you
could use unusual characters in index or table names. It is also clear from
the manual that SQL0803N should NOT be setting a reason code, otherwise I
might suspect that the '1' was the reason code in this case.

You _are_ using DB2 for Windows, Unix, and Linux, right? I believe the
documentation is always kept updated to the latest fixpack so it ought to be
accurate.

As for your main question, the reason code is not stored in its own separate
field in the SQLCA. It should appear in the SQLERRMC field with the other
tokens, assuming that the message in question supplies a reason code; most
messages do _not_ set reason codes.

I don't pretend to know how the log writers inside DB2 write the db2diag.log
but I would guess that they are getting the reason code from SQLERRMC. But
maybe one of the Toronto Lab employees who monitors this newsgroup will jump
in and give you an authoritative explanation.

--
Rhino


"shorti" <lbryan21@juno.com> wrote in message
news:1149533093.686037.147270@j55g2000cwa.googlegr oups.com...
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!

Rhino wrote:
> I'm not completely clear on what is going on in your routine.
>
> According to the Information Center:
> ---------------------------------------------------------------------------------------------------------------------
>
> sqlerrmc VARCHAR (70) Contains one or more tokens, separated by
> X'FF', which are substituted for variables in the descriptions of error
> conditions.
> This field is also used when a successful connection is completed.
>
> When a NOT ATOMIC compound SQL statement is issued, it may contain
> information on up to seven errors.
>
> The last token might be followed by X'FF'. 7 The sqlerrml value will
> include any trailing X'FF'.

Yes, I read that too. Although it doesnt specify precisely what
information you might get. I had to dig deep to determine the 'reason
code' is stored in the sqlerrmc. As this implies, sqlerrmc is used for
other info than just the reason code.


> But the specific information you are seeing has me confused. According to
> the example you gave, the message you are seeing doesn't make a lot of
> sense: SQL0803N should not have a reason code!! What it should have,
> according to the manual are two message tokens, the first of which should be
> an index-id and the second of which should be a table. But in your case, you
> appear to be getting something rather different. I'm pretty sure you can't
> create an index named '1' (or an index that _starts_ with a '1'), and I'm
> pretty sure that you can't put vertical bars or plus/minus signs,

Well...it might be just junk at the end of the string. I am not sure
about the 1 either...hence my confusion. I was actually expecting the
string to be null terminated at the end of the data OR to find the
0x'FF' values in between or at the end. I dont see either. I wanted
to make sure I wasnt cutting off the string until I knew how to
determine to best whats in it first.

> As for your main question, the reason code is not stored in its own separate
> field in the SQLCA. It should appear in the SQLERRMC field with the other
> tokens,

Ok...just wanted to make sure I wasnt way off base.

Oh, btw, I am only logging if the sqlerrml is > 0 so I would expect
something in sqlerrmc. The reason code I pasted above is from a simple
strcpy of sqlca->sqlerrmc

This is running on an AIX box.

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.

"shorti" <lbryan21@juno.com> wrote in message
news:1149548166.271445.205640@i40g2000cwc.googlegr oups.com...
> Rhino wrote:
>> I'm not completely clear on what is going on in your routine.
>>
>> According to the Information Center:
>> ---------------------------------------------------------------------------------------------------------------------
>>
>> sqlerrmc VARCHAR (70) Contains one or more tokens, separated by
>> X'FF', which are substituted for variables in the descriptions of error
>> conditions.
>> This field is also used when a successful connection is completed.
>>
>> When a NOT ATOMIC compound SQL statement is issued, it may contain
>> information on up to seven errors.
>>
>> The last token might be followed by X'FF'. 7 The sqlerrml value
>> will
>> include any trailing X'FF'.
>
> Yes, I read that too. Although it doesnt specify precisely what
> information you might get. I had to dig deep to determine the 'reason
> code' is stored in the sqlerrmc. As this implies, sqlerrmc is used for
> other info than just the reason code.
>
That "other info" is the message tokens mentioned in the manual excerpt I
posted. Many, but not all, error messages in DB2 include specific values.
For example, in SQL0803N, the message will tell you the actual index name
and table name that are experiencing the problem. Therefore, the SQLERRMC
value for an SQL0803N message should contain that index name and table name.
But it shouldn't contain anything else, like a reason code, because SQL0803N
is not supposed to give a reason code. That's what is odd about your
SQLERRMC: it contains tokens that don't appear to be associated with the
error you are getting.
>
>> But the specific information you are seeing has me confused. According to
>> the example you gave, the message you are seeing doesn't make a lot of
>> sense: SQL0803N should not have a reason code!! What it should have,
>> according to the manual are two message tokens, the first of which should
>> be
>> an index-id and the second of which should be a table. But in your case,
>> you
>> appear to be getting something rather different. I'm pretty sure you
>> can't
>> create an index named '1' (or an index that _starts_ with a '1'), and
>> I'm
>> pretty sure that you can't put vertical bars or plus/minus signs,
>
> Well...it might be just junk at the end of the string. I am not sure
> about the 1 either...hence my confusion. I was actually expecting the
> string to be null terminated at the end of the data OR to find the
> 0x'FF' values in between or at the end. I dont see either. I wanted
> to make sure I wasnt cutting off the string until I knew how to
> determine to best whats in it first.
>
Perhaps it is a simple initialization problem? If your routine is written in
C, as it appears, maybe you had some stuff in the SQLERRMC field from a
previous iteration of the routine and didn't clear it before writing the
SQLERRMC value for the SQL0803N error? I'm not fluent in C and haven't
touched it in years but I seem to recall that being a frequent problem for
me when I did play with C language 10 years ago....

>> As for your main question, the reason code is not stored in its own
>> separate
>> field in the SQLCA. It should appear in the SQLERRMC field with the other
>> tokens,
>
> Ok...just wanted to make sure I wasnt way off base.
>
No, I think you are doing things more-or-less correctly. Just have a look at
your routine and check the initialization; that could clean up the actual
contents of the SQLERRMC. Or maybe the documentation on SQL0803N is simply
wrong and you are getting exactly what you should get! The IBM documentation
is certainly not guaranteed to be 100% perfect; the message might have been
revised and the tech writers might have missed updating that error
message....

Just don't expect to get reason codes in every SQLERRMC. In some cases,
where there are no variables in the message, you will find SQLERRMC empty.
In many cases, you will find message tokens in SQLERRMC but they will be
things like table names and not reason codes. In some cases, you will find
reason codes but they may be mixed in among other tokens and you will need
to figure out which token is the reason code. It's easy enough to do
manually: you simply look at the message in the manual and the italicized
parts of the message should tell you what tokens to expect in what order.
But doing that programmatically is somewhat harder....

--
Rhino

"shorti" <lbryan21@juno.com> wrote in message
news:1149551369.400983.11130@y43g2000cwc.googlegro ups.com...
> Oh, btw, I am only logging if the sqlerrml is > 0 so I would expect
> something in sqlerrmc. The reason code I pasted above is from a simple
> strcpy of sqlca->sqlerrmc
>
That all sounds perfectly reasonable; just double-check to make sure that
you reinitialize the field that will contain your SQLERRMC value to blanks
before writing into it each time.

> This is running on an AIX box.
>

As far as I know, it shouldn't make any difference whether you are running
AIX, Windows, or some other form of Unix or Linux. I believe the meaning of
SQLERRMC should be standard across all operating systems, probably even
mainframe and AS/400.

However, the exact message text and what tokens are present in it COULD
might be different on z/OS or AS/400 than it is on Windows/Linux/Unix.
That's why I verified that you are using DB2 on Unix/Windows/Linux, not DB2
on z/OS!

--
Rhino

Couple of points:
Anything past the announced length of the message is indeed garbage.
0xFF is used to separate token.
Example:
db2 connect to test
db2 pi pa po
SQL0104N An unexpected token "pa" was found following "pi ".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: -104 sqlerrml: 26
sqlerrmc: paÿpi ÿJOIN <joined_table>
sqlerrp : SQLNP012
sqlerrd : (1) -2145779603 (2) 0 (3) 0
(4) 0 (5) -705 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 42601

So the length is 26 bytes and the 'ÿ' separates the three token.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:4elte8F1f0m96U1@individual.net...
> Couple of points:
> Anything past the announced length of the message is indeed garbage.
> 0xFF is used to separate token.
> Example:
> db2 connect to test
> db2 pi pa po
> SQL0104N An unexpected token "pa" was found following "pi ".
> Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
> SQLCA Information
>
> sqlcaid : SQLCA sqlcabc: 136 sqlcode: -104 sqlerrml: 26
> sqlerrmc: paÿpi ÿJOIN <joined_table>
> sqlerrp : SQLNP012
> sqlerrd : (1) -2145779603 (2) 0 (3) 0
> (4) 0 (5) -705 (6) 0
> sqlwarn : (1) (2) (3) (4) (5) (6)
> (7) (8) (9) (10) (11)
> sqlstate: 42601
>
> So the length is 26 bytes and the 'ÿ' separates the three token.
>
That all makes good sense but I'm still confused by how the original poster
got "1 MYINST.TABLE1-¦M¦CONTX EX±µáa" from the SQLERRMC field unless his
code is not blanking out a previous value; otherwise, I would have expected
SQL0803N to put a table name and an index name in SQLERRMC.

--
Rhino

Rhino wrote:
> "Serge Rielau" <srielau@ca.ibm.com> wrote in message
> news:4elte8F1f0m96U1@individual.net...
>> Couple of points:
>> Anything past the announced length of the message is indeed garbage.
>> 0xFF is used to separate token.
>> Example:
>> db2 connect to test
>> db2 pi pa po
>> SQL0104N An unexpected token "pa" was found following "pi ".
>> Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
>> SQLCA Information
>>
>> sqlcaid : SQLCA sqlcabc: 136 sqlcode: -104 sqlerrml: 26
>> sqlerrmc: paÿpi ÿJOIN <joined_table>
>> sqlerrp : SQLNP012
>> sqlerrd : (1) -2145779603 (2) 0 (3) 0
>> (4) 0 (5) -705 (6) 0
>> sqlwarn : (1) (2) (3) (4) (5) (6)
>> (7) (8) (9) (10) (11)
>> sqlstate: 42601
>>
>> So the length is 26 bytes and the 'ÿ' separates the three token.
>>
> That all makes good sense but I'm still confused by how the original poster
> got "1 MYINST.TABLE1-¦M¦CONTX EX±µáa" from the SQLERRMC field unless his
> code is not blanking out a previous value; otherwise, I would have expected
> SQL0803N to put a table name and an index name in SQLERRMC.
>
> --
> Rhino
>
>
Index ID is 1. Schema is MYINST, Table is TABLE1
It's very possible the space isn't flushed...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/