Dynamic Plan Stability with DB2 for z/OS

Project and Program: Information Management, Database

Tags: Proceedings , SHARE Providence 2017 , 2017

Applications that use repeating dynamic SQL often suffer from query performance instability when compared to static SQL. While the risk of any individual query regressing on any given day is small, exposing thousands of queries to access path changes every day as statistics, the environment, maintenance level, even release of DB2 changes results in exposure to query performance regression.

This presentation covers how DB2 12 for z/OS  is enhanced to support the ability to stabilize and reuse the prepared-form (runtime structures) of dynamic SQL, extending the stability currently only available to statically bound SQL to stabilized dynamic SQL.

Presentation outline:
* Overview of Dynamic Statement Cache (DSC) in DB2 for z/OS.

* How to identify statements in the DSC to be stabilized, externalized them to persistent catalog tables, and load them on subsequent statement executions for the same performance characteristics.
* How to free stabilized dynamic SQL to allow new access paths. How to identify unused stabilized dynamic SQL to be freed. How statistics can be used to ensure that short PREPAREs, cache hit ratio, catalog hit ratio are optimal.
* How to query stabilized dynamic SQL dependencies which will allow DBA to identify queries that are dependent on particular tables and indexes. Those are objects that are subject to proposed DDL changes that could invalidate or drive the DBA to free stabilized statements.
* How to examine the access path for stabilized dynamic SQL, to allow for investigating and/or tuning stabilized dynamic queries.
* How to turn on monitoring so DB2 can continuously stabilize dynamic SQL by criteria, also display monitoring progress and stop the monitoring process as needed.-Tammie Dang-IBM Corp

Back to Proceedings File Library