Statement Cache and XProc Detail

This panel provides information about the usage of the SQL statement cache and the xProc storage.


>       Help PF1         Back PF3             Up PF7            Down PF8        
> R.M.D                                                                         
>                                                                               
> A-Address Space Summary B-Thread Information C-Shared and Common Storage      
> *-Stmt Cache and xProc  E-Pool Detail        F-IRLM Storage                   
=============================================================================== 
>              Statement Cache and XProc Detail                                 
 J22D                                                                           
+      Total Allocated Shareable Storage for Dynamic SQL           12414976     
+      Total Requested Shareable Storage for Dynamic SQL           10630160     
+      HWM Requested Shareable Storage for Dynamic SQL             10630160     
+      Total Allocated Shareable Storage for Static SQL              667648     
+                                                                               
+      Number of Stmts in 64-Bit Storage                                  0     
+      Number of Stmts in 64-Bit Storage When Interval HWM Is Set         0     
+                                                                               
+      Total Stmt Cache Storage(64-Bit)                                   0     
+      High Water Mark for Stmt Cache Storage(64-Bit) This Interval       0     
+      Timestamp of High Water Mark 64-Bit Local Pools  2016-07-26 22:28:52     
+      Total 64-Bit Statement 2G Cache Block Storage               23199744     
+      Stmt Cache Hash Entry Allocation                             5580541     
===============================================================================

Navigation

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

For additional options

select one of the options from the panel. The same snapshot of DB2 performance data is used as long as Enter is not pressed.
use the PF keys.

Fields

Total xPROC Storage for Dynamic SQL: Shows the total size of the xPROC storage for dynamic SQL that is used by active threads and globaly cached statements.; This applies to Db2 10.
Allocated xPROC Storage for Dynamic SQL: Shows the size of the allocated xPROC storage for globally cached statements.; This applies to Db2 10.
High Water Mark xPROC Storage for Dynamic SQL: Shows the largest amount of allocated xPROC storage so far.; This applies to Db2 10.
Total xPROC Storage for Static SQL: Shows the size of the total xPROC storage that is used for static SQL statements.; This applies to Db2 10.

Total Allocated Shareable Storage for Dynamic SQL: Shows the total allocated sharable storage for dynamic SQL used by active threads (64-bit shared system variable pools); This applies to Db2 11 or higher.
Total Requested Shareable Storage for Dynamic SQL: Shows the total requested sharable storage for dynamic SQL used by active threads (64-bit shared system variable pools).; This applies to Db2 11 or higher.
HWM Requested Shareable Storage for Dynamic SQL: Shows the total allocated sharable storage for static SQL statements (64-bit shared system variable pools).; This applies to Db2 11 or higher.
Total Allocated Shareable Storage for Static SQL: Shows the high water mark of total requested sharable storage for dynamic SQL used by active threads (64-bit shared system variable pools).; This applies to Db2 11 or higher.

Number of Stmts in 64-Bit Storage: Shows the number of statements in the 64-bit agent local pool (64-bit shared agent local variable pools).
Number of Stmts in 64-bit storage when interval HWM Is Set: Shows the number of statements at the time a new interval High Water Mark is set for 64-bit shared agent local variable pools (default statistics interval: 1 minute).

Total Stmt Cache Storage(64-Bit): Shows the allocated statement cache storage that is in 64-bit agent local pools (64-bit shared agent local variable pools).
High Water Mark for Stmt Cache Storage(64-Bit) This Interval: Shows the largest amount of allocated statement cache storage in 64-bit shared agent local pools in this interval (default statistics interval: 1 minute).
Timestamp of High Water Mark 64-Bit Local Pools: Shows the timestamp when the largest amount of storage in 64-bit agent local pools was allocated after the last IFCID 0225 record was written (64-bit shared agent local variable pools).
Total 64-Bit Statement 2G Cache Block Storage: Shows the total statement cache storage blocks above the bar (64-bit shared variable pool).
Stmt Cache Hash Entry Allocation (QW0225DMH): The amount of storage that is allocated for hashing entries in the dynamic statement cache. Db2 12 and later.