DB2 predicate and query transformations

Why transformations exist

Query generators and ORM tools emit verbose SQL: extra joins, nested IN lists, views on views. Humans write YEAR(col) = 2020 because it reads nicely. IBM’s documented stance is still: write the simple form. Do not complicate a statement to “invite” a transform. Transforms are not guaranteed. When they fire, PLAN_TABLE and DSN_PREDICAT_TABLE show extra predicates or fewer query blocks than you expected.

Stage 1 versus stage 2

Predicate stage is not a rewrite by itself, but every transformation is judged by whether it produces earlier filtering.

Within a stage after matching, IBM applies equality (including single-item IN and BETWEEN with the same bound twice), then ranges and IS NOT NULL, then other types. DSN_FILTER_TABLE.STAGE shows where a predicate landed.

• Indexable + stage 1 — COL = value, COL BETWEEN, COL LIKE 'ABC%', many IN lists
• Stage 1, not matching — leading-wildcard LIKE '%ITH' can still be stage 1 screening, not a matching start
• Stage 2 — COL LIKE '%X%' in many cases, YEAR(COL), COL + 1 = 10, T1.COL1 = T1.COL2 (same table), many correlated subquery comparisons

AND of indexable predicates can still match leading index columns. OR of stage 1 with stage 2 makes the compound stage 2. One residual OR can disable early filtering for the whole disjunction.

1
2
3
4
5
6
-- Prefer a range (stage 1, indexable) over a function (often stage 2)
SELECT EMPNO, LASTNAME, HIREDATE
  FROM HR.EMPLOYEE
 WHERE HIREDATE BETWEEN '2020-01-01' AND '2020-12-31';
 
-- YEAR(HIREDATE) = 2020  -- typically worse; hope-for-rewrite is not a strategy

IBM documents that YEAR(COL) and SUBSTR(COL, 1, n) can be indexable/stage 1 in specific cases (SUBSTR start must be 1). Code the range anyway so you are not depending on a special-case rewrite.

Predicate simplification

Db2 may remove predicates that are always true or already implied, fold constants, and reshape IN lists. IBM also documents how it modifies IN predicates (for example converting some IN lists toward forms that can use IN-list matching, ACCESSTYPE N). Pre-evaluated predicates such as a literal comparison that is already known at prepare time can disappear from the executable path.

Simplification is why EXPLAIN sometimes shows fewer predicates than your WHERE clause. It is also why adding a redundant predicate “for the optimizer” can do nothing—or can help only when it enables transitive closure (next section).

Predicate transitivity (transitive closure)

If the query logically implies another predicate, Db2 can generate that predicate for access path selection. Classic example:

1
2
3
4
5
SELECT E.EMPNO, D.DEPTNAME
  FROM HR.EMPLOYEE E
  INNER JOIN HR.DEPARTMENT D
    ON E.WORKDEPT = D.DEPTNO
 WHERE D.DEPTNO = 'A00';

You wrote a local predicate on DEPARTMENT and a join. Logic also says E.WORKDEPT = 'A00'. That generated local predicate can match an index on EMPLOYEE.WORKDEPT. Without transitive closure, EMPLOYEE might be accessed only through the join.

IBM’s rules (simplified): for single-table or inner join queries, generation can occur when you have an equal join or local predicate COL1 = COL2 and a Boolean term on one side that is equality, BETWEEN / NOT BETWEEN, or IN-list. Generated forms include COL = value, COL BETWEEN, and COL1 = COL2. For outer joins, generation is more restricted: a generated predicate must not reference the null-producing table in a way that would change semantics. When conditions are met, Db2 may generate the predicate even if you already wrote it.

Boolean term means the predicate is not trapped inside OR in a way that would make the implication unsafe. OR is the usual reason transitivity does not fire.

Predicate pushdown is the related idea of applying outer WHERE predicates inside a view or nested table expression before materialization. Merge makes pushdown natural; materialization without pushdown builds a huge work file and filters late.

Join elimination

IBM lists removal of table references for certain joins as a query transformation. If you join to a parent table that has a unique key, the foreign key already guarantees at most one parent, and you do not select any parent columns, the parent join can be redundant. Db2 may drop that table from the executable plan.

1
2
3
4
5
6
-- If DEPTNO is unique on DEPARTMENT and you only need EMP columns,
-- the join might be eliminated (RI / uniqueness permitting)
SELECT E.EMPNO, E.LASTNAME
  FROM HR.EMPLOYEE E
  INNER JOIN HR.DEPARTMENT D
    ON E.WORKDEPT = D.DEPTNO;

Do not design applications around join elimination. If you need the parent for a filter or for columns in the SELECT list, the table stays. Extra joins from generated SQL are the usual beneficiaries.

Subquery transformation

Db2 may:

• Convert a subquery to a join (IN / EXISTS / some quantified predicates)
• De-correlate a correlated subquery so it runs once
• Correlate a non-correlated subquery when probing per outer row is cheaper

1
2
3
4
5
6
SELECT E.EMPNO, E.LASTNAME
  FROM HR.EMPLOYEE E
 WHERE E.WORKDEPT IN (
       SELECT D.DEPTNO
         FROM HR.DEPARTMENT D
        WHERE D.LOCATION = 'CALIFORNIA');

After transformation you might see a join METHOD of 1/2/4 instead of a separate subquery query block—or QBLOCK_TYPE still showing SUBQUERY if the transform did not apply. Correlated EXISTS against a huge inner without an index on the correlation column is the classic timeout; rewriting to a join yourself is still fair game when EXPLAIN shows a poor subquery loop.

IBM notes that after both sets of predicates in a compound are considered, non-Boolean subquery predicates are often de-correlated or transformed into a join rather than applied in source-text order.

OR transformation

Disjunctions are hard. COL = 'A' OR COL = 'B' can become IN-list matching. COL = 'A' OR OTHERCOL = 1 may be rewritten toward a union of two index access paths (sometimes described as OR-to-UNION) so each leg can use a different index. If one leg is stage 2, the compound is stage 2 and those fancy matching tricks evaporate.

1
2
3
4
5
6
7
8
9
-- Both legs indexable: matching or multi-index OR is possible
SELECT EMPNO FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
    OR EMPNO = '000010';
 
-- One stage-2 leg poisons the OR
SELECT EMPNO FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
    OR YEAR(HIREDATE) = 2020;

UNION transformation

IBM documents simplification and removal of subselects in statements that contain UNION ALL. Empty or redundant legs can disappear. UNION ALL does not require a uniqueness sort; UNION does. Do not write UNION when UNION ALL is semantically enough—the transform will not always save you from the extra sort.

Views defined as UNION ALL of partitions (an old design before range-partitioned table spaces) are a case where the optimizer tries to prune legs using predicates on the discriminating column. Page-range screening on a true partitioned table is usually cleaner than a UNION ALL view, but you will still see UNION transformations in generated SQL.

Materialized query tables

A materialized query table (MQT) stores a precomputed query result.Automatic query rewrite can replace all or part of a user query with MQT references when:

• The query block is eligible (IBM: read-only dynamic, analyzed at query-block level)
• The block is not disqualified (examples IBM lists include outer join in that block, and certain fullselects in REFRESH TABLE or SET contexts)
• The MQT contains every column and row the query needs (Db2 assumes the MQT is current)
• The rewritten query has the same results and lower estimated cost

If several MQTs match, Db2 may use more than one or pick by internal rules. After rewrite, ordinary access path selection runs on the new statement. Stale MQT data yields approximate answers relative to the base tables—REFRESH TABLE (and your operational process) is what keeps them honest.

1
2
3
4
5
6
7
CREATE TABLE HR.EMP_DEPT_MQT AS
  (SELECT E.WORKDEPT, COUNT(*) AS EMPCNT, AVG(E.SALARY) AS AVGSAL
     FROM HR.EMPLOYEE E
    GROUP BY E.WORKDEPT)
  DATA INITIALLY DEFERRED REFRESH DEFERRED
  MAINTAINED BY SYSTEM
  ENABLE QUERY OPTIMIZATION;

ENABLE QUERY OPTIMIZATION (and subsystem/bind controls that allow rewrite) is what makes the MQT a rewrite candidate rather than just another table you query by name.

How to see what Db2 did

• DSN_PREDICAT_TABLE — each predicate, including generated ones
• DSN_FILTER_TABLE — STAGE of application
• PLAN_TABLE — extra or missing query blocks, METHOD, MATCHCOLS, TABLE_TYPE W for materialization
• Query tuning tools that pretty-print the transformed SQL text

If a generated predicate is missing, check OR, outer join, or a non-Boolean-term placement. If a subquery did not become a join, look at correlation, NULLs/NOT IN, and SELECT-list cardinality. If MQT rewrite did not fire, confirm dynamic SQL, ENABLE QUERY OPTIMIZATION, no blocking outer join in that block, and that the MQT’s SELECT list covers the query.

Explain It Like I'm Five

You say “get the red cars that are in the same box as the red cars.” A grown-up simplifies that to “get the red cars” (transitivity). Stage 1 is checking color while the cars are still in the box. Stage 2 is carrying every car to the living room and then checking. Join elimination is not walking to the empty extra box you did not need. Subquery-to-join is “stop asking the same question for every car; make one combined list.” An MQT is a photo of the toy shelf: faster to look at, but only right if someone took a new photo after you dumped more toys.

Exercises

1. EXPLAIN the join with DEPTNO = 'A00' only on the parent. In DSN_PREDICAT_TABLE, find the generated child local predicate (or explain why it is absent).
2. Rewrite YEAR(HIREDATE) = 2020 as a BETWEEN range and compare STAGE / MATCHCOLS.
3. Take an IN (subquery) and an equivalent INNER JOIN. Compare query block counts and METHOD.
4. OR an indexable predicate with a stage 2 predicate. Confirm the compound is stage 2.
5. List three reasons automatic MQT rewrite would skip a query block (outer join, missing columns in the MQT, estimated cost not better).