Indexable vs stage 2 predicates in DB2

Why stage matters

Imagine 10 million rows and a predicate that keeps 100. If the predicate is indexable and a good index exists, Db2 may touch a tiny slice of the index and a handful of data pages. If it is only stage 1, the Data Manager can still discard rows while reading pages—cheaper than shipping every row upward. If it is stage 2, rows flow to RDS first; CPU and getpages explode when nothing else filters early.

IBM’s processing order (simplified):

Vocabulary you will hear from DBAs:

• Indexable — eligible to match index entries (matching index scan)
• Matching predicate — an indexable predicate the optimizer actually used on leading index columns
• Stage 1 / sargable — Data Manager can evaluate it
• Stage 2 / residual / nonsargable — RDS evaluates after the row is returned
• Index screening — stage 1 predicates on index columns that are not matching (still applied to index entries)

IBM’s rule of thumb in older wording: all indexable predicates are stage 1. The predicate C1 LIKE '%BC' is stage 1 but not indexable. Later manuals also list a small class that is indexable but not stage 1—notably XMLEXISTS.

Indexable vs non-indexable

An indexable predicate can match index entries. It might or might not become matching, depending on available indexes and the chosen access path.

1
2
3
4
5
6
7
-- Indexable: can match an index on LASTNAME
SELECT * FROM DSN8C10.EMP
 WHERE LASTNAME = 'SMITH';
 
-- Not indexable: <> is not a matching predicate
SELECT * FROM DSN8C10.EMP
 WHERE SEX <> 'F';

Typical indexable forms (when types/lengths cooperate):

• COL = value, COL op value for <, <=, >, >=
• COL BETWEEN, COL IN (list) under documented IN-list rules
• COL LIKE 'ABC%' — pattern does not start with % or _
• COL IS NULL
• SUBSTR(COL, 1, n) op value — start must be 1
• YEAR(COL) / DATE(COL) in documented indexable cases

Non-indexable classics: <> / NOT, leading-wildcard LIKE, many expressions that wrap the column (COL concatenated, COL + 0, scalar functions not in the indexable list). DECFLOAT columns or constants can force a predicate that “looks” indexable to be treated as not stage 1. Over-long string comparisons can also lose stage 1.

Indexable is not a guarantee. EXPLAIN’s PLAN_TABLE (METHOD, ACCESSTYPE, MATCHCOLS) shows whether matching happened.

Stage 1 vs stage 2

Stage depends on syntax, data types and lengths, join sequence, and whether the predicate is applied before or after a join. A predicate evaluated after a join is always stage 2. The same ON/WHERE text can be stage 1 if that table is first in the join sequence and stage 2 if it is the inner table after the join.

Outer join ON clauses: for a full outer join, ON is evaluated like a stage 2 predicate during the join. Predicates in a table expression can run before the join and stay stage 1—push filters into a nested table expression when you need early stage 1 on one side of a full join.

1
2
3
4
5
6
7
-- EDLEVEL > 100 can be stage 1 inside the table expression
-- before the full join
SELECT *
  FROM (SELECT * FROM DSN8C10.EMP
         WHERE EDLEVEL > 100) AS X
  FULL JOIN DSN8C10.DEPT
    ON X.WORKDEPT = DSN8C10.DEPT.DEPTNO;

Boolean predicates (AND / OR / NOT)

Compound predicates inherit stage from how they are combined.

• AND — each simple predicate keeps its own stage. Matching can use several leading index columns (COL1 = ? AND COL2 = ?). A stage 2 AND-ed with a highly filtering indexable predicate is often acceptable: the indexable part does the heavy reduction first.
• OR — IBM: the compound has the same characteristics as the simple predicate evaluated latest. Two indexable ORs stay indexable. A stage 1 OR a stage 2 becomes stage 2. That is the trap: one residual disjunct poisons the OR.
• NOT — often destroys indexability (NOT BETWEEN, NOT IN, NOT LIKE). NOT XMLEXISTS is listed as stage 2.

1
2
3
4
5
6
7
8
-- Still indexable if both sides are indexable
WHERE WORKDEPT = 'A00' OR WORKDEPT = 'B01'
-- equivalent and often cleaner:
WHERE WORKDEPT IN ('A00', 'B01')
 
-- Stage 2 OR: YEAR(...) residual poisons the whole OR
WHERE WORKDEPT = 'A00'
   OR YEAR(HIREDATE) = 2010

Rewrite poisoned ORs as UNION of two queries, or replace the residual with a range so both legs stay indexable.

Functions on columns: YEAR, SUBSTR, CASE

Wrapping a column in a function is the usual way a beginner accidentally writes stage 2 SQL. Prefer predicates that leave the column bare on the left.

1
2
3
4
5
6
7
8
9
-- Weaker (often stage 2 / non-matching) unless rewritten
WHERE YEAR(HIREDATE) = 2010
 
-- Stronger: indexable range on HIREDATE
WHERE HIREDATE BETWEEN '2010-01-01' AND '2010-12-31'
 
-- SUBSTR start 1 can be indexable; LIKE is clearer
WHERE SUBSTR(LASTNAME, 1, 3) = 'SMI'
WHERE LASTNAME LIKE 'SMI%'

IBM documents that predicates may be indexable if the expression is DATE, YEAR, or SUBSTR with start 1. The optimizer may also rewrite YEAR(date) = n into a BETWEEN. Do not rely on rewrite for every function: UPPER(LASTNAME) = 'SMITH' is a common residual unless you have expression-based indexes (limited on z/OS compared with LUW) or you store a search column already folded.

CASE in a WHERE clause is listed among stage 2 predicates. Use CASE in the SELECT list; keep WHERE as simple comparisons when you care about matching.

XML predicates

XMLEXISTS is the SQL/XML predicate that tests whether an XQuery/XPath expression returns a non-empty sequence. IBM’s summary classifies XMLEXISTS as indexable but not stage 1: it can be evaluated during XML index access, but it is not an ordinary Data Manager stage 1 predicate on a relational column. NOT XMLEXISTS is stage 2.

1
2
3
4
5
6
7
8
-- Prefer simple XPath in XMLEXISTS so an XML index can match
WHERE XMLEXISTS(
        '$d/order/item[@sku="W42"]'
        PASSING ORDER_DOC AS "d"
      )
 
-- FLWOR in XMLEXISTS is generally not indexable
-- Keep FLWOR in XMLQUERY after rows already qualified

XML indexes are defined with an XMLPATTERN. The XMLEXISTS path needs to be compatible with that pattern (and namespaces). Combine a relational indexable predicate (ORDER_ID = ?) with XMLEXISTS so the relational filter runs first when it is selective.

Classification cheat sheet

Always confirm with EXPLAIN and, when available, DSN_FILTER_TABLE / predicate instrumentation. Join sequence and data types can move a “usually stage 1” predicate to stage 2.

How to write friendlier predicates

• Put the column alone on one side; put functions and expressions on the literal/host-variable side when possible
• Replace YEAR/MONTH extractions with date ranges
• Avoid leading % in LIKE; consider a design that stores a reversed or tokenized search column if you must search suffixes
• Do not OR a residual with an indexable predicate if you need matching on that OR
• Keep XMLEXISTS paths index-shaped; filter with relational keys too
• A leftover stage 2 predicate is fine when another predicate already reduces to a handful of rows

Explain It Like I'm Five

The library has a card catalog (the index) and a huge stack of books (the table). An indexable question is one the catalog can answer: “cards that start with SMI.” A stage 1 question can be checked while pulling a book off the shelf. A stage 2 question is “open every book and see if chapter seven mentions cats”—you already carried the book to the desk. LIKE '%ITH' is “titles that end with ITH”: the catalog is in A–Z order, so you still scan. OR-ing a hard question with an easy one is like saying “catalog SMI or books that mention cats”—the librarian cannot use the catalog for the whole request.

Exercises

1. Rewrite YEAR(HIREDATE) = 2010 as an indexable range on HIREDATE.
2. Explain why LASTNAME LIKE 'SMI%' and LASTNAME LIKE '%ITH' differ for matching index access.
3. An index exists on (WORKDEPT, LASTNAME). Which matching columns can WHERE LASTNAME = 'SMITH' use by itself?
4. Why can OR of WORKDEPT = 'A00' and a CASE expression in WHERE become stage 2?
5. Is XMLEXISTS stage 1, stage 2, or indexable-but-not-stage-1 according to IBM’s summary? What about NOT XMLEXISTS?