Dynamic SQL cache
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. |