With these sample queries, find out how to review your Db2 messages from a high level overview down to the details.
You can review the Db2 Console output from different perspectives and simply bypass the boring „J“ ones for Full Log and Log Offload to leave behind the interesting ones.
Now you have two possibilities to get the Console Messages from Db2, just write your own IFI program or buy our newly updated software (!)
Buy it or Write it
Buy it 😉
Indeed. One of our major products Db2 SQL WorkLoadExpert just got an extra little addition: The Db2 Console message support.
Now I must admit I thought “Whoopy do!” that will be excellent data… but I have to say it is actually incredibly cool!
Write it 😯
You can, of course, write your own IFI program to get the Console Messages from Db2. Especially after you have been inspired by the following queries!
After a while I ran this query (remember this is all on my little testplex!) which worked on data processed from 2018-04-23-12.02.07.303151 up until today. So about five months.
SELECT COUNT(*) FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES FOR FETCH ONLY WITH UR ; ---------+---------+---------+-------- ---------+---------+---------+-------- 18684 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
Db2 is a talkative little beast : get the Console Overview
That is quite a lot of messages… so now the next query GROUPing it into message ids to get the overview:
SELECT DB2_MESSAGE_ID , COUNT(*) FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES GROUP BY DB2_MESSAGE_ID ORDER BY 1 FOR FETCH ONLY WITH UR ;
Very interesting selection of messages here (For details look further down!) even some of the “old school” E ending ones!
---------+---------+---------+------- DB2_MESSAGE_ID ---------+---------+---------+------- DSNB250E 175 DSNB260I 1013 DSNI005I 162 DSNI006I 162 DSNI031I 7 DSNI070I 24 DSNJ001I 4164 <- Current copy 1 is xx--xx DSNJ002I 4164 <- Full active log DSNJ003I 2082 <- Full archive log DSNJ004I 15 DSNJ005I 15 DSNJ031I 1421 DSNJ103I 918 DSNJ110E 349 DSNJ111E 168 DSNJ115I 641 DSNJ125I 277 DSNJ139I 2575 <- Log offload ended DSNL003I 12 DSNL004I 12 DSNL005I 14 DSNL006I 11 DSNL007I 2 DSNL008I 2 DSNL012I 2 DSNL030I 12 DSNL500I 1 DSNL510I 31 DSNL511I 2 DSNL512I 12 DSNL519I 15 DSNL523I 10 DSNP002I 2 DSNP007I 14 DSNP009I 2 DSNP010I 2 DSNP016I 2 DSNR035I 1 DSNT375I 1 DSNT376I 5 DSNT500I 3 DSNT501I 33 DSNT736I 1 DSNU241I 1 DSNU971I 7 DSNU973I 7 DSN3201I 133 DSNE610I NUMBER OF ROWS DISPLAYED IS 47
Boring, boring, boring: get the “J ” message
Nice little break down huh? Now weeding out the “boring ones,” the J messages about:
- Full Log and
- Log Offload leaves:
SELECT COUNT(*)
FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES
WHERE NOT DB2_MESSAGE_ID IN ('DSNJ001I'
, 'DSNJ002I'
, 'DSNJ003I'
, 'DSNJ139I')
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
---------+---------+---------+---------+
5699
DSNE610I NUMBER OF ROWS DISPLAYED IS 1Get now the detailed Console view
And drilling on down:
SELECT DB2_MESSAGE_ID , COUNT(*)
FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES
WHERE NOT DB2_MESSAGE_ID IN ('DSNJ001I'
, 'DSNJ002I'
, 'DSNJ003I'
, 'DSNJ139I')
GROUP BY DB2_MESSAGE_ID
ORDER BY 1
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
DB2_MESSAGE_ID
---------+---------+---------+---------+
DSNB250E 175 <- Page range added to LPL
DSNB260I 1013 <- Long running reader
DSNI005I 162 <- LPL or GREP cannot be done
DSNI006I 162
DSNI031I 7 <- Lock escalation
DSNI070I 24 <- FTB usage stats
DSNJ004I 15
DSNJ005I 15
DSNJ031I 1421 <- 5000 log recs
DSNJ103I 918 <- Log allocation error
DSNJ110E 349 <- Last copy of log nnn% full
DSNJ111E 168 <- Out of log space
DSNJ115I 641 <- Log offload failed
DSNJ125I 277 <- Error dumping BSDS
DSNL003I 12
DSNL004I 12
DSNL005I 14 <- DDF is stopping
DSNL006I 11
DSNL007I 2 <- DDF is abnormally ending
DSNL008I 2
DSNL012I 2
DSNL030I 12
DSNL500I 1
DSNL510I 31
DSNL511I 2
DSNL512I 12
DSNL519I 15
DSNL523I 10
DSNP002I 2 <- Define failed for dataset
DSNP007I 14 <- Extend failed
DSNP009I 2
DSNP010I 2
DSNP016I 2 <- Create failed
DSNR035I 1 <- Uncommitted UR after X checkpoints
DSNT375I 1 <- Deadlock
DSNT376I 5 <- Timeout
DSNT500I 3
DSNT501I 33
DSNT736I 1 <- Asynch stop database command
DSNU241I 1
DSNU971I 7 <- Table is in check pending
DSNU973I 7 <- Table is no longer in check pending
DSN3201I 133
DSNE610I NUMBER OF ROWS DISPLAYED IS 43A nice little mix don’t you think?
Please remember that this is my sandbox so loads of errors are perfectly normal, and actually desired, for test purposes! Then of course even the “boring” J ones are actually quite handy as it tells you how often you get a Full Log and a Log Offload.
IFCID_TIMESTAMP DB2_MESSAGE_ID DB2_MESSAGE_TEXT ---------+---------+---------+---------+---------+---------+---------+----- 2018-08-20-06.31.30.216749 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-06.31.30.221125 DSNJ001I -DC10 DSNJW307 CURRENT COPY 1 A 2018-08-20-06.31.30.225300 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-06.31.30.228824 DSNJ001I -DC10 DSNJW307 CURRENT COPY 2 A 2018-08-20-06.31.31.877862 DSNJ003I -DC10 DSNJOFF3 FULL ARCHIVE LOG 2018-08-20-06.31.31.903315 DSNJ139I -DC10 LOG OFFLOAD TASK ENDED. 2018-08-20-10.38.48.051394 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-10.38.48.056692 DSNJ001I -DC10 DSNJW307 CURRENT COPY 1 A 2018-08-20-10.38.48.060583 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-10.38.48.065864 DSNJ001I -DC10 DSNJW307 CURRENT COPY 2 A 2018-08-20-10.38.49.806783 DSNJ003I -DC10 DSNJOFF3 FULL ARCHIVE LOG 2018-08-20-10.38.49.840157 DSNJ139I -DC10 LOG OFFLOAD TASK ENDED. 2018-08-20-10.38.57.778588 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-10.38.57.782337 DSNJ001I -DC10 DSNJW307 CURRENT COPY 1 A 2018-08-20-10.38.57.786754 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-10.38.57.790629 DSNJ001I -DC10 DSNJW307 CURRENT COPY 2 A 2018-08-20-10.38.59.211112 DSNJ003I -DC10 DSNJOFF3 FULL ARCHIVE LOG 2018-08-20-10.38.59.238033 DSNJ139I -DC10 LOG OFFLOAD TASK ENDED. 2018-08-20-10.39.07.774517 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-10.39.07.779967 DSNJ001I -DC10 DSNJW307 CURRENT COPY 1 A 2018-08-20-10.39.07.783267 DSNJ002I -DC10 FULL ACTIVE LOG DATA SET 2018-08-20-10.39.07.787409 DSNJ001I -DC10 DSNJW307 CURRENT COPY 2 A 2018-08-20-10.39.09.283591 DSNJ003I -DC10 DSNJOFF3 FULL ARCHIVE LOG 2018-08-20-10.39.09.303103 DSNJ139I -DC10 LOG OFFLOAD TASK ENDED.
Here you can see the day started OK but then it got pretty busy around 10:38!
All-in-all a wonderful extra tool available for the DBA to use on a daily basis. Just check for the bad guy messages that I hope you never have!
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect