Understanding Return Codes in the DB2 Diagnostic Log

I learned a nifty little trick while troubleshooting a connection issue, and thought I would share. It can be useful in getting information out of the db2 diagnostic log.

DB2 Diagnostic Log

The DB2 diagnostic log has a wealth of information. After over 15 years working with DB2, I still learn new things about how to read it every single year.

db2diag tool

I very much like the db2diag tool for parsing the DB2 diagnostic log, and it’s my first line of defense when looking at it. One of the nice things about the db2diag tool is that it can be run to get the contents of the diagnostic log without changing directories. This syntax gives a quick and easy look at the last few records:

db2diag -lastrecords 100

db2diag also has some nice features for filtering and formatting the data to look for specific things. I frequently use syntax like this to highlight recent problems in the diagnostic log:

db2diag -level error,severe -H 3d

For more on db2diag, see my blog entry on the db2diag Tool for Parsing the Diagnostic Log

PDLOGMSGS_LAST24HOURS and PD_GET_LOG_MSGS

When analyzing more deeply, I also sometimes use the PD_GET_LOG_MSGS table function and the PDLOGMSGS_LAST24HOURS administrative view. I’m a big fan of using SQL to get the data I want, and these two make that possible. See my blog on SQL Access to the DB2 Diagnostic Log for more information on these access methods.

Specific Authentication Issue

I learned something new about db2diag this week. I was investigating an issue where a user reported an inability to authenticate(remotely) with DB2. Digging in the DB2 diagnostic log, I found this text:

2017-01-18-13.37.20.290401-480 I381648021E487        LEVEL: Warning
PID     : 30986                TID : 140732262246144 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : SAMPLE
APPHDL  : 0-62447
HOSTNAME: prd-01.example.com
EDUID   : 11715                EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, bsu security, sqlexLogPluginMessage, probe:20
DATA #1 : String with size, 67 bytes
Password validation for user db2inst1 failed with rc = -2146500507

2017-01-18-13.37.20.290642-480 I381648509E558        LEVEL: Warning
PID     : 30986                TID : 140732262246144 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : SAMPLE
APPHDL  : 0-62447
HOSTNAME: prd-01.example.com
EDUID   : 11715                EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, bsu security, sqlexSlsSystemAuthenticate, probe:150
MESSAGE : Failing connection IP address: 192.0.2.0
DATA #1 : String, 16 bytes
application id:
DATA #2 : String with size, 32 bytes
192.0.2.0.58459.170118213732

From those two DB2 diagnostic log entries, I can see that there was a failed password validation attempt at 13:37.

Getting More Information from a Return Code

There is more information available here. The rc = -2146500507 in the first entry here can actually provide a bit more information. Return codes like this can be expanded a bit using this syntax:

$ db2diag -rc -2146500507

Input ZRC string '-2146500507' parsed as 0x800F0065 (-2146500507).

ZRC value to map: 0x800F0065 (-2146500507)
        V7 Equivalent ZRC value: 0xFFFF8665 (-31131)

ZRC class :
        SQL Error, User Error,... (Class Index: 0)
Component:
        SQLO ; oper system services (Component Index: 15)
Reason Code:
        101 (0x0065)

Identifer:
        SQLO_BAD_PSW
Identifer (without component):
        SQLZ_RC_BADPSW

Description:
        The password is not valid for the specified userid

Associated information:
        Sqlcode -30082
SQL30082N  Security processing failed with reason "" ("").

        Number of sqlca tokens : 2
        Diaglog message number: 8111

This error message clearly states that an incorrect password was entered. There are different values for other issues – even things like the user id or password being too long or the password being expired. This doesn’t just apply to authentication issues – return codes can be returned in this format in a number of scenarios. A riddiculously long list of possible RC’s is returned with this syntax:

$ db2diag -rc zrc |more
ABP_LOGIC_ERROR 0x82A90001 (-2102853631)
ABP_INV_PARM 0x80A900FC (-2136407812)
ABP_INTRP 0x80A90003 (-2136408061)
ABP_NO_AGENT 0x80A90442 (-2136406974)
ABP_DISTRIBUTION_REJECTED 0x82A9005A (-2102853542)
ABP_TERMINATE_AGENT 0x82A9005C (-2102853540)
ABP_THROTTLE 0x82A90060 (-2102853536)
ABP_SWITCH_TASK_PRO 0x82A90061 (-2102853535)
ABP_TASK_COMPLETE 0x82A90062 (-2102853534)
ABP_DELETE_TASK_PRO 0x82A90065 (-2102853531)
ABP_SUSPEND_TASK_PRO 0x82A90066 (-2102853530)
ABP_PRIORITY_CHANGED 0x82A90068 (-2102853528)
...

The list is much longer than this. It literally scrolled for more than 15 seconds when I ran this command.