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 V540.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
===============================================================================
The following
panel shows the display of Db2 10:
________________ ZESSS VTM O2 V540./C DA31 06/20/13 18:48:28
> Help PF1 Back PF3 Up PF7 Down PF8 Sort PF10 Zoom PF1
>
> 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 31-bit xPROC Storage for Static SQL Statements 765952
+ Total Number of Statements in Static SQL Cache 106
+
+ *
+ Times CPU Elapsed Wait Get Sync
+ Exec. Time Time Time Pages Reads
+ ------ ------------ ------------ ------------ ------ ------
+ 21369 00:00:21.796 00:11:13.978 00:00:00.000 5834K 21365
+ 13952 00:19:26.424 09:46:03.521 00:00:00.752 87M 87M
+ 11003 00:00:08.269 00:04:33.869 00:00:00.000 2323K 11000
+ 6972 00:00:00.017 00:00:00.419 00:00:00.000 63 0
+ 5932 00:00:01.788 00:00:03.932 00:00:00.000 439K 532
+ 5230 00:00:00.941 00:00:02.462 00:00:00.000 10276 4
+ 5226 00:00:00.045 00:00:01.741 00:00:00.000 590 2
+ 3608 00:00:02.681 00:01:28.910 00:00:00.000 725K 3610
+ 2914 00:04:03.612 02:02:46.488 00:00:00.000 19M 18M
+ 782 00:00:00.113 00:00:00.852 00:00:00.000 1567 3
+ 752 00:00:00.003 00:00:00.014 00:00:00.000 0 0
+ 752 00:00:00.000 00:00:00.008 00:00:00.000 0 0
+ 752 00:00:00.000 00:00:00.000 00:00:00.000 0 0
+ 752 00:00:00.069 00:00:00.844 00:00:00.000 6016 0
+ 752 00:00:00.000 00:00:00.001 00:00:00.000 0 0
+ 752 00:00:00.000 00:00:00.029 00:00:00.000 0 0
+ 752 00:00:00.081 00:00:00.362 00:00:00.000 2256 0
+ 529 00:00:00.005 00:00:00.014 00:00:00.000 64 0
+ 321 00:00:00.002 00:00:00.024 00:00:00.000 0 0
+ 321 00:00:00.051 00:00:01.036 00:00:00.000 590 0
+ 267 00:00:02.414 00:00:47.986 00:00:00.000 81176 55795
+ 264 00:00:00.041 00:00:00.348 00:00:00.000 792 0
+ 258 00:00:00.173 00:00:04.416 00:00:00.000 597 1051
+ 1 00:00:00.000 00:00:00.008 00:00:00.000 2 0
+ 1 00:00:00.118 00:00:03.462 00:00:00.000 6398 6386
+ 1 00:00:00.000 00:00:00.177 00:00:00.000 2 3
+ 1 00:00:00.001 00:00:00.135 00:00:00.000 273 1
+ 1 00:00:00.000 00:00:00.018 00:00:00.000 0 0
+ 1 00:00:00.000 00:00:00.000 00:00:00.000 0 0
+ 1 00:00:00.000 00:00:00.009 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
+ 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 31-bit xPROC storage for static SQL Statements
- Total 31-bit xPROC storage that is used for static SQL statements (31-bit DBM1private variable pool).
- Total Allocation for Shareable Static SQL
- The total allocated shareable storage for static SQL statements.
- 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.