Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Tips and Tricks

DB2DIAG.LOG by sathyarams
Posted: 31 Dec 02

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.


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.


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- _1_ Instance:payroll _2_ Node:000 _3_
PID:44829(db2agent (SAMPLE))_4_ TID:352_5_
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
_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- Instance:payroll 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)

_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

Back to IBM: DB2 FAQ Index
Back to IBM: DB2 Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close