Apptune for DB2: Comparing Apptune and MainView for DB2 (SMF) data

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    APPTUNE for DB2


    COMPONENT:

    APPTUNE for DB2


    APPLIES TO:

    SQL Performance for DB2, MainView for DB2, BMC SQL Performance for DB2



    QUESTION:

    Why does Apptune and MVDB2 numbers not match for a given interval?


    ANSWER:

     

    Apptune and MVDB2 numbers may not match for the reasons stated below:-

                                                                                                              
    ApptuneMVDB2

    Apptune reports times on SQL level. It is called "IN-SQL" times

    MVDB2 reports times from DB2 accounting records on thread level. It is called "IN-DB2" times.

    Apptune workload intervals are based on SQL statistics collection intervals.

    DB2 accounting records are written when a thread terminates, when authorization ID changes, and when using rollup accounting (ACCUMAC>1).

    Apptune accumulates activity and externalized at the end of the each SQL execution.

    DB2 accumulates activity and externalized when an accounting record is written. 
    IN-SQL CPU time includes zIIP times.zIIP times are reported separately.
      
    Compare the Apptune report (1) to MVDB2 table output (2).  
                           
      

    (1) SQMCACTP report for DSNTIAUL interval 10/26 10:00 - 10:59

    ASQEQRPW/I                      VIEW A REPORT                      LINE 1 OF 1 
    COMMAND ====> _______________________________________________ SCROLL ===> CSR_ 
                                                                                   
    BMCSftwr.SQMCACTP    --      PLAN ANALYSIS (DATA)      --       
    SOURCE : DC01-ACTIVE   INTVL : 10/26 10:00 - 10:59                  MORE:       >  
    -------------------------------------------------------------------------------
       Zooms:   G-APPGRP I-CLNTID L-CORR  T-DETL   K-IMPQUL R-PROG   U-USER        
       Y-ACCEL  N-CLNTAP W-CLNTWS D-DB2   E-ERROR  V-INTVL  J-REQLOC               
       M-ACCMTX 9-CLNTCT C-CONN   B-DSGRP X-EXCPTN O-OBJECT S-STMT                 
       Expands: A-AVHILO          Z-ZIIP  1-MORE   2-ACCEL                         
       DB2: DB01 DSGRP:                            PROG:                           
                 SQL    +----- TOTAL IN-SQL TIME -----+         
       PLAN      CALLS  ELAPSED       % CPU           % GETPAGES
       --------  ------ --------------- --------------- --------
    +  DSNTIAUL      15 00:48.31261 100 00:25.57275 100      896

      

    (2) DMRACDTL - GROUPBY DSNTIAUL for 10/26 Hour 10

       +---------------------------------------------------------------------+
       |  PLANNAME  |    DATE    | HOUR | CL2 ELP TM | CL2 CPU TM | GETPAGES |
       +---------------------------------------------------------------------+
       | DSNTIAUL    | 10/26/2016| 10   | 29.122234  |  16.195599 |      780 |
       +---------------------------------------------------------------------+


    The reasons why elapsed time and CPU times do not match are listed above. The GETPAGES do not match also. The reason is because at the end of the Apptune interval, SQL activity from threads that are still in progress (in this e.g., the difference of 116 GETPAGES) are reported. However, in DB2, this SQL activity will not be written until the accounting record is written. This can be validated by looking at the statements in Apptune and the accounting records in DMRACDTL.

    ASQEQRPW/I                      VIEW A REPORT                      LINE 1 OF 2 
    COMMAND ====> _______________________________________________ SCROLL ===> CSR_ 
                                                                                   
      

    BMCSftwr.SQMCACTX    -- SQL STATEMENT ANALYSIS (DATA)  --      
    SOURCE : DC01-ACTIVE   INTVL : 10/26 10:00 - 10:59                MORE:       >  
    -------------------------------------------------------------------------------
       Zooms:   Q-CATSQL I-CLNTID L-CORR  T-DETL   K-IMPQUL P-PLAN   S-SQLTXT      
       M-ACCMTX N-CLNTAP W-CLNTWS D-DB2   E-ERROR  V-INTVL  R-RECMND U-USER        
       G-APPGRP 9-CLNTCT C-CONN   B-DSGRP X-EXCPTN O-OBJECT J-REQLOC               
       Expands: A-AVHILO F-FULL   H-HDR   Z-TIMES  1-MORE   2-ACCEL                
       DB2: DB01 DSGRP:           PLAN:                                            
                DYN/STAT   SECT STMT   SQL   +----- TOTAL IN-SQL TIME -----+ GET-  
       PROGRAM  STMT TYPE   NO.  NO.   CALLS ELAPSED       %  CPU          %  PAGES
       -------- ---------- ---- ------ ----- --------------- --------------- ------
    +  DSNTIAUL D CURSOR      1    189    11 00:29.11086  60 00:16.18844  63    780
    +  DSNTIAUL D CURSOR      1    189     4 00:19.20175  40 00:09.38431  37    116

      

    In this example, the accounting record was written at 11:05 and hence the numbers were not reported in interval 10:00 - 10:59.

      

       +-----------------------------------------------------------------------+
       | PLANNAME |       DATE | TIME     | CL2 ELP TM | CL2 CPU TM | GETPAGES |
       +-----------------------------------------------------------------------+
     1_| DSNTIAUL | 10/26/2016 | 10:30 AM |  29.122234 |  16.195599 |      780 |
     2_| DSNTIAUL | 10/26/2016 | 11:05 AM |  19.208018 |   9.387832 |      116 |
       +-----------------------------------------------------------------------+

      

    For more information, refer to IN-SQL and IN-DB2 time.

     


    Article Number:

    000123520


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles