Dynamic SQL Cache Statistics

This panel provides additional details about the dynamic SQL cache.

The statistical data requires that monitor class 1 and IFCID 318 are started. This might be accomplished by starting the Near Term History collector.

With this panel, you can identify problem statements in the dynamic SQL cache.

One line of output is produced for each unique dynamic SQL statement.

To sort output by display column move the cursor under the display column heading you want and press the sort key (PF10).

________________ ZEDDT    VTM     O2       V550.#P DC11 05/19/16 18:53:20   2  
>  Help PF1    Back PF3     Up PF7     Down PF8     Sort PF10     Zoom PF11     
>                                                                               
> A-BY AUTHID   *-ALL STMTS   C-BY TXID   D-BY WSNM   E-BY EUID                 
=============================================================================== 
>              Dynamic SQL Cache Statement Statistics                           
>     statistics require that monitor class 1 and ifcid 318 be started          
>                                                                               
>   place an 'a' before the EDDT command to get average for times and counts    
>                                                                               
 EDDT                                                                           
+                                                                         *     
+  Times     CPU         Elapsed       Wait      Get-   Sync  Sync              
+  Exec.     Time        Time          Time      Pages  Reads Writes ACEL DPS   
+ ------ ------------ ------------ ------------ ------ ------ ------ ---- ---   
+      4 00:00:00.000 00:00:00.111 00:03:14.729     32      8      0 N    Y     
+      4 00:00:00.000 00:00:00.078 00:01:29.582     24      6      0 N    Y     
+      3 00:00:00.017 00:00:00.000 14-07:15          0      0      0 N    Y     
+      2 00:00:44.894 00:00:00.009 37-13:32         42      0      0 N    Y     
+      1 00:00:00.019 00:00:00.000 60-18:57          0      0      0 N    Y     
+      2 00:00:40.278 00:00:00.010 37-13:32         42      2      0 N    Y     
+      1 00:00:00.000 00:00:00.001 00:00:04.539      9      1      0 N    N     
+      0 00:00:00.000 00:00:00.000 00:00:00.000      0      0      0 N    N     
+      1 00:00:00.000 00:00:00.000 00:00:00.000      1      0      0 N    N     
+      1 00:00:00.000 00:00:00.010 00:00:43.591     38      2      0 N    N     
+      1 00:00:00.000 00:00:00.000 00:00:02.725      6      0      0 N    N     
+      1 00:00:00.000 00:00:00.000 00:00:00.000     38      0      0 N    N     

Navigation

For additional information about
  • the dynamic SQL statement and statistics, move the cursor to the required line and press F11 (Zoom).
  • other topics, use the PF keys.

Fields

Each field reflects the data that was available when collection was executed. OMEGAMONĀ® for Db2 PE collects data and refreshes the panel each time you navigate to the panel.

Times Exec.
The number of times that an SQL statement in cache was executed. For a cursor statement, this number represents the number of OPENs.
CPU Time
The accumulated CPU time used while executing in Db2.
Elapsed Time
The accumulated elapsed time for the SQL statement.
Wait Time
The accumulated time spent waiting in Db2 for all causes.
Get - Pages
The number of Getpage requests performed for the SQL statement.
Sync Reads
The number of synchronous Buffer Read operations performed for the SQL statement.
Sync Writes
The number of synchronous Buffer Write operations performed for the SQL statement.
ACEL
IDAA flag.
DPS
Dynamic Plan Stability flag (dynamic SQL only). Db2 12 only.