September 24, 2013

What's Ahead for the Optimizer

I had the pleasure of attending IDUG's DB2 Tech Conference in Australia earlier this month. There were numerous presentations on DB2 11, and since IBM is now making this information public, I'll share with you some details about the upcoming release.

What follows is based on Terry Purcell's presentation, "What's Coming from the Optimizer in DB2 11 for z/OS?" (session A10):

DB2 11 optimizer enhancements retrofitted to DB2 10 — Obviously, this means that customers using DB2 10 will be able to benefit from the latest optimizer features and improvements.

Plan management — I'll focus on this topic for this post. DB2 10 introduced APREUSE(ERROR), a plan management parameter that prompts DB2 to reuse the previous access paths for each SQL statement. One drawback to APREUSE(ERROR) is that if any one SQL statement fails, the entire plan/package would fail. However, with DB2 11 comes another new parameter, APREUSE(WARN), that operates at the statement level.

Let's compare the two parameters by summarizing what will happen if you REBIND on a package and some of the statements fail:


  • Access paths are retained on all statements that took the previous hint.
  • A new access path is created for statements on which the hint failed.
  • The package is bound successfully.


  • If all statements took the previous hint, access paths are retained for the entire package.
  • If a single statement fails, package REBIND fails.

Keep in mind that some things could still change between now and DB2 11's general availability. As IBM notes in its standard disclaimer:

"IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion. Information regarding potential future products is intended to outline our 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 our products remains at our sole discretion."

Incidentally, if you'd like to find out more about the recent IDUG conference, in the near future all of the presentations will be posted here. If you're starting at, just select "Tech Library," then "Past Conference Presentations."

Next week I’ll discuss optimizer improvements around predicate indexability.