Sort and materialization in DB2 for z/OS

What a sort is (and is not)

A sort is not the same thing as the SQL ORDER BY clause. ORDER BY is a request for a sequence in the result. A sort is the runtime work Db2 may do to produce that sequence—or to support grouping, uniqueness, a merge scan join, or a RID list for list prefetch. The only common SQL form that always needs uniqueness processing equivalent to a sort is UNION (not UNION ALL), because duplicates must be removed.

For ORDER BY, GROUP BY, DISTINCT, and many joins, the optimizer may choose a sort. It may also use an index that already returns keys in the needed order, or (for grouping) other techniques. The only reliable check is EXPLAIN.

1
2
3
4
SELECT WORKDEPT, LASTNAME, SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT IN ('A00', 'D11')
 ORDER BY LASTNAME;

If an index on LASTNAME (perhaps with WORKDEPT as a matching leading column, depending on the predicates and index design) can deliver rows in LASTNAME order, SORTC_ORDERBY can be N. If Db2 must gather qualifying rows first and then order them, you will see a sort indicator of Y and often a METHOD = 3 row.

PLAN_TABLE: how sorts are recorded

On z/OS, PLAN_TABLE carries eight yes/no sort flags. Names that start with SORTN_ apply to the new table in that step (the table being added). Names that start with SORTC_ apply to the composite table (the result accumulated so far).

METHOD = 3 is a dedicated sort step. That row does not access a new table; CREATOR is blank. METHOD = 2 (merge scan) or METHOD = 4 (hybrid join) can show SORTN_JOIN / SORTC_JOIN on the same row as the inner table access. A RID sort for ordinary list prefetch is often not spelled out as METHOD = 3; hybrid join is the case where SORTN_JOIN commonly means “sort the RID list,” and a highly clustered inner index can make SORTN_JOIN = N.

When you create the extra EXPLAIN tables, DSN_SORT_TABLE and DSN_SORTKEY_TABLE describe sort keys and sort type in more detail than the eight flags.

Why Db2 sorts

ORDER BY

ORDER BY asks for a sequence. If the chosen access path already produces that sequence (clustering index, matching index scan in the right direction), Db2 can skip the sort. DESC vs ASC, extra columns in the ORDER BY, and joins that scramble order are common reasons a “perfect-looking” index still does not avoid SORTC_ORDERBY.

GROUP BY and DISTINCT

GROUP BY needs equal keys together (or another grouping method). DISTINCT is uniqueness on the selected columns—SORTN_UNIQ or SORTC_UNIQ. A unique index that already guarantees uniqueness can make a DISTINCT cheap. SELECT DISTINCT * from a table with no unique constraint is a classic accidental sort.

UNION versus UNION ALL

UNION concatenates two result sets and removes duplicates, so a sort (or equivalent uniqueness step) is required. UNION ALL keeps duplicates and does not need that uniqueness sort. If the application does not care about duplicates, UNION ALL is the first rewrite to try.

Joins

A merge scan join (METHOD = 2) needs both inputs ordered on the join columns. If an index does not provide that order, Db2 sorts the new table, the composite, or both. A hybrid join (METHOD = 4) typically builds a RID list on the inner table and sorts those RIDs into page order so list prefetch can read data sequentially. Nested loop join (METHOD = 1) does not need join-column order, but SORTC_JOIN can still appear in some nested-loop cases documented on PLAN_TABLE.

1
2
3
4
5
6
-- Merge scan join often sorts unless indexes provide join order
SELECT E.EMPNO, D.DEPTNAME
  FROM HR.EMPLOYEE E
  INNER JOIN HR.DEPARTMENT D
    ON E.WORKDEPT = D.DEPTNO
 WHERE E.SALARY > 50000;

Where the sort runs: sort pool and work files

Db2 tries to sort in memory using the sort pool. When the ordered runs do not fit, it writes logical work files into table spaces in the work file database (DSNDB07 in a non-data-sharing subsystem). Each data sharing member has its own work file database.

IBM documents the sort in two phases. During the input phase, ordered sets of rows are written to work files. After all rows are inserted, Db2 merges those work files if needed into a single sorted result. Buffer pools back the logical work files; the number of sort work files is limited by buffer pool size, not by a tiny hard-coded file count from ancient versions.

Allocation prefers certain work file table spaces (for example Db2-managed segmented non-UTS spaces with SECQTY 0). If preferred spaces are unavailable, subsystem parameter WFDBSEP decides whether the sort fails (YES) or Db2 picks another space (NO). Record length also matters: IBM prefers 32 KB pages when data + key + prefix is greater than 100 bytes, and 4 KB pages when the record is 100 bytes or smaller.

Undersized, fragmented, or badly pooled work files show up as sort failures, SQLCODE -904 style resource problems, or queries that burn elapsed time in work file getpages. Temporary tables compete for the same database. Capacity planning for DSNDB07 is part of sort tuning, not an afterthought.

What materialization means

Materialization means: evaluate this piece of the query into a temporary result, park it in a work file, then read that work file in a later step. Merge (the opposite) means: fold the view, nested table expression, or CTE into the outer query so Db2 never builds that extra result.

IBM states that merge is generally more efficient than materialization because predicates from the outer query can be applied earlier (predicate pushdown) and extra copies are avoided. Materialization is required when the inner query block must be fully evaluated first—classic triggers are GROUP BY, DISTINCT, aggregate functions, some UNION operations, and some outer join combinations with views or nested table expressions.

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW HR.DEPT_AVG (WORKDEPT, AVGSAL) AS
  SELECT WORKDEPT, AVG(SALARY)
    FROM HR.EMPLOYEE
   GROUP BY WORKDEPT;
 
-- GROUP BY inside the view typically forces materialization
SELECT E.EMPNO, E.SALARY, V.AVGSAL
  FROM HR.EMPLOYEE E
  INNER JOIN HR.DEPT_AVG V
    ON E.WORKDEPT = V.WORKDEPT
 WHERE E.SALARY > V.AVGSAL;

EXPLAIN clues for materialization:

• TABLE_TYPE = W — intermediate result materialized to a work file
• TNAME = DSNWFQB(n) — work file produced by query block n
• METHOD = 3 — a sort of that (or another) result
• ACCESSTYPE = T — sparse index on a materialized work file (star join / in-memory work file access)
• Nested table expression or non-materialized view: TABLE_TYPE Q
• Non-recursive CTE: TABLE_TYPE C; recursive CTE: R

Outer joins combined with other joins, views, or nested table expressions sometimes force materialization of a composite even when you did not write GROUP BY. IBM documents that case specifically: TABLE_TYPE W and TNAME DSNWFQB(xx).

When merge happens

If the view or nested table expression is a simple SELECT without aggregation, DISTINCT, or grouping, Db2 typically merges it: column expressions are substituted into the outer statement. That is good when the outer WHERE can then become a matching index predicate on the base table. It can be less good when a costly expression is referenced many times after merge (the expression may be evaluated once per reference). In rare nested cases people force materialization on purpose; do not do that as a first habit.

CTEs

A common table expression can be merged, materialized, or (if recursive) iterated. Recursion always implies a working result. A CTE referenced twice may be materialized so Db2 does not run the inner SELECT twice—or it may be merged twice. EXPLAIN, not guesswork, tells you which happened.

Sparse indexes on work files

After materialization, Db2 may build a sparse index (in-memory work file index) so later nested-loop probes do not scan the whole work file. PLAN_TABLE ACCESSTYPE T is the usual flag. Sparse indexes help when a small materialized inner is probed many times. They still cost memory and a build step; they are not a substitute for a real index on a base table.

Tuning sorts and materialization

• Do not sort if the index already orders the rows — design clustering and indexes to match high-volume ORDER BY / join columns
• Prefer UNION ALL when duplicate elimination is unnecessary
• Push predicates into views and nested table expressions so materialization, if required, builds a smaller work file
• Avoid SELECT DISTINCT * unless you truly need uniqueness
• Size DSNDB07 and its buffer pools for peak concurrent sorts and declared global temporary tables (32 KB work file spaces are required for DGTTs)
• Watch sort CPU and work file I/O in accounting traces; a “simple” report query can be 90% sort
• Keep statistics current — bad cardinality estimates cause unnecessary sorts (huge join composites) or missing sorts the optimizer should have planned

Materialization is not always a failure. IBM notes that with deep nesting and repeated references to generated columns, materialize-once can beat merge-and-recompute. Your job is to see the work file in EXPLAIN and decide whether the copy is justified.

Explain It Like I'm Five

Imagine you have a big box of toy cars. A sort is lining them up by color before you hand them out. If they were already stored in color order on the shelf (an index), you skip the lining-up. Materialization is dumping a mixed pile onto a side table, finishing that pile, then using the side table for the next game. The side table is a work file. If the pile is huge, it spills off the desk onto the floor (DSNDB07). UNION is “put two piles together and throw away extra copies of the same car.” UNION ALL is “just dump both piles together.”

Exercises

1. EXPLAIN a query with ORDER BY on a column that has a clustering index and one that does not. Compare SORTC_ORDERBY.
2. Rewrite a UNION that does not need duplicate removal as UNION ALL and compare METHOD = 3 rows.
3. Create a view with GROUP BY and SELECT from it with a selective WHERE on the base key. Confirm TABLE_TYPE = W and discuss whether rewriting as a nested table expression with a correlated predicate would shrink the work file.
4. Find your subsystem work file database name (DSNDB07 or a data-sharing member name) and list its table space page sizes.
5. Identify whether a slow query’s accounting data shows sort CPU and work file getpages rather than index I/O.