Mastering DB2 Indexes, Predicates, and Runtime Architecture

Speaker Introduction

IBM Database Solutions Architect Ma Yuan, based in IBM China Labs, supports DB2 partners and has extensive experience with DB2 for z/OS and DB2 tooling.

DB2 Index Structure

DB2 indexes are organized as pages forming a B+‑tree: a root node, intermediate (non‑leaf) nodes, and leaf nodes. The leaf nodes contain ordered key values with associated RIDs. A simplified triangular diagram often represents the ordered keys.

Predicate Types

Two key predicate concepts are introduced:

Indexable : a predicate that can determine a continuous range on an index, enabling efficient range scans.

Sargable (Search ARGument ABLE) : a predicate that cannot define a single continuous range on the index; it may require multiple or disjoint ranges.

Static predicate analysis focuses only on these properties, independent of SQL placement, index availability, or optimizer decisions.

DB2 Runtime Architecture

The DB2 runtime consists of several modules that a query traverses from bottom to top:

Buffer Pool (BM) – manages data and index pages in memory.

Index Manager (IM) – handles index page retrieval and applies predicates.

Data Manager (DM) – performs table or index scans based on the required access method.

Relational Data Store (RDS) – final processing, including residual predicates and result set assembly.

Index Scan Strategies

IM can perform two scan types:

Matching Scan : scans a continuous range of index keys from the root to leaf, returning matching rows directly.

Screening Scan : scans a broader range (potentially multiple disjoint segments) and then applies the predicate to each index entry, filtering results.

When an index has multiple key columns and the predicate combines several Boolean conditions, matching and screening can be combined. Example diagrams illustrate how different predicate combinations affect scan behavior.

Practical Recommendations

Prefer writing Indexable predicates.

Use the equality operator ( =) whenever possible.

Place columns used in equality predicates among the leading keys of the index.

Q&A Highlights

Q1: Can DB2 skip the first column of a composite index?

A: Not for a matching scan; a screening scan can, because it filters after retrieving index entries.

Q2: Is there an optimal order for predicates?

A: Local predicates (those referencing only the current table) are evaluated first, followed by subqueries, joins, etc. Within local predicates, matching scans run before screening scans.

Q3: How to rewrite non‑optimal predicates without changing the schema?

A: Replace function‑based predicates such as Year(DateCol) = 2005 with range predicates like DateCol BETWEEN '2005-01-01' AND '2005-12-31' to make them Indexable.

Q4: If only index 1 exists and the query is c2 = 50 AND c3 LIKE '%PAM%', will the index be used?

A: It depends on the optimizer; if used, the scan will be a screening scan.

Q5: Does DB2 support index compression and columnar storage?

A: Yes; DB2 BLU provides both compression and columnar storage capabilities.