Friday, October 18, 2013

Friday DB2 Quiz: Not much to report...

It is Friday. How about a quick quiz? What did I do to produce the following output in DB2? Don't comment on the (in)activities you see... :)



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

  TEXT                                                                           
  --------------------------------------------------------------------------------
  --------------------------------------------------------------------------------
  Monitoring report - database summary                                           
  --------------------------------------------------------------------------------
  Database:                                 HL                                   
  Generated:                                10/18/2013 09:43:38                  
  Interval monitored:                       10                                   
                                                                                 
  ================================================================================
  Part 1 - System performance                                                    
                                                                                 
  Work volume and throughput                                                     
  --------------------------------------------------------------------------------
                                    Per second             Total                 
                                    ---------------------  -----------------------
  TOTAL_APP_COMMITS                 0                      0                     
  ACT_COMPLETED_TOTAL               24                     246                   
  APP_RQSTS_COMPLETED_TOTAL         0                      3                     
                                                                                 
  TOTAL_CPU_TIME                    = 25439                                      
  TOTAL_CPU_TIME per request        = 8479                                       
                                                                                 
  Row processing                                                                 
    ROWS_READ/ROWS_RETURNED         = 0 (40/246)                                 
    ROWS_MODIFIED                   = 0                                          
                                                                                 
  Wait times                                                                     
  --------------------------------------------------------------------------------
                                                                                 
  -- Wait time as a percentage of elapsed time --                                
                                                                                 
                                           %    Wait time/Total time             
                                           ---  ----------------------------------
  For requests                             2    260/10659                        
  For activities                           2    221/10512                        
                                                                                 
  -- Time waiting for next client request --                                     
                                                                                 
  CLIENT_IDLE_WAIT_TIME               = 4665                                     
  CLIENT_IDLE_WAIT_TIME per second    = 466                                      
                                                                                 
  -- Detailed breakdown of TOTAL_WAIT_TIME --                                    
                                                                                 
                                %    Total                                       
                                ---  ---------------------------------------------
  TOTAL_WAIT_TIME               100  260                                         
                                                                                 
  I/O wait time                                                                  
    POOL_READ_TIME              0    0                                           
    POOL_WRITE_TIME             0    0                                           
    DIRECT_READ_TIME            80   210                                         
    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               10399                    
                                                                                 
  Section execution                                                              
    TOTAL_SECTION_PROC_TIME           2                 220                      
      TOTAL_SECTION_SORT_PROC_TIME    0                 0                        
  Compile                                                                        
    TOTAL_COMPILE_PROC_TIME           1                 109                      
    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            Reads (Logical/Physical)                     
  ---------------  ---------------  ----------------------------------------------
  Data             100              29/0                                         
  Index            100              28/0                                         
  XDA              0                0/0                                          
  COL              0                0/0                                          
  Temp data        0                0/0                                          
  Temp index       0                0/0                                          
  Temp XDA         0                0/0                                          
  Temp COL         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                                    
  LBP Data         100              (29 - 0)/(29 + 0)                            
  LBP Index        100              (28 - 0)/(28 + 0)                            
  LBP XDA          0                (0 - 0)/(0 + 0)                              
  LBP COL          0                (0 - 0)/(0 + 0)                              
                                                                                 
  I/O                                                                            
  --------------------------------------------------------------------------------
  Buffer pool writes                                                             
    POOL_DATA_WRITES      = 0                                                    
    POOL_XDA_WRITES       = 0                                                    
    POOL_INDEX_WRITES     = 0                                                    
    POOL_COL_WRITES       = 0                                                    
  Direct I/O                                                                     
    DIRECT_READS          = 802                                                  
    DIRECT_READ_REQS      = 32                                                   
    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     0                         237                    
  TOTAL_ROUTINE_TIME            41                        10294                  
                                                                                 
  TOTAL_ROUTINE_TIME per invocation   = 43                                       
                                                                                 
  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            = 94         (189/2)                       
  IPC_RECV_VOLUME per receive         = 160        (482/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                 = 16                                       
    PKG_CACHE_LOOKUPS                 = 17                                       
  Catalog cache                                                                  
    CAT_CACHE_INSERTS                 = 3                                        
    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              
  ----------------------  -----------  -------------  ----------------------------
  8479                    2            0              40                         
                                                                                 
  Application performance by connection                                          
  --------------------------------------------------------------------------------
  APPLICATION_   TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ + 
  HANDLE         per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED
  -------------  -------------------   -----------    -------------  -------------
  804            8479                  2              0              40          
                                                                                 
  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             8479                  2              0              40          
                                                                                 
  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  8479                    2            0              40          
                                                                                 
  ================================================================================
  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       8479                    2            3                 261             
                                                                                 

  241 record(s) selected.

  Return Status = 0

6 comments:

raf mathijs said...

call monreport.dbsummary(10)

Henrik Loeser said...

Hi Raf,

you win. I actually executed "call monreport.dbsummary". 10 is the default, so you are correct. Congratulations!

Enjoy the weekend,
Henrik

Henrik Loeser said...

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0056370.html is the link to the description of the reporting procedure.

raf mathijs said...

Henrik,

what did I win ? ;)

a nice weekend for you as well,

kind regards,

Raf

Henrik Loeser said...

It's all about honor and standing out... ;-)

Henrik

Bobby Proffitt said...

monreport.dbsummary

LinkWithin

Related Posts with Thumbnails