Static SQL Cache Statistics

This panel provides additional details about the static SQL cache.

The statistical data requires that monitor class 1 and ifcid 400 are started.

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

The following panel shows the display of DB2® 11 and DB2 12:

 ________________ ZESSS    VTM     O2       V610.HZ SE11 06/20/13 18:41:44   2  
> Help PF1      Back PF3       Up PF7      Down PF8     Sort PF10    Zoom PF11  
>                                                                               
>  A - Packages     * - All Stmts                                               
=============================================================================== 
>         Static SQL Cache Statistics with Times and Counts                     
>                                                                               
>     statistics require that monitor class 29 or ifcid 400 be started          
>                                                                               
 ESSS                                                                           
+                                                                               
+ Total Allocation for Shareable Static SQL              348160                 
+ Total Number of Statements in Static SQL Cache             24                 
+                                                                               
+ *                                                                             
+   Times      CPU          Elapsed        Wait       Get     Sync              
+   Exec.      Time         Time           Time       Pages   Reads             
+  ------  ------------  ------------  ------------   ------  ------            
+      49  00:00:00.048  00:00:01.827  00:00:00.002     1815       5            
+      49  00:00:00.000  00:00:00.090  00:00:00.000        0       0            
+      49  00:00:00.005  00:00:00.947  00:00:00.000       98       1            
+      49  00:00:00.007  00:00:01.390  00:00:00.003      294       4            
+      49  00:00:00.015  00:00:01.948  00:00:00.000      294       0            
+      49  00:00:00.021  00:00:00.726  00:00:00.003      688       5            
+      49  00:00:00.005  00:00:01.293  00:00:00.000      150       6            
+      49  00:00:00.009  00:00:00.583  00:00:00.001      248      10            
+      12  00:00:00.000  00:00:00.022  00:00:00.000        0       0            
+      12  00:00:00.000  00:00:00.027  00:00:00.000        0       0            
+      12  00:00:00.000  00:00:00.013  00:00:00.000        0       0            
+       8  00:00:00.000  00:00:00.065  00:00:00.000        0       0            
+       3  00:00:00.000  00:00:00.008  00:00:00.000        0       0            
+       3  00:00:00.000  00:00:00.001  00:00:00.000        0       0            
+       2  00:00:00.006  00:00:00.300  00:00:00.221      470       2            
+       1  00:00:00.000  00:00:00.000  00:00:00.000        0       0            
+       1  00:00:00.000  00:00:00.035  00:00:00.000        3       1            
+       1  04:14:30.425  08:10:42.681  00:00:00.000     261M       0            
+       1  00:00:00.000  00:00:00.000  00:00:00.000        0       0            
+       1  00:00:00.000  00:00:00.000  00:00:00.000        0       0            
+       0  00:00:00.000  00:00:00.000  00:00:00.000        0       0            
+       0  00:00:00.000  00:00:00.000  00:00:00.000        0       0            
+       0  00:00:00.000  00:00:00.000  00:00:00.000        0       0            
+       0  00:00:00.000  00:00:00.000  00:00:00.000        0       0            
===============================================================================

Navigation

You can scroll through the information using F7 and F8 (if the information requires more than one panel).

For more information about

a particular static SQL cache, move the cursor to the respective information line and press F11 (Zoom). For more information, see the description of panel EDM Snapshot Static SQL Cache Statement Detail.

Fields

Total Allocation for Shareable Static SQL: The total allocated shareable storage for static SQL statements.; This applies to Db2 11 or higher.
Total number of statements in static SQL Cache: The count of SQL statements in the static SQL cache.

Times Exec.: The number of times that an SQL statement in cache was executed.

CPU Time

The accumulated CPU time used while executing in Db2. This time includes CPU that is consumed on an IBM® specialty engine.

Elapsed Time

The accumulated elapsed time for the SQL statement.

Wait Time (Derived)

The accumulated time spent waiting in Db2 for all causes.

QW0401SI Accumulated wait for synchronous I/O
QW0401LK Accumulated wait for lock
QW0401EU Accumulated wait for synchronous execution unit switch
QW0401GL Accumulated wait time for global locks
QW0401OR Accumulated wait time for read activity done by another thread
QW0401OW Accumulated wait time for write activity done by another thread
QW0401LH Accumulated wait time for latch req
QW0401PL Accumulated wait for page latch
QW0401DL Accumulated wait for drain lock
QW0401CM Accumulated wait for drain lock waiting for for claims to be released
QW0401LW Accumulated wait for log writer
QW0401WC Accumulated wait for global child L-Locks. Db2 12 and later.
QW0401WD Accumulated wait for global other L-Locks. Db2 12 and later.
QW0401WE Accumulated wait for global P/P P-Locks. Db2 12 and later.
QW0401WF Accumulated wait for global page P-Locks. Db2 12 and later.
QW0401WG Accumulated wait for global other P-Locks. Db2 12 and later.
QW0401WH Accumulated wait for PIPE locks. Db2 12 and later.
QW0401WPQS Accumulated wait for PQS locks. Db2 12 and later.

GetPages

The number of Getpage requests performed for the SQL statement.

Sync Reads

The number of synchronous Buffer Read operations performed for the SQL statement.