DB2 Basics: MONREPORT

If you’re mourning the loss of “RESET MONITOR ALL”, there are a number of ways you can address that. Ideally, you’d use this approach: http://www.ibm.com/developerworks/data/library/techarticle/dm-1009db2monitoring1/, but there are some arguments for using MONREPORT over that in some ways. One argument is that MONREPORT is built-in and you don’t have to add objects to make it work. The disadvantages are a bit less flexibility and the inability to use SQL to capture information. Though if it’s the old snapshot interfaces you miss, then the output from MONREPORT is more similar in a lot of ways.

I talk as though the snapshot monitors no longer exist – they do, but he lower overhead of more recent monitoring tools is worth leaving them behind.

What is MONREPORT

MONREPORT is a built-in module for DB2 LUW, starting in version 9.7. (A module is a group of related stored procedures and other objects) Most of the MONREPORT procedures take an input parameter of a number of seconds. They then take note of the values for a number of performance related metrics and sleep for the specified number of seconds. Then the metrics are captured again, and the before values are subtracted from the after values to show only the numbers represented by what happened in the specified time interval. An easily human-readable report is then generated showing the metrics during the specified interval.

This is useful because simply querying the MON_GET table functions and SYSIBMADM views/tables will get you just the values since the last database restart. Sometimes you want to do analyses based on everything that has happened since the last database restart. Other times you really need to know what’s happening in a particular 5 minute or even one-hour interval.

MONREPORT is generally focused on database performance, though in several areas. In DB2 10.5, the following stored procedures can be called to get informaion from MONREPORT:

  • DBSUMMARY – provides values for key performance indicators across the database. Optional input is simply the monitoring interval. See the IBM DB2 Knowledge Center entry on DBSUMMARY for more details.
  • CONNECTION – provides values for preformance indicators specific to each connection. Input is the monitoring interval and optionally the application handle of a single connection if you want information only returned for one connection. See the IBM DB2 Knowledge Center entry on CONNECTION for more details.
  • CURRENTAPPS – provides point-in-time values for performance indicators for applications at the moment it is run. No inputs. See the IBM DB2 Knowledge Center entry on CURRENTAPPS for more details.
  • CURRENTSQL – provides details on current SQL executing – including it’s own SQL. Optional input of the member is only valid in a PureScale environment. See the IBM DB2 Knowledge Center entry on CURRENTSQL for more details.
  • LOCKWAIT – provides details on lockwaits currently in progress. No inputs. See the IBM DB2 Knowledge Center entry on LOCKWAIT for more details.
  • PKGCACHE – provides details on statements in the package cache. Note this is not my favorite way of accessing package cache information (MON_GET_PKG_CACHE_STMT is). Inputs are optional and include: an interval in which the statements in the package cache have been updated, a section type if you want to limit data to either dynamic or static SQL, and a member if you’re using PureScale. See the IBM DB2 Knowledge Center entry on PKGCACHE for more details.

How to use MONREPORT

I’m going to cover the use of DBSUMMARY. The others are similar. To use an interval of 60 seconds, use this syntax:

db2 "CALL MONREPORT.DBSUMMARY(60)"

If you think it seems to be hanging, make sure you specified the correct interval. For all of the MONREPORT procedures that take an interval, except for the PKGCACHE one, it will take a bit more than the interval you’ve specified to return the metrics.

This is a sample of the output:

$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

$ db2 "call monreport.dbsummary(60)"


  Result set 1
  --------------

  TEXT                                                                            
  --------------------------------------------------------------------------------
  --------------------------------------------------------------------------------
  Monitoring report - database summary                                            
  --------------------------------------------------------------------------------
  Database:                                 SAMPLE                                
  Generated:                                11/24/2014 19:10:03                   
  Interval monitored:                       60                                    
                                                                                  
  ================================================================================
  Part 1 - System performance                                                     
                                                                                  
  Work volume and throughput                                                      
  --------------------------------------------------------------------------------
                                    Per second             Total                  
                                    ---------------------  -----------------------
  TOTAL_APP_COMMITS                 0                      0                      
  ACT_COMPLETED_TOTAL               4                      271                    
  APP_RQSTS_COMPLETED_TOTAL         0                      5                      
                                                                                  
  TOTAL_CPU_TIME                    = 48751                                       
  TOTAL_CPU_TIME per request        = 9750                                        
                                                                                  
  Row processing                                                                  
    ROWS_READ/ROWS_RETURNED         = 0 (73/271)                                  
    ROWS_MODIFIED                   = 0                                           
                                                                                  
  Wait times                                                                      
  --------------------------------------------------------------------------------
                                                                                  
  -- Wait time as a percentage of elapsed time --                                 
                                                                                  
                                           %    Wait time/Total time              
                                           ---  ----------------------------------
  For requests                             0    137/60508                         
  For activities                           0    168/60532                         
                                                                                  
  -- Time waiting for next client request --                                      
                                                                                  
  CLIENT_IDLE_WAIT_TIME               = 1944                                      
  CLIENT_IDLE_WAIT_TIME per second    = 32                                        
                                                                                  
  -- Detailed breakdown of TOTAL_WAIT_TIME --                                     
                                                                                  
                                %    Total                                        
                                ---  ---------------------------------------------
  TOTAL_WAIT_TIME               100  137                                          
                                                                                  
  I/O wait time                                                                   
    POOL_READ_TIME              0    0                                            
    POOL_WRITE_TIME             0    0                                            
    DIRECT_READ_TIME            43   59                                           
    DIRECT_WRITE_TIME           0    0                                            
    LOG_DISK_WAIT_TIME          0    0                                            
  LOCK_WAIT_TIME                0    0                                            
  AGENT_WAIT_TIME               0    0                                            
  Network and FCM                                                                 
    TCPIP_SEND_WAIT_TIME        0    0                                            
    TCPIP_RECV_WAIT_TIME        0    0                                            
    IPC_SEND_WAIT_TIME          0    0                                            
    IPC_RECV_WAIT_TIME          0    0                                            
    FCM_SEND_WAIT_TIME          0    0                                            
    FCM_RECV_WAIT_TIME          0    0                                            
  WLM_QUEUE_TIME_TOTAL          0    0                                            
  CF_WAIT_TIME                  0    0                                            
  RECLAIM_WAIT_TIME             0    0                                            
  SMP_RECLAIM_WAIT_TIME         0    0                                            
                                                                                  
  Component times                                                                 
  --------------------------------------------------------------------------------
  -- Detailed breakdown of processing time --                                     
                                                                                  
                                      %                 Total                     
                                      ----------------  --------------------------
  Total processing                    100               60371                     
                                                                                  
  Section execution                                                               
    TOTAL_SECTION_PROC_TIME           0                 263                       
      TOTAL_SECTION_SORT_PROC_TIME    0                 9                         
  Compile                                                                         
    TOTAL_COMPILE_PROC_TIME           0                 3                         
    TOTAL_IMPLICIT_COMPILE_PROC_TIME  0                 0                         
  Transaction end processing                                                      
    TOTAL_COMMIT_PROC_TIME            0                 0                         
    TOTAL_ROLLBACK_PROC_TIME          0                 0                         
  Utilities                                                                       
    TOTAL_RUNSTATS_PROC_TIME          0                 0                         
    TOTAL_REORGS_PROC_TIME            0                 0                         
    TOTAL_LOAD_PROC_TIME              0                 0                         
                                                                                  
  Buffer pool                                                                     
  --------------------------------------------------------------------------------
  Buffer pool hit ratios                                                          
                                                                                  
  Type             Ratio            Formula                                       
  ---------------  ---------------  ----------------------------------------------
  Data             100              (1-(0+0-0)/(28+0))                            
  Index            100              (1-(0+0-0)/(24+0))                            
  XDA              0                (1-(0+0-0)/(0+0))                             
  COL              0                (1-(0+0-0)/(0+0))                             
  LBP Data         100              (28-0)/(28+0)                                 
  LBP Index        0                (0-0)/(24+0)                                  
  LBP XDA          0                (0-0)/(0+0)                                   
  LBP COL          0                (0-0)/(0+0)                                   
  GBP Data         0                (0 - 0)/0                                     
  GBP Index        0                (0 - 0)/0                                     
  GBP XDA          0                (0 - 0)/0                                     
  GBP COL          0                (0 - 0)/0                                     
                                                                                  
  I/O                                                                             
  --------------------------------------------------------------------------------
  Buffer pool reads                                                               
    POOL_DATA_L_READS                 = 28                                        
    POOL_TEMP_DATA_L_READS            = 0                                         
    POOL_DATA_P_READS                 = 0                                         
    POOL_TEMP_DATA_P_READS            = 0                                         
    POOL_ASYNC_DATA_READS             = 0                                         
    POOL_INDEX_L_READS                = 24                                        
    POOL_TEMP_INDEX_L_READS           = 0                                         
    POOL_INDEX_P_READS                = 0                                         
    POOL_TEMP_INDEX_P_READS           = 0                                         
    POOL_ASYNC_INDEX_READS            = 0                                         
    POOL_XDA_L_READS                  = 0                                         
    POOL_TEMP_XDA_L_READS             = 0                                         
    POOL_XDA_P_READS                  = 0                                         
    POOL_TEMP_XDA_P_READS             = 0                                         
    POOL_ASYNC_XDA_READS              = 0                                         
    POOL_COL_L_READS                  = 0                                         
    POOL_TEMP_COL_L_READS             = 0                                         
    POOL_COL_P_READS                  = 0                                         
    POOL_TEMP_COL_P_READS             = 0                                         
    POOL_ASYNC_COL_READS              = 0                                         
  Buffer pool pages found                                                         
    POOL_DATA_LBP_PAGES_FOUND         = 28                                        
    POOL_ASYNC_DATA_LBP_PAGES_FOUND   = 0                                         
    POOL_INDEX_LBP_PAGES_FOUND        = 0                                         
    POOL_ASYNC_INDEX_LBP_PAGES_FOUND  = 0                                         
    POOL_XDA_LBP_PAGES_FOUND          = 0                                         
    POOL_ASYNC_XDA_LBP_PAGES_FOUND    = 0                                         
    POOL_COL_LBP_PAGES_FOUND          = 0                                         
    POOL_ASYNC_COL_LBP_PAGES_FOUND    = 0                                         
  Buffer pool writes                                                              
    POOL_DATA_WRITES                  = 0                                         
    POOL_XDA_WRITES                   = 0                                         
    POOL_INDEX_WRITES                 = 0                                         
    POOL_COL_WRITES                   = 0                                         
  Direct I/O                                                                      
    DIRECT_READS                      = 592                                       
    DIRECT_READ_REQS                  = 16                                        
    DIRECT_WRITES                     = 0                                         
    DIRECT_WRITE_REQS                 = 0                                         
  Log I/O                                                                         
    LOG_DISK_WAITS_TOTAL              = 0                                         
                                                                                  
  Locking                                                                         
  --------------------------------------------------------------------------------
                          Per activity                    Total                   
                          ------------------------------  ----------------------  
  LOCK_WAIT_TIME          0                               0                       
  LOCK_WAITS              0                               0                       
  LOCK_TIMEOUTS           0                               0                       
  DEADLOCKS               0                               0                       
  LOCK_ESCALS             0                               0                       
                                                                                  
  Routines                                                                        
  --------------------------------------------------------------------------------
                                Per activity              Total                   
                                ------------------------  ------------------------
  TOTAL_ROUTINE_INVOCATIONS     1                         290                     
  TOTAL_ROUTINE_TIME            222                       60271                   
                                                                                  
  TOTAL_ROUTINE_TIME per invocation   = 207                                       
                                                                                  
  Sort                                                                            
  --------------------------------------------------------------------------------
  TOTAL_SORTS                         = 4                                         
  SORT_OVERFLOWS                      = 0                                         
  POST_THRESHOLD_SORTS                = 0                                         
  POST_SHRTHRESHOLD_SORTS             = 0                                         
                                                                                  
  Network                                                                         
  --------------------------------------------------------------------------------
  Communications with remote clients                                              
  TCPIP_SEND_VOLUME per send          = 0          (0/0)                          
  TCPIP_RECV_VOLUME per receive       = 0          (0/0)                          
                                                                                  
  Communications with local clients                                               
  IPC_SEND_VOLUME per send            = 182        (365/2)                        
  IPC_RECV_VOLUME per receive         = 231        (694/3)                        
                                                                                  
  Fast communications manager                                                     
  FCM_SEND_VOLUME per send            = 0          (0/0)                          
  FCM_RECV_VOLUME per receive         = 0          (0/0)                          
                                                                                  
  Other                                                                           
  --------------------------------------------------------------------------------
  Compilation                                                                     
    TOTAL_COMPILATIONS                = 2                                         
    PKG_CACHE_INSERTS                 = 15                                        
    PKG_CACHE_LOOKUPS                 = 16                                        
  Catalog cache                                                                   
    CAT_CACHE_INSERTS                 = 5                                         
    CAT_CACHE_LOOKUPS                 = 9                                         
  Transaction processing                                                          
    TOTAL_APP_COMMITS                 = 0                                         
    INT_COMMITS                       = 0                                         
    TOTAL_APP_ROLLBACKS               = 0                                         
    INT_ROLLBACKS                     = 0                                         
  Log buffer                                                                      
    NUM_LOG_BUFFER_FULL               = 0                                         
  Activities aborted/rejected                                                     
    ACT_ABORTED_TOTAL                 = 0                                         
    ACT_REJECTED_TOTAL                = 0                                         
  Workload management controls                                                    
    WLM_QUEUE_ASSIGNMENTS_TOTAL       = 0                                         
    WLM_QUEUE_TIME_TOTAL              = 0                                         
                                                                                  
  DB2 utility operations                                                          
  --------------------------------------------------------------------------------
    TOTAL_RUNSTATS                    = 0                                         
    TOTAL_REORGS                      = 0                                         
    TOTAL_LOADS                       = 0                                         
                                                                                  
  ================================================================================
  Part 2 - Application performance drill down                                     
                                                                                  
  Application performance database-wide                                           
  --------------------------------------------------------------------------------
  TOTAL_CPU_TIME          TOTAL_       TOTAL_APP_     ROWS_READ +                 
  per request             WAIT_TIME %  COMMITS        ROWS_MODIFIED               
  ----------------------  -----------  -------------  ----------------------------
  9750                    0            0              73                          
                                                                                  
  Application performance by connection                                           
  --------------------------------------------------------------------------------
  APPLICATION_   TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +  
  HANDLE         per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED
  -------------  -------------------   -----------    -------------  -------------
  74             9750                  0              0              73           
                                                                                  
  Application performance by service class                                        
  --------------------------------------------------------------------------------
  SERVICE_       TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +  
  CLASS_ID       per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED
  --------       -------------------   -----------    -------------  -------------
  4              0                     0              0              0            
  11             0                     0              0              0            
  12             0                     0              0              0            
  13             9750                  0              0              73           
                                                                                  
  Application performance by workload                                             
  --------------------------------------------------------------------------------
  WORKLOAD_      TOTAL_CPU_TIME          TOTAL_       TOTAL_APP_     ROWS_READ +  
  NAME           per request             WAIT_TIME %  COMMITS        ROWS_MODIFIED
  -------------  ----------------------  -----------  -------------  -------------
  SYSDEFAULTADM  0                       0            0              0            
  SYSDEFAULTUSE  9750                    0            0              73           
                                                                                  
  ================================================================================
  Part 3 - Member level information                                               
                                                                                  
  - I/O wait time is                                                              
    (POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME).    
                                                                                  
          TOTAL_CPU_TIME          TOTAL_       RQSTS_COMPLETED_  I/O              
  MEMBER  per request             WAIT_TIME %  TOTAL             wait time        
  ------  ----------------------  -----------  ----------------  -----------------
  0       9750                    0            5                 279              
                                                                                  

  267 record(s) selected.

  Return Status = 0

I am not covering the output in detail here, because explaining it would be very much more in-depth than I want to get. Sample output for most of the procedures is available at the IBM DB2 Knowledge Center links supplied above.

Impact

While nearly all monitoring has some small impact, it’s on the lower side here since the stored procedures are mostly using the lowest available impacting monitoring interfaces of the MON_GET table functions. There must be some overhead in display and simple subtraction, but it must be less invasive than the old snapshot monitoring interfaces.

You may also like...

1 Response

  1. December 4, 2014

    […] you’re not familiar with MONREPORT, see my DB2 Basics entry on MONREPORT […]

Leave a Reply

Your email address will not be published. Required fields are marked *