Dynamic SQL cache
Views for analysis of the dynamic SQL cache are organized into the following groups:
- Dynamic SQL cache statistics views
- Dynamic SQL statement views
You can access all the views for dynamic SQL cache analysis from the Dynamic SQL Cache Menu (WZCACHE) (see the following figure).
Dynamic SQL Cache Menu.................
Quick Statistics....................... Interval Session
#Stmts in Pool........................ 0.0
Inserts............................... 0 0.0
Global Cache Hit ratio................ 0.0 0.0
Local Cache Hit ratio................. 0.0 0.0
. All Statistics........................
. Statement Summary.....................
SQL Statement List Views................
. SQL List by Time Cached...............
. SQL Execution Statistics by Program...
. SQL Wait Time Totals by Program.......
. SQL List (To Customize)...............
SQL Statement Summary Views.............
. By Program............................
. By User ID............................
. By Application........................
Statement Filters.......................
. Set/Clear Filters.....................
Start Statistics Trace..................
Save the ASSIGNED TRACE NUMBER........
returned from the Start Trace command.
. Start Statistics Trace................
Stop the Statistics Trace...............
Enter the following command after.....
hyperlinking to the Log Display:......
-Stop Trace(Mon) TNO(nn)..............
Use the ASSIGNED TRACE NUMBER returned
from the Start Trace command to.......
replace 'nn' in the TNO(nn) parameter.
. Stop Trace Statistics.................
WZCACHE provides a few key statistics about the dynamic SQL cache usage and performance for each of these periods:
- Current recording interval
- Current session since DB2 was started
You can hyperlink from this menu to see the complete statistics about the dynamic SQL cache as well as several statement-related views.
The statement-related views will show you whether statistics are being collected at the statement level.
If you want to activate these statistics, issue the following DB2 command:
-START TRACE(MON) IFCID(318)
Make note of the TNO trace number that is returned in the message from DB2.
Stop the statistics gathering with this DB2 command:
-MODIFY TRACE(MON) IFCID(318) TNO(nn)
| For more information, view the Quick Course Navigating the dynamic SQL cache. |
The following table lists the views available for dynamic SQL cache:
View name | Group | Type | Description |
|---|---|---|---|
SCFTOTZ | Statement | Detail Summary | Dynamic SQL Cache Filter Results Summarizes the number of statements selected and provides hyperlinks to each of the statement-related tabular views It gives you a choice of which tabular view of the selected statements that you want to see. |
SCPGMZ | Cache Statistics | Tabular Summary | SQL Summary by Program Name Summarizes current usage of the cache by program and provides hyperlinks to the SCSQL view for a list of SQL statements that are used by the selected program |
SCSETF | Statement | Detail | SQL Cache Statement Filters Allows you to specify filters to determine which SQL statements are returned Once set, the filters remain in effect for your session until cleared. |
SCSTMTD | Statement | Tabular | SQL Cache Statement Text Shows the complete SQL statement text for a selected statement |
SCUSERZ | Cache Statistics | Tabular Summary | SQL Summary by User ID Summarizes current usage of the cache by user ID and provides hyperlinks to the SCSQL view for a list of SQL statements that are used by the selected user |
SCXAPPLZ | Cache Statistics | Tabular Summary | SQL Summary by Application Summarizes current usage of the cache by application and provides hyperlinks to the SCSQL view for a list of SQL statements that are used by the selected application |
SCXLIST | Statement | Tabular | SQL Cache Statement Elements List Provides a way for you to create a customized view of all the elements about the SQL statements in the cache that you want to see in each of the following categories:
|
SCXPGMZ | Statement | Tabular | SQL Summary by Program Name |
SCXSTATS | Statement | Tabular | SQL Cache Statement Statistics Provides a tabular list of all the statements in the cache showing the collected statistics |
SCXSQL | Statement | Tabular | SQL Cache Statement List Lists the SQL statements in the cache with the available identifiers, current usage, and the first part of the SQL text |
SCXSQLD | Statement | Tabular | SQL Cache Statement Detail |
SCXUSERZ | Cache Statistics | Tabular Summary | SQL Summary by User ID Summarizes current usage of the cache by user ID and provides hyperlinks to the SCSQL view for a list of SQL statements that are used by the selected user |
SCXWAITS | Statement | Tabular | SQL Cache Statement Waits Shows the available wait time totals per statement |
STCACHE | Cache Statistics | Tabular | Dynamic SQL Cache Statistics Provides an overview of the current usage of the cache in the separate pool above the 2-GB bar It then provides all the DB2 statistics concerning both global and local cache usage, including hit ratios. |
WCACHED | Cache Statistics | Detail | Dynamic SQL Cache Statistics Detail Provides an overview of the current usage of the cache in the separate pool above the 2-GB bar, for each of these time periods:
The local cache can be a large consumer of DBM1 storage. You can hyperlink on the Local Cache Hit Ratio field to access the DB2STORD view and see more information about DBM1 storage usage. |
Related topic