Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Apptune for Db2 13.1.

Monitoring SQL statement cache entries

Use this procedure to view a series of reports that monitor performance metrics from SQL statement cache entries.

Warning

Note

For the dynamic statement cache statistics to be available, DB2 ZPARM must be set to CACHEDYN=YES and IFCID 318 must be active in a trace.

For static statement cache statistics to be available, IFCID 400 must be active in a trace.

You can start these IFCIDs automatically by using DOMPLEX option set parameters. Alternatively, you can turn these IFCIDs ON or OFF from these reports.

To monitor SQL statement cache entries

  1. On the Apptune Main Menu, select Statement Cache and press Enter.The Logical Db2 Cache Analysis report is displayed. 

    Logical Db2 Cache Analysis report 

    ASQEQRPW/I                      View a Report                      LINE 1 OF 6
    Command ====> _______________________________________________ Scroll ===> CSR_
                                                                                  
    BMCSftwr.SQMZACTL    --   LOGICAL DB2 CACHE ANALYSIS   --       10/14 07:01:47
    Source : C112-ACTIVE   Intvl : 10/14 07:01 - UNLIMITED          More:       >  
    -------------------------------------------------------------------------------
       Zooms:   B-Subsystem  M-Monitor  R-Program  U-User  C-CollID  S-Statement   
       Expands: D-Dynamic/Static  T-Detail                                         
                                                                                  
       Logical  DSGrp  SQL             +----- Elapsed -----+ +------- CPU -------+
       DB2 Name Trc St Calls  Getpages   Total     Per Call    Total     Per Call  
       -------- ------ ------ -------- ---------- ---------- ---------- ----------
    +  DSNDIY   YES        32      229 00:37.7956 00:01.1811 00:00.0136 00:00.0004
    -   DYNAMIC AUTO       28      222 00:00.5004 00:00.0179 00:00.0117 00:00.0004
    -   STATIC  AUTO        4        7 00:37.2952 00:09.3238 00:00.0019 00:00.0005
    +  DSNDJY   YES        85      562 00:02.9552 00:00.0348 00:00.1595 00:00.0019
    -   DYNAMIC AUTO       37      527 00:02.3994 00:00.0648 00:00.0287 00:00.0008
    -   STATIC  AUTO       48       35 00:00.5557 00:00.0116 00:00.1309 00:00.0027
  2. For SQL statements currently in the Db2 EDM pool, use the action codes to produce reports sorted by:
    • Db2 subsystem
    • Program
    • User
    • Collection ID
    • Statement level
    • Accelerator

  3. Type M over the + (plus sign), and press Enter to display SQL statement cache activity changes from one report refresh to the next refresh.The Cache Monitor report is displayed. 

    Cache Monitor report

    ASQEQRPW/I                      View a Report                      LINE 1 OF 4
    Command ====> _______________________________________________ Scroll ===> CSR_

    BMCSftwr.SQMZACTM    --         CACHE MONITOR          --       11/04 13:58:23
    Source : Q62M-ACTIVE   Intvl : 11/04 13:44 - UNLIMITED          More:       >
    -----------------------------------------------------------------------------
       Hit Enter to refresh with current cache statistics.
       Actions: T-Detail  S-Full SQL Text

       Subsys: DHQ6 Program:          User:

       Activity from 13:55:50.379636 to 13:58:22.648560
                                                                  +- Binding --+   SQL             Elapsed      CPU     +--- Wait ----+
       Text Preview                                              Program   Stmt  Calls Getpages    Time       Time    Time        Pct
       --------------------------------------------------------- -------------- ------ --------- ---------- ---------- ---------------

    +  SELECT * FROM AFD.TBBP00                                  DSNTEP2   1636      1         4 00:00.0110 00:00.0004 00:00.0101 91.7
    +  SELECT * FROM AFD.TBBP01                                  DSNTEP2   1636      1         4 00:00.0041 00:00.0002 00:00.0000  0.0
    +  SELECT * FROM AFD.TBBP02                                  DSNTEP2   1636      1         0 00:00.0024 00:00.0002 00:00.0000  0.0
    +  SELECT SERIAL_NUMBER, SLLNG_DSTRBTR_CD, ISSUING_DSTRBTR_C DSNTEP2   1638      1         2 00:00.0003 00:00.0002 00:00.0000  0.0
  4. Type S over the + (plus sign) to display the full SQL text.

Related topic

Workload-analysis-tasks



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Apptune for Db2 12.1