Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DB2DIAG.LOG
Tips and Tricks
1. What is db2diag.log ?
The most important diagnostic error log available is the db2diag.log file. On Unix system, the default location of this file is <instance-home>/sqllib/db2dump . When errors occur within DB2, the product component which is receiving the error will attempt to log information that will help to determine and fix the problem.
2. Configuration Parameters
The following are the database manager configuration parameters related to db2diag.log file.
2.1 DIAGPATH
Db2diag.log file is located in the directory defined by this configuration parameter. If the value for this parameter is null, then the default path is used.
2.2 DIAGLEVEL
This parameter is used to control the amount of information and the type of errors that are recorded in the db2diag.log file.
DIAGLEVEL Value Implication
0 No diagonostic data capture
1 Severe Errors only
2 Severe and Non-Severe errors
3 (Default) Severe and Non-Severe errors and warning messages
4 Severe and Non-Severe errors ,warning and informational messages
The diaglevel 4 can be used to diagnose problems and should be used only when there is a need for detailed information. There will be a performance hit if DIAGLEVEL 4 is used.
3. Interpreting the db2diag.log file
The following is an example entry in the db2diag.log file:
2000-03-06-11.53.18.001160 _1_ Instance
ayroll _2_ Node:000 _3_
PID:44829(db2agent (SAMPLE))_4_ TID:352_5_
Appid:*LOCAL.payroll.000306140834_6_
lock_manager _7_ sqlplrq _8_ Probe:111 _9_ Database:SAMPLE _10_
DIA9999E _11_ An internal return code occurred. Report the following:
"0xFFFFE10E". _12_
_1_ A timestamp for the message.
_2_ The name of the instance generating the message.
_3_ Always 000 for Enterprise Edition
_4_ Identification of the process generating the message. In this example,
the message came from the process identified as 44829. The name of
this process is db2agent and it is connected to the database named
SAMPLE.
_5_ Identification of the table generating the message. In this example, the
message came from the table identified as 352.
_6 Application id
_7_ The DB2 component that is writing the message.
_8_ The name of the function that is providing the message. This function
operates within the DB2 subcomponent that is writing the message.
To find out more about the type of activity performed by a function,
look at the fourth letter of its name. In this example, the letter .p. in
the function .sqlplrq. indicates a data protection problem. (Logs could
be damaged, for example.)
The following list shows some of the letters used in the fourth
position of the function name, and the type of activity they identify:
b Buffer pools
c Communication between clients and servers
d Data management
e Engine processes
o Operating system calls (such as opening and closing files)
p Data protection (such as locking and logging)
r Relational database services
s Sorting
x Indexing
_9_ Identification of the internal error that was reported.
_10_ The database on which the error occurred.
_11_ Diagnostic message indicating that an internal error occurred.
_12_ Hexadecimal representation of an internal return code.
Sometimes the structure of SQLCA is dumped into the db2diag.log .
1997-03-16-11.53.18.001160 Instanceayroll Node:000
PID:44829(db2agent (SAMPLE)) Appid:*LOCAL.payroll.970317140834
relation_data_serv sqlrerlg Probe:17 Database:SAMPLE
DIA9999E An internal return code occurred. Report the following : "0xFFFFE101".
Data Title :SQLCA pid(14358) _1_
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -980 _2_ sqlerrml: 0
sqlerrmc: _3_
sqlerrp : sqlrita
sqlerrd _4_: (1) 0xFFFFE101 _5_ (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
_1_ Beginning of the SQLCA entry.
_2_ The SQL state (when negative, an error has occurred).
_3_ Any reason codes associated with the SQL error code.
_4_ Sometimes there are several errors leading to the final SQL error code.
These errors are shown in sequence in the sqlerrd area.
_5_ The hexadecimal representation of an SQL error
The most important diagnostic error log available is the db2diag.log file. On Unix system, the default location of this file is <instance-home>/sqllib/db2dump . When errors occur within DB2, the product component which is receiving the error will attempt to log information that will help to determine and fix the problem.
2. Configuration Parameters
The following are the database manager configuration parameters related to db2diag.log file.
2.1 DIAGPATH
Db2diag.log file is located in the directory defined by this configuration parameter. If the value for this parameter is null, then the default path is used.
2.2 DIAGLEVEL
This parameter is used to control the amount of information and the type of errors that are recorded in the db2diag.log file.
DIAGLEVEL Value Implication
0 No diagonostic data capture
1 Severe Errors only
2 Severe and Non-Severe errors
3 (Default) Severe and Non-Severe errors and warning messages
4 Severe and Non-Severe errors ,warning and informational messages
The diaglevel 4 can be used to diagnose problems and should be used only when there is a need for detailed information. There will be a performance hit if DIAGLEVEL 4 is used.
3. Interpreting the db2diag.log file
The following is an example entry in the db2diag.log file:
2000-03-06-11.53.18.001160 _1_ Instance
ayroll _2_ Node:000 _3_PID:44829(db2agent (SAMPLE))_4_ TID:352_5_
Appid:*LOCAL.payroll.000306140834_6_
lock_manager _7_ sqlplrq _8_ Probe:111 _9_ Database:SAMPLE _10_
DIA9999E _11_ An internal return code occurred. Report the following:
"0xFFFFE10E". _12_
_1_ A timestamp for the message.
_2_ The name of the instance generating the message.
_3_ Always 000 for Enterprise Edition
_4_ Identification of the process generating the message. In this example,
the message came from the process identified as 44829. The name of
this process is db2agent and it is connected to the database named
SAMPLE.
_5_ Identification of the table generating the message. In this example, the
message came from the table identified as 352.
_6 Application id
_7_ The DB2 component that is writing the message.
_8_ The name of the function that is providing the message. This function
operates within the DB2 subcomponent that is writing the message.
To find out more about the type of activity performed by a function,
look at the fourth letter of its name. In this example, the letter .p. in
the function .sqlplrq. indicates a data protection problem. (Logs could
be damaged, for example.)
The following list shows some of the letters used in the fourth
position of the function name, and the type of activity they identify:
b Buffer pools
c Communication between clients and servers
d Data management
e Engine processes
o Operating system calls (such as opening and closing files)
p Data protection (such as locking and logging)
r Relational database services
s Sorting
x Indexing
_9_ Identification of the internal error that was reported.
_10_ The database on which the error occurred.
_11_ Diagnostic message indicating that an internal error occurred.
_12_ Hexadecimal representation of an internal return code.
Sometimes the structure of SQLCA is dumped into the db2diag.log .
1997-03-16-11.53.18.001160 Instance
ayroll Node:000PID:44829(db2agent (SAMPLE)) Appid:*LOCAL.payroll.970317140834
relation_data_serv sqlrerlg Probe:17 Database:SAMPLE
DIA9999E An internal return code occurred. Report the following : "0xFFFFE101".
Data Title :SQLCA pid(14358) _1_
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -980 _2_ sqlerrml: 0
sqlerrmc: _3_
sqlerrp : sqlrita
sqlerrd _4_: (1) 0xFFFFE101 _5_ (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
_1_ Beginning of the SQLCA entry.
_2_ The SQL state (when negative, an error has occurred).
_3_ Any reason codes associated with the SQL error code.
_4_ Sometimes there are several errors leading to the final SQL error code.
These errors are shown in sequence in the sqlerrd area.
_5_ The hexadecimal representation of an SQL error