IBM DB2 - The Ultimate Database for Cloud, Analytics & Mobile
This is the second of a two-part series covering Dynamic Plan Stability (DPS) in DB2 12 and how IBM OMEGAMON XE for DB2 Performance Expert/Monitor can help you take advantage of it in both batch and online/real-time monitoring. Part 2 focuses on real-time monitoring.
The VTAM Classic interface offers in-depth monitoring for running threads, system statistics including statement caches, system parameters and near term historical information. DB2 12 Dynamic Plan Stability impacts almost all of these areas.
As stated in the first part of this article on ‘batch reporting,’ Dynamic Plan Stabilization (DPS) implements a mechanism to cache the dynamic statement access plan in the DB2 catalog. Once a dynamic statement re-enters the statement cache after being discarded (evicted), DPS allows the access plan to be loaded from the catalog cache by means of a query hash value into the statement cache, thus avoiding an expensive full prepare. In other words, DPS extends the advantages of static statements, as listed above, to dynamic SQL
The remainder of this article highlights where to find dynamic plan stability-related information in the VTAM classic panels.
If you select “T” from the VTAM main menu, it will take you to the thread summary panel (ZSUMM) where you can drill down into the thread details panel (ZTHDTL) of a running thread. By looking at the “SQL COUNTS” for the thread, you will find a new entry “Prepares (Catalog Found)” in the “Prepare Statistics” section of the ZTSCNT panel that shows whether any SQL statements in the thread have been prepared using DPS. (Catalog Found is indicating that the prepared statement was found in the catalog cache – avoiding a full prepare.)
The Subsystem statistics have been enhanced to show the new counter for prepares originating from a catalog lookup as well as the new DB2 commands. Both enhancements can be found in the respective historical (NTH) panels.
Here you see the new SQL count for “Prepares (Catalog Fnd)”:
The portion of the panel shows the new commands introduced by DPS that are displayed in VTAM.
VTAM Classic contains many screen changes for DPS support of dynamic statement cache monitoring. All screens shown below have corresponding support in the respective NTH screens.
Navigate to the statement cache analysis through the resource manager option, by selecting the EDM Pool view.
In the overview screen (ZEDMP), new entries for the catalog loads and the catalog load percentage for prepares can be found:
When viewing the EDM Pool in the "all statements" display, a new column is added, indicating whether a statement belongs to a stabilized group as set by DPS. Note that this column is sortable, thus allowing to quickly identify the stabilized statements.
The ZPARM display for Performance and Optimization has been extended to include ZPARM CACHEDYN_STABILIZATION:
New DB2 commands introduced by DPS are shown in the Performance Expert (PE) Client Subsystem Statistics view under Miscellaneous->DB2 Commands (Display / Other):
Open the Dynamic SQL Statements Overview page to view the DPS features under consideration:
If you drill down to the Statement Cache using the “SQL Statements” view, you will see the DPS-related counters in the Statement detail:
The SQL- and Extended Insight Dashboards both show statement cache metrics based on SQL statement Details for a selected statement.
The SQL Dashboard Execution summary view has been enhanced for to display the Stabilization group as a column in identifying the stabilized statements:
After selecting the SQL statement in the Execution Summary view, the SQL statement details section displays the new DPS-related metrics:
DB2 12’s implementation of dynamic plan stability demonstrates IBM’s commitment to leading enterprise relational database management systems support for modern languages and applications. OMEGAMON XE for DB2 Performance Expert provides critical management information about the effectiveness of applications’ exploitation of this facility and can help with investment decisions for future application development.