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.
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-188.8.131.520401-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-184.108.40.2060642-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.