Static SQL Cache Statistics with Package Information
The statistical data requires that monitor class 1 and ifcid 400 are started.
This panel organizes static SQL cache data by key fields. You can sort the fields to group similar SQL records together.
For the package and collection IDs, only the first 17-bytes are displayed. If the data is longer than 17 bytes, a plus (+) sign is shown to indicate that the actual data is longer than the data that is displayed in the panel. For an example, see the following panel.
With the information on this panel, database administrators can locate particular ZPARMS according to DB2® installation panels, so they can view related ZPARMs of the DB2 subsystem.
The following panel shows the display of DB2 10:
________________ ZESSK VTM O2 V540.#P SZ22 07/07/16 14:46:14 2
> Help PF1 Back PF3 Up PF7 Down PF8 Sort PF10 Zoom PF11
>
> * - Packages B - All Stmts
===============================================================================
> Static SQL Cache Statistics with Package Information
>
> Statistics require that monitor class 29 or ifcid 400 be started
>
ESSK
+
+ Total 31-bit xPROC Storage for Static SQL Statements 765952
+ Total Number of Statements in Static SQL Cache 21
+
+ *
+ Execs StmtID CPU Time Elapsed Time Package Collection ID ACCEL
+ ----- ------ ------------ ------------ ------------- ------------------ -----
+ 5553 596346 00:00:00.572 00:00:14.224 SWG0XVDS DSN_DEFAULT_COLLI+ N/A
+ 1676 596369 00:00:00.152 00:45:45.635 SWG0XVDS DSN_DEFAULT_COLLI+ N/A
+ 1676 596363 00:00:00.120 00:30:02.761 SWG0XVDS DSN_DEFAULT_COLLI+ N/A
+ 162 596345 00:00:00.029 00:00:00.038 SWG0XVDS DSN_DEFAULT_COLLI+ N/A
+ 123 596375 00:00:00.006 00:00:00.021 SWG0XVDS DSN_DEFAULT_COLLI+ N/A
+ 4 764511 00:00:00.000 00:00:00.103 DSQGICVS Q N/A
+ 2 764780 00:00:00.000 00:00:00.000 DSQGSDB2 Q N/A
+ 1 2604K 00:00:00.000 00:00:00.000 FPEVWRPA KO2OM530 N/A
+ 1 764733 00:00:00.000 00:00:00.059 DSQGRDBR Q N/A
+ 1 764513 00:00:00.000 00:00:00.000 DSQGICVS Q N/A
+ 1 764774 00:00:00.000 00:00:00.000 DSQGSDB2 Q N/A
+ 1 764910 00:00:00.000 00:00:00.070 DSQGSDB9 Q N/A
+ 1 2604K 00:00:00.000 00:00:00.002 DGOVSDOB KO2OM530 N/A
+ 1 2604K 00:00:00.007 00:00:00.034 DGOVSDOB KO2OM530 N/A
+ 1 2604K 00:00:00.001 00:00:00.009 DGOVSDOB KO2OM530 N/A
+ 1 2604K 00:00:00.000 00:00:00.000 DGOVSDOB KO2OM530 N/A
+ 1 2604K 00:00:00.000 00:00:00.000 DGOVSDOB KO2OM530 N/A
+ 1 2604K 00:00:00.000 00:00:00.004 DGOVSDOB KO2OM530 N/A
+ 1 2604K 00:00:00.000 00:00:00.011 DGOVSDOB KO2OM530 N/A
+ 1 2604K 00:00:00.000 00:00:00.062 DGOVSDOB KO2OM530 N/A
+ 1 764466 00:00:00.000 00:00:00.028 DSQGBOR Q N/A
=============================================================================== 
The following panel shows the display of DB2 11
and later:
________________ ZESSK VTM O2 V540.#P SEE2 07/07/16 14:42:16 2
> Help PF1 Back PF3 Up PF7 Down PF8 Sort PF10 Zoom PF11
>
> * - Packages B - All Stmts
===============================================================================
> Static SQL Cache Statistics with Package Information
>
> Statistics require that monitor class 29 or ifcid 400 be started
>
ESSK
+
+ Total Allocation for Shareable Static SQL 106496
+ Total Number of Statements in Static SQL Cache 9
+
+ *
+ Execs StmtID CPU Time Elapsed Time Package Collection ID ACCEL
+ ----- ------ ------------ ------------ ------------- ------------------ -----
+ 1 1015K 00:00:00.000 00:00:00.000 FPEVWRPA KO2OM530 N
+ 1 1015K 00:00:00.000 00:00:00.000 DGOVSDOB KO2OM530 N
+ 1 1015K 00:00:00.001 00:00:00.006 DGOVSDOB KO2OM530 N
+ 1 1015K 00:00:00.003 00:00:00.039 DGOVSDOB KO2OM530 N
+ 1 1015K 00:00:00.000 00:00:00.000 DGOVSDOB KO2OM530 N
+ 1 1015K 00:00:00.000 00:00:00.000 DGOVSDOB KO2OM530 N
+ 1 1015K 00:00:00.000 00:00:00.002 DGOVSDOB KO2OM530 N
+ 1 1015K 00:00:00.000 00:00:00.004 DGOVSDOB KO2OM530 N
+ 1 1015K 00:00:00.001 00:00:07.623 DGOVSDOB KO2OM530 N
===============================================================================Navigation
For additional information about
- the statistics detail for a particular statement, place the cursor on the row that contains the statement and press PF11.
- other topics, use the PF keys.
Fields
The following fields are displayed
for Static SQL cache:
- Total 31-bit xPROC Storage for Static SQL Statements
- The total 31-bit xPROC storage for static SQL statements (31-bit DBM1 private variable pool).
- This applies to DB2 10.
- Total Allocation for Shareable Static SQL
- The total allocated shareable storage for static SQL statements.
- This applies to DB2 11 and higher.
- Total Number of Statements in Static SQL Cache
- The count of static SQL statements in cache.
The following fields are displayed for each
static SQL statement:
- Execs
- The number of executions.
- StmtlD
- The statement identifier.
- CPU Time
- The accumulated in-DB2 CPU time. This time includes CPU that is consumed on an IBM® specialty engine.
- Elapsed Time
- The accumulated in-DB2 elapsed time.
- Package
- The first 17 bytes of the package ID.
- Collection ID
- The first 17 bytes of the collection ID.
- ACEL
- IDAA flag.