DB2 11 Performance Expectations

By Kurt Struyf posted Feb 25, 2014 12:30 PM


In every new release of DB2 for z/OS, performance improvements are made. How much someone benefits from these improvements varies, depending on the features you use, the type of workload you run and the effort that you put into tuning in the previous releases of DB2 for z/OS. Nevertheless, I feel that significant performance improvements were made in DB2 11.

The first performance feature of DB2 11 for z/OS isn’t technically a performance feature, but it will surely bring more stability: a new option on APREUSE.   

DB2 10 introduced the option APREUSE(ERROR), indicating that if the optimizer couldn’t choose the previous access path for a query, the rebind of the entire package would fail and no new DB2 10 package (with all the new runtime improvements) was created.

 In that regard, APREUSE(ERROR) could mean you won’t get the new optimizer and runtime benefits, as one query’s access path can’t be reused, the entire package doesn’t get to be rebound  and  the old one gets reused. When APREUSE(ERROR) is able to reuse the access paths, you will end up with new runtime structures, but it still means you do not get any of the potentially new optimize benefits.  So it’s great for stability, but might be sub optimal from performance point of view. So, within releases it might be a good strategy to use APREUSE, but after moving to a new release APREUSE might not be a good option, as you might be losing performance benefits.

The great thing about the new DB2 11 key word APREUSE(WARN) is that it works on a statement level.  Imagine that after an upgrade to DB2 11, out of all the statements in a package, one statement isn’t getting the same access path due to improvements of the optimizer (or query transformation improvements), DB2 will still create a DB2 11 package. This package utilizes all of the performance benefits that will come with that, but issues a warning about the statements it couldn’t keep the access path. Evidently, even though improvements to the optimizer or query transformation are a good thing, it might cause an access path regression for your particular query. Keep in mind, the option APREUSE(ERROR) has not gone away, IBM merely gave you more granularity and flexibility.

Predicate Indexability Improvements
This improvement is a collection of measures by the improved DB2 11 optimizer to solve some of the issues of “where predicates” that previously resulted in poorly performing or sub optimal access paths.
In DB2 11, the query transformation component will optimize some of the less optimal coding that sometimes occurs. E.g. SUBSTR(colABC,1,2) = ‘AB’.

If you’ve been around tuning projects you’ll know that this predicate is stage2 non index-able.  You’ll probably also know that a substring starting on the first position can be rewritten as a “like,” while remaining the same logical predicate. E.g.  colABC like ‘AB%’, which was stage1 indexable.

Before DB2 11, it was the developer’s responsibility to code the predicate correctly and make the best use of the existing indexes. DB2 11 query transformation will rewrite the query and apply these common best practices to the query before handing it off to the optimizer. However, it will only do so if no index on expression exists supporting the stage2 predicate.

Another improvement allows DB2 to more efficiently solve some “or” predicates, including nulls. For instance:

  • colABC > ? or colABC is NULL 
  • DB2 11 solves that more efficiently similar to colABC> ?

Internally in DB2, the NULL value is the high value so stored at the high end of the index, allowing DB2 to pass through the index only once starting at colABC larger than hostvariable. As far as access path selection is concerned, by eliminating the “OR” clause, the optimizer might get better index access. The result, of course, will include the null values.

DB2 11 will eliminate always true and always false predicates before handing the query to the optimizer. Predicate like “1=1” or “1=2,” which are commonly generated by SQL generators, will be eliminated during query rewrite. E.g.; 
(1=1 AND T1.C1 = T2.C1)
OR (1=2 AND T1.C2 = T2.C2)

The example where-clause can cause trouble because of the “OR” to a predicate, which will never be true.  In DB2 11 this will be re-written toWHERE T1.C1 = T2.C1

Before you start panicking about all of the predicate tweaking you did using “or 0=1,” this predicate is recognized by IBM and will remain working as before.

Beyond the few examples given here, there are still other improvements in the optimizer area, including predicate push down into materialized table expressions for some stage2 and non-Boolean term predicates, and many more. How much will you benefit from these improvements? Well, it depends - how much tuning has happened? How many of these “open and shut” predicates are in your code?  The great thing about this DB2 11 for z/OS improvement is that besides a rebind (for static SQL), no work is needed from your developers or DBAs. Of course it means that during your bind your access paths must be allowed to change, to get these benefits in DB2 11. When your DB2 11 bind fails due to APREUSE(ERROR), you will not get these benefits.

Other DB2 11 for z/OS Improvements
Discussing all DB2 11 improvements would make this article too lengthy, but there are some improvements I would still like to point out:

  • Improved page range screening and smarter DPSI use
  • Not logged option for Declare Global Temporary Tables
  • Improved pseudo-delete clean up
  • Improved decompression performance

Last but not least, the possibility of the optimizer to give feedback on which statistics it is missing in SYSSTATFEEDBACK catalog table, allows you to use or write tooling to improve the quality of your runstats jobs and collect the statistics the optimizer is missing.

I hope you enjoyed this article - the first one on this month’s topic of DB2 for z/OS performance. If this topic is of interest to you, I invite you to keep track of the content committee blog space the coming month. You can find us at: http://www.idug.org/p/bl/bl/blogid=278
We have a number of great articles and videos coming up this month. Take a look!

  • Java Frameworks for JDBC access an article by Ludovic Janssens about performance choices you can make when using Java Frameworks.
  • Tuning explained to C-level: a video by Thomas Baumann (IDUG Hall of Fame Speaker) on explaining to your (top) management the benefits of your tuning efforts.
  • Efficiently Sending Data Downstream by John Maenpaa on replicating a high volume low latency environment.

To read more about DB2 11 features, look forward to DB2 11 for z/OS Performance Topics (SG248222), a redbook which is currently being written. Expect it somewhere mid to late spring


Kurt Struyf
Senior Consultant