DB2 10.5 for Linux, UNIX, and Windows

DBSUMMARY procedure - Generate a summary report of system and application performance metrics

The DBSUMMARY procedure generates a text-formatted monitoring report that summarizes system and application performance metrics.

The DB Summary report contains in-depth monitor data for the entire database as well as key performance indicators for each connection, workload, service class, and database member.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MONREPORT.DBSUMMARY--(--monitoring_interval--)--------------><

Parameters

monitoring_interval
An optional input argument of type INTEGER that specifies the duration in seconds that monitoring data is collected before it is reported. For example, if you specify a monitoring interval of 30, the routine calls the table functions, waits 30 seconds and then calls the table functions again. The DBSUMMARY procedure then calculates the difference, which reflects changes during the interval. If the monitoring_interval argument is not specified (or if null is specified), the default value is 10. The range of valid inputs are the integer values 0-3600 (that is, up to 1 hour).

Authorization

The following privilege is required:
  • EXECUTE privilege on the MONREPORT module

Examples

The following examples demonstrate various ways to call the DBSUMMARY procedure.

The first example produces a report that displays data corresponding to an interval of 30 seconds.
call monreport.dbsummary(30);
The next example produces a report that displays data corresponding to an interval of 10 seconds (the default value):
call monreport.dbsummary; 
This procedure call returns the following output:
  Result set 1
  --------------

  TEXT                                                                            
  --------------------------------------------------------------------------------
  -------------------------------------------------------------------------------- 
  Monitoring report - database summary                                             
  -------------------------------------------------------------------------------- 
  Database:                                 SAMPLE                                 
  Generated:                                04/06/2010 13:35:24                    
  Interval monitored:                       10                                     
                                                                                   
  ================================================================================ 
  Part 1 - System performance                                                      
                                                                                   
  Work volume and throughput                                                       
  -------------------------------------------------------------------------------- 
                                    Per second             Total                   
                                    ---------------------  ----------------------- 
  TOTAL_APP_COMMITS                 0                      2                       
  ACT_COMPLETED_TOTAL               0                      9                       
  APP_RQSTS_COMPLETED_TOTAL         0                      6                       
                                                                                   
  TOTAL_CPU_TIME                    = 2649800                                      
  TOTAL_CPU_TIME per request        = 441633                                       
                                                                                   
  Row processing                                                                   
    ROWS_READ/ROWS_RETURNED         = 97 (685/7)                                   
    ROWS_MODIFIED                   = 117                                          
                                                                                   
  Wait times                                                                       
  -------------------------------------------------------------------------------- 
                                                                                   
  -- Wait time as a percentage of elapsed time --                                  
                                                                                   
                                           %    Wait time/Total time               
                                           ---  ---------------------------------- 
  For requests                             19   3434/17674                         
  For activities                           10   1203/11613                         
                                                                                   
  -- Time waiting for next client request --                                       
                                                                                   
  CLIENT_IDLE_WAIT_TIME               = 70566                                      
  CLIENT_IDLE_WAIT_TIME per second    = 7056                                       
                                                                                   
  -- Detailed breakdown of TOTAL_WAIT_TIME --                                      
                                                                                   
                                %    Total                                         
                                ---  --------------------------------------------- 
  TOTAL_WAIT_TIME               100  3434                                          
                                                                                   
  I/O wait time                                                                    
    POOL_READ_TIME              23   805                                           
    POOL_WRITE_TIME             8    280                                           
    DIRECT_READ_TIME            3    131                                           
    DIRECT_WRITE_TIME           3    104                                           
    LOG_DISK_WAIT_TIME          10   344                                           
  LOCK_WAIT_TIME                0    18                                            
  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          6    212                                           
  WLM_QUEUE_TIME_TOTAL          0    0                                             
  CF_WAIT_TIME                  32   1101                                          
  RECLAIM_WAIT_TIME             2    98                                            
  SMP_RECLAIM_WAIT_TIME         3    118                                           
                                                                                   
  Component times                                                                  
  -------------------------------------------------------------------------------- 
  -- Detailed breakdown of processing time --                                      
                                                                                   
                                      %                 Total                      
                                      ----------------  -------------------------- 
  Total processing                    100               14240                      
                                                                                   
  Section execution                                                                
    TOTAL_SECTION_PROC_TIME           2                 365                        
      TOTAL_SECTION_SORT_PROC_TIME    0                 0                          
  Compile                                                                          
    TOTAL_COMPILE_PROC_TIME           0                 17                         
    TOTAL_IMPLICIT_COMPILE_PROC_TIME  2                 294                        
  Transaction end processing                                                       
    TOTAL_COMMIT_PROC_TIME            0                 36                         
    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)/(27+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              (27-0)/(27+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                 = 27                                        
    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         = 27                                        
    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                      = 620                                       
    DIRECT_READ_REQS                  = 15                                        
    DIRECT_WRITES                     = 0                                         
    DIRECT_WRITE_REQS                 = 0                                         
  Log I/O                                                                         
    LOG_DISK_WAITS_TOTAL              = 0
                       
  Locking                                                                          
  -------------------------------------------------------------------------------- 
                          Per activity                    Total                    
                          ------------------------------  ----------------------   
  LOCK_WAIT_TIME          2                               18                       
  LOCK_WAITS              22                              2                        
  LOCK_TIMEOUTS           0                               0                        
  DEADLOCKS               0                               0                        
  LOCK_ESCALS             0                               0                        
                                                                                   
  Routines                                                                         
  -------------------------------------------------------------------------------- 
                                Per activity              Total                    
                                ------------------------  ------------------------ 
  TOTAL_ROUTINE_INVOCATIONS     0                         1                        
  TOTAL_ROUTINE_TIME            1117                      10058                    
                                                                                   
  TOTAL_ROUTINE_TIME per invocation   = 10058                                      
                                                                                   
  Sort                                                                             
  -------------------------------------------------------------------------------- 
  TOTAL_SORTS                         = 5                                          
  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            = 137        (1101/8)                        
  IPC_RECV_VOLUME per receive         = 184        (1106/6)                        
                                                                                   
  Fast communications manager                                                      
  FCM_SEND_VOLUME per send            = 3475       (31277/9)                       
  FCM_RECV_VOLUME per receive         = 2433       (131409/54)                     
                                                                                   
  Other                                                                            
  -------------------------------------------------------------------------------- 
  Compilation                                                                      
    TOTAL_COMPILATIONS                = 4                                          
    PKG_CACHE_INSERTS                 = 11                                         
    PKG_CACHE_LOOKUPS                 = 13                                         
  Catalog cache                                                                    
    CAT_CACHE_INSERTS                 = 74                                         
    CAT_CACHE_LOOKUPS                 = 112                                        
  Transaction processing                                                           
    TOTAL_APP_COMMITS                 = 2                                          
    INT_COMMITS                       = 2                                          
    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                
  ----------------------  -----------  -------------  ---------------------------- 
  441633                  19           2              802                          
                                                                                   
  Application performance by connection                                            
  -------------------------------------------------------------------------------- 
  APPLICATION_   TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +   
  HANDLE         per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED 
  -------------  -------------------   -----------    -------------  ------------- 
  180            0                     0              0              0             
  65711          495970                46             1              566           
  131323         324379                43             1              222           
                                                                                   
  Application performance by service class                                         
  -------------------------------------------------------------------------------- 
  SERVICE_       TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +   
  CLASS_ID       per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED 
  --------       -------------------   -----------    -------------  ------------- 
  11             0                     0              0              0             
  12             0                     0              0              0             
  13             440427                19             2              802           
                                                                                   
  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  410174                  45           2              788           
                                                                                   
  ================================================================================ 
  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       17804                   0            9                 10                
  1       108455                  47           14                866               
  2       74762                   41           13                441               
                                                                                   

  267 record(s) selected.

  Return Status = 0