Parameter Wednsday – DBM CFG: DIAGLEVEL

Continuing my trend of attacking some of the simpler parameters first, I’m going to cover DIAGLEVEL this week.

DB2 Version This Was Written For

9.7

Parameter Name

DIAGLEVEL

Where This Parameter Lives

Database Manager Configuration

How To Check Value

> db2 get dbm cfg |grep DIAGLEVEL
 Diagnostic error capture level              (DIAGLEVEL) = 3

OR

> db2 "select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBMCFG where name='diaglevel' with ur"

NAME                             VALUE            VALUE_FLAGS DEFERRED_VALUE   DEFERRED_VALUE_FLAGS DATATYPE
-------------------------------- ---------------- ----------- ---------------- -------------------- ----------------
diaglevel                        3                NONE        3                NONE                 INTEGER

  1 record(s) selected.

Description

Level of logging for the DB2 diagnostic log

Impact

Can have performance impact if set too high, especially on loads

Default

3

Range/Values

0 – No diagnostic data captured

1 – Severe errors only

2 – All errors

3 – All errors and warnings

4 – All errors, warnings, and informational messages

Recycle Required To Take Effect?

No – should take effect immediately

Can It Be Set To AUTOMATIC?

No, there is no automatic setting for this parameter.

How To Change It

 db2 update dbm cfg using DIAGLEVEL N

Where N is the desired logging level

Rule of Thumb

Leave it at the default. That almost always works for this parameter.

Tuning Considerations

Generally, you will have this at either 3 or 4. You should only use 4 when troubleshooting a specific issue for a limited period of time. You should never leave it at 4 while doing LOADs.

Related Error Messages

War Stories From The Real World

I’ve seen a setting of 4 for this increase the time that a nightly LOAD process took by a factor of 10 or more. So be cautious when using 4, even if it is at the recommendation of support. A setting of 4 can also generate a lot of output, so keep a close eye on filesystem utilization while you have it set at 4. Return the setting to 3 as quickly as possible. I’ve never seen a setting other than 3 or 4 used in a real-world situation.

Link To Info Center

 http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000298.html

Related Parameters

diagpath – Diagnostic data directory path configuration parameter (Info Center link)