2018-09 Db2 Console output : Message in a bottle

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 1

Get 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                       <- DDF is abnormally ending           
DSNL008I                  2            
DSNL012I                  2            
DSNL030I                 12            
DSNL500I                  1            
DSNL510I                 31            
DSNL511I                  2            
DSNL512I                 12            
DSNL519I                 15            
DSNL523I                 10            
DSNP002I                        <- 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 43

A 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