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!
9  6686 
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" <lb******@juno.com> wrote in message
news:11**********************@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" <lb******@juno.com> wrote in message
news:11**********************@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" <lb******@juno.com> wrote in message
news:11*********************@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" <sr*****@ca.ibm.com> wrote in message
news:4e*************@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" <sr*****@ca.ibm.com> wrote in message
news:4e*************@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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jjesso |
last post by:
I am trying to add a new logging level.
logging.config.fileConfig("bengineLog.cfg")
logging.CLIENT = logging.INFO + 1
logging.addLevelName( logging.CLIENT, 'CLIENT' )
logging.root.setLevel( )...
|
by: Karuppasamy |
last post by:
H
I am trying to use the Logging Module provided by Microsoft Application Blocks for .Net
I installed everything as per the Instructions given in the 'Development Using the Logging Block'
...
|
by: Joe Bloggs |
last post by:
Hi all,
I take the pleasure to inform that Dotnet Commons Logging has been released
for use.
Dotnet Commons Logging, a subproject of the Donet Commons project currently
located under the...
|
by: Chris Smith |
last post by:
Hola, pythonisas:
The documentation for the logging module is good, but a bit obscure.
In particular, there seems to be a lot of action at a distance.
The fact that getLogger() can actually be a...
|
by: apexi.200sx |
last post by:
Just wondering if anyone could give me advice on handling potential
error conditions in python, it seems that exceptions are used alot more
for this stuff than in other languages.
I have...
|
by: rajesh.hanchate |
last post by:
Please help me in resolving this issue.
I am using EnterpriseLibrary 2.0 Exception and logging block for
logging exceptions to event log. It works fine for sometime. After some
time it stops...
|
by: Tekkaman |
last post by:
I'm getting a strange behaviour from the "pathname" and "lineno"
formatter mapping keys. Instead of my file and my line number I get:
/usr/lib/python2.4/logging/__init__.py
as the file, and...
|
by: Matthew Wilson |
last post by:
In this code, I tried to kill my thread object by setting a variable on it
to False.
Inside the run method of my thread object, it checks a different
variable.
I've already rewritten this...
|
by: samwyse |
last post by:
In the Python 2.5 Library Reference, section 14.5.3 (Logging to
multiple destinations), an example is given of logging to both a file
and the console. This is done by using logging.basicConfig()...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
