Blog
DB2utor

Categories

October 01, 2013

A Smarter Optimizer in DB2 11

One of the challenges of using ERP applications such as Oracle or SAP is that the SQL can't be rewritten to improve the performance. However, with the optimizer enhancements in DB2 11, predicates can be automatically rewritten as stage 2, making them indexable.

This information is taken from session Terry Purcell's recent presentation at the IDUG DB2 Tech Conference in Australia. The usual disclaimers apply.

In the sample below, the first line is SQL rewritten as stage 2. The next line is indexable:

            EQUAL Predicates

o    WHERE YEAR(DATE_COL) = 2013      ←Stage 2

            Becomes

o   WHERE DATE_COL BETWEEN ‘2013-01-01’ and ‘2013-12-31’   ←Indexable

This rewrite will also occur with IN, BETWEEN and RANGE predicates. Terry's presentation has more examples.

Here are some additional SQL-related enhancements:

Prune always TRUE predicates: When a predicate always evaluates to TRUE, DB2 removes it from the statement so the predicate won't evaluate at execution time. For example:

  • WHERE 1=1 and CUSTNO = ?

      becomes

  • WHERE CUSTNO = ?

 

Prune always FALSE predicates: When a statement is written to always evaluate to FALSE, the optimizer will rewrite the SQL and remove the predicate. In this example, ‘A’ = ‘B’ is always false, so that predicate is removed during SQL rewrite:

  •  WHERE C1 = ? OR ‘A’=’B’

becomes

  •  WHERE C1=?

DB2 11 extends the pruning to complex predicates that include AND Boolean operations. In this example, the predicate after the OR will always be false, so the entire predicate is removed:

  •  WHERE (1=1 and T1.C1 = T2.C1)

      OR   (1=2 and T1.C2 = T2.C2)

    becomes

  •  WHERE T1.C1 = T2.C1

     The documented statement “OR 0=1” won't be pruned because this is a documented coding tip.

 

CASE statements (formerly stage 2) are now indexable: For example:

              WHERE C1 = CASE(CAST(? AS INT))

                                          WHEN 1 THEN ‘A’

                                          WHEN 2 THEN ‘B’

                                          ELSE ‘C END;

 

CASE statement when JOIN predicate:

  • When the CASE expression must be evaluated first.
  • In the example below, join predicate is indexable if C1 is evaluated first.

 

 WHERE T2.C1 = CASE WHEN T1.C1 = ‘Y’

                                          THEN T1.C2

                                           ELSE   T1.C3    END;

 

Predicate PUSH DOWN:The process of predicate push-down involves the optimizer rewriting the SQL in such a way the predicate can be evaluated sooner; this reduces the number of rows that are evaluated in stage 2. The IBM Redbook, "DB2 10 for z/OS Performance Topics," has a detailed explanation.

DB2 11 extends predicate push-down to non-Boolean term (OR) predicates and stage 2 predicates (expressions). Here's an example of each:

Example 1: With non-Boolean term (OR) predicates, this:

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT

AND (B.WORKDEPT LIKE 'C%' OR B.WORKDEPT LIKE 'A%‘);

 

     becomes this:

 

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

WHERE WORKDEPT LIKE 'C%' OR WORKDEPT LIKE 'A%‘)

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT;

 

Example 2: With stage 2 predicates (expressions), this:

 

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT

AND UPPER( B.WORKDEPT) = 'C01'

 

      becomes this:

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

WHERE UPPER( B.WORKDEPT) = 'C01'

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT

The optimizer enhancements in DB2 11 are quite impressive. Keep in mind though that the performance gains you'll receive by upgrading will vary depending on your SQL. However, the good news is the optimizer is now capable of rewriting the SQL automatically. As noted, this is especially handy when you're running package applications that don't allow for SQL coding changes.