Disclaimer: Information regarding potential future products is intended to outline IBM’s general product direction and it should not be relied on in making a purchasing decision. The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for IBM’s products remains at IBM’s sole discretion.  

Dynamic Plan Stability in DB2 12 ESP for z/OS  

Over the last few versions of DB2, IBM has introduced more capabilities to hold on to existing access paths (when we want it to), instead of determining a new access path when we rebind (for static SQL) or execute a query (dynamic SQL).  There are a number of reasons we want to retain existing plans and also a number of reasons why we would want a new plan.  IDUG will be publishing a DB2 V12 white paper to coincide with the release of DB2 12 for z/OS.  This article is part of that white paper.

Let’s keep what we’ve got:

• It’s good (i.e. it performs well), why take a chance on changing it?
• It was good, the new one is bad – can we get back the old one?
• It takes time to generate an access path; for a high volume dynamic query, it would be nice if we could save the time of doing a full prepare and access path generation.

Let DB2 do its thing and give us a new access path:

• It’s not good, maybe DB2 can give us a better one.
• Things change – tables grow the relative size of tables changes over time. Maybe the access path was good last year, but is not the best one with today’s statistics.
• Each new version of DB2 features improvements to the Optimizer. A rebind might be able to give us a better access path even with the same statistics.
• We’ve created a new index and we want DB2 to use it (if the Optimizer thinks it is the best choice).

If DB2 always gave the same or a better access path every time we prepared a statement or did a rebind, we wouldn’t worry so much.  But sometimes the access path is worse than before.

For static SQL, the tools we have include Access Path Stability (saving up to 2 previous copies of the access plan – previous and original – and being able to switch to one of the saved copies), and BIND options APREUSE and APCOMPARE to tell DB2 to use the existing access path when we rebind (provided that it can).  Of course one of the advantages of static SQL is that unless we rebind, the access path doesn’t change – so it is always stable for a while.

For dynamic SQL, we have the dynamic statement cache.  When we execute a dynamic query, DB2 will store the statement and its access path in the cache and can reuse the access path if we re-execute the identical statement.  Of course, if we have literals in the query, then 2 queries that are identical except for the literals will not actually be identical and will not reuse the cached statement.

The Cache is not Enough

The dynamic statement cache does help, but only if a statement stays in the cache.  There are a number of reasons it may not:

• The cache is not large enough. A query that is not executed frequently may be forced out of the cache to make room for other statements.  This can especially be a problem if the statements use literals instead of host variables for high cardinality columns such as IDs.  Each execution may be a different statement.
• Runstats invalidates any statement that references the tables/indexes for which runstats has been run.
• A DB2 recycle empties the cache.

DB2 12 for z/OS comes to the rescue

Just as DB2 stores static plans in the DB2 catalog, DB2 12 can optionally save dynamic SQL access paths in the catalog.  When a query is executed, DB2 will look in the dynamic statement cache.  If it is not found there, it will look in the catalog before resorting to a full prepare.

How are Dynamic SQL statements captured and reused?

DB2 does not automatically capture all dynamic SQL.  You have to tell it to via a DB2 command.  You do not have to capture all statements, you can filter by several means. 

Seeing what is in the Dynamic Statement Cache

Before deciding on what statements to capture, you can examine the cache to see what is there, performance metrics on each statement, and the access plans.  The steps to do this are:

• Create a DSN_STATEMENT_CACHE_TABLE with your sqlid.
• EXPLAIN STMTCACHE ALL
• Query DSN_STATEMENT_CACHE_TABLE as well as the other plan tables.

Seeing what Statements have been Stabilized

DB2 stores the stabilized queries in new catalog tables which you can query and run Explain against.

Ongoing Monitoring

You don’t want to just blindly capture all of your dynamic SQL and then sit back. 

• First of all, you only want to capture access paths that are good, otherwise you are locking in poor performance.
• New SQL is always introduced in an application. You want to stabilize these as appropriate. 
• Stabilized queries can be invalidated. If the underlying objects change, the stabilized access path may no longer be available (e.g. an index was dropped).  Even if the index is recreated, you have to re-capture the statement (re-stabilization).
• Just as with static SQL, if the statistics change significantly, or if new indexes are added, you may want a new access path.

Summary

With more and more of today’s applications using dynamic SQL, having the option to retain a good access path and avoid prepares is important to maintain good, consistent performance.  Dynamic Plan Stability is a major new capability and a key reason for looking forward to DB2 12 and early adaptation.  The V12 white paper will have more detail on this topic and on the many other new features of DB2 12 for z/OS.  In the meantime, IDUG will have a monthly blog highlighting some more of these new features.