How to extend performance advantages of static SQL statements to dynamic SQL to improve availability and reduce costs - Part 2


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.

Real-time Monitoring (VTAM® Classic Interface)

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.

Thread Details

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 same can be found in the NTH (near-term history) panel, ZHTSQLS.

Subsystem Statistics – SQL Counts and DB2 Commands

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.

Subsystem Statistics – Dynamic Statement Cache


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.


If you want to drill into the statement cache using a key field such as AUTHID, the same "DPS" column is also displayed to pinpoint the stabilized statements.

Finally, if you drill down into the statement details, you will see the DPS-related details for Query Hash ID, Hash ID version, Stabilized Statement ID and Stabilization Group:

System Parameters

The ZPARM display for Performance and Optimization has been extended to include ZPARM CACHEDYN_STABILIZATION:

Real-time Monitoring (Performance Expert Client)


New DB2 commands introduced by DPS are shown in the Performance Expert (PE) Client Subsystem Statistics view under Miscellaneous->DB2 Commands (Display / Other):

Dynamic Statement Cache displays

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:

System Parameters


Real-time Monitoring (Web Console)

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.

Views: 96


You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Bringing DB2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2. [Latest Blogs from the Biggest Names in DB2]

© 2018   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service