Blogs

The Power’s in the Rebind

Take it from a consultant: IBM DB2 11 for z/OS enhancements make a great first impression

Now that IBM® DB2® 11 for z/OS® data management software has been generally available for several months, it’s had ample opportunity to make some first impressions—and they have been positive. In particular, DB2 11 for z/OS has some scintillating performance enhancements that come at the cost of a mere rebind. The degree to which any organization benefits from DB2 11 for z/OS improvements varies, of course, depending on the features used, the types of workload run, and the effort that is put into tuning previous releases of DB2 for z/OS.

IBM has always recommended running a full rebind after migrating to a new release of DB2 for z/OS, and this same recommendation still stands for migrating to DB2 11 for z/OS. Based on personal experience, quite a few client organizations are reluctant to issue such a mass rebind. That concern is understandable because introducing accidental performance degradations should be avoided. Nevertheless, the enhancements discussed here require no changes to code—only a rebind. For those plans and packages in which the change in behavior causes a deteriorated access path, I recommend a fallback plan by working with extended plan stability.

DB2 11 for z/OS comes with a large range of predicate indexable capability improvements. They are a collection of measures by the enhanced DB2 11 optimizer designed to solve some of the problems of where predicates that previously resulted in poorly performing or suboptimal access paths. Before the release of DB2 11, improving these SQL statements often meant tracking down the underperforming SQL statements, correcting them, and rebinding the package. In DB2 11, the query transformation component rewrites the query and applies these common best practices to the query before handing it off to the optimizer. However, it will do so only if no index on expression exists to support the stage2 predicate.

The strength behind rebind

One mistake that is often made is using the substring function starting at the first position rather than using a like operator, as in the following example:

SUBSTR(colABC,1,2) = 'AB'

Those familiar with tuning projects know that this substring predicate is stage2 non-indexable, and can easily be rewritten as a like operator, which is stage1 indexable, while remaining the same logical predicate, as in this following example:

colABC like 'AB%'

In DB2 11, using a like operator in this manner is recognized as a potential enhancement that can be implemented during a simple rebind, rather than as a query rewrite.

Another DB2 11 improvement enables solving some or predicates, including nulls, more efficiently than in previous versions. When a column is nullable and the null values have to be included in the result, then a developer is faced with having to write the actual search criteria, but also has to add the possibility of the unknown. Consider the following example:

colABC > ? or colABC is NULL

Before the release of DB2 11, this statement quite possibly resulted in a multi-index access, which may be as good—but usually isn’t—as performing as a single index access. DB2 11 solves this problem more efficiently and in a manner that is very similar to the predicate:

colABC> ?

Internally in DB2, the NULL value is the high value, so it is stored at the high end of the index, which allows 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 may get better index access. The result, of course, includes the null values. Again, this improvement is available by merely doing a rebind in DB2 11.

DB2 11 will prune—eliminate—always true and always false predicates before handing the query to the optimizer. Predicates such as 1=1 or 1=2, which are commonly generated by SQL generators, are eliminated during query rewrite. Quite often, eliminating the need for the or expression allows a more efficient access path, as shown in the following example:

WHERE (1=1 AND T1.C1 = T2.C1) OR (1=2 AND T1.C2 = T2.C2)

This example WHERE clause can cause trouble because of the OR to a predicate, which is never true. In DB2 11, this WHERE clause can be rewritten as follows:

WHERE T1.C1 = T2.C1

If in the past OR 0 = 1 was used to tweak an SQL statement and force a specific access path, there is no need to panic. This predicate is recognized by IBM and continues working as before.

Other optimizer enhancements

Beyond these few examples, there are still other improvements in the optimizer area that include predicate push down into materialized table expressions for some stage2 and non-Boolean term predicates. There is also enhanced decompression speed—especially if only a few columns are needed rather than all of them—and many other enhancements. The only work required in DB2 11 deployments is rebinding.

Bear in mind that opting to use APREUSE to provoke access path stability rather than for extended plan management does not offer any of the benefits discussed here. When possible, APREUSE forces the same access path as before and doesn’t consider the potential enhancements that come from a rebind in DB2 11.

DB2 11 for z/OS brings many improvements that previously required SQL code changes. The great thing about these improvements is that now migration requires no effort from the developer. Simply pushing the “rebind button” does the trick. DB2 11 for z/OS is fully providing the power of rebind.

In a sequel article, "Beyond the Rebind," I go beyond the rebind and present DB2 11 for z/OS performance enhancements that do require intervention by administration. Please share any thoughts or questions in the comments.

[followbutton username='db2kurts' count='false' lang='en' theme='light']
 
[followbutton username='IBMdatamag' count='false' lang='en' theme='light']