Mastering DB2 Execution Plans: Decoding Scans, Joins, and Performance Pitfalls

In database performance tuning, the SQL statement is often the source of bottlenecks. The execution plan describes how the optimizer will retrieve data and combine rows, so reading the plan is essential for precise tuning.

Two key phases of SQL execution

Data‑access method (scan) : table scan or index scan.

Join method : Nest Loop (NLJOIN), Merge Join (MSJOIN), Hash Join (HSJOIN), semi‑join, and the chosen join order.

Understanding these phases lets you locate the dominant cost in a DB2 plan quickly.

DB2 execution‑plan output (db2expln)

The db2expln utility (use the -g flag for a graphical diagram) returns three sections:

Original SQL text.

Detailed plan information – codepage, estimated cost, row count, and a line‑by‑line operator list.

Visual plan diagram (read from bottom‑right to top‑left, following the operator numbers).

Full‑table‑scan example

In the example the optimizer reports a cost of 124470 and expects a single row. The first lines show the codepage and the estimated cost; subsequent lines list each operator. The plan shows a TBSCAN (table scan) followed by a GROUP BY and result return.

Index‑scan example

The plan first reads an index, obtains the RID (row identifier), then fetches the remaining columns from the base table. After the fetch a GROUP BY is performed and the result is returned.

The index used contains four columns, but the query references only one. If a more covering index exists, the current index is sub‑optimal.

Join methods

Nest Loop (NLJOIN)

The optimizer picks an outer (driver) table, scans it once, and for each outer row scans the inner table. Approximate cost:

cost ≈ outer‑scan‑cost + (outer‑row‑count × inner‑scan‑cost)

NLJOIN is efficient when the outer table is small or highly selective.

Merge Join (MSJOIN)

Both inputs are sorted (or already ordered) and then merged in a linear pass. Approximate cost: cost ≈ outer‑scan + inner‑scan + sorting‑cost MSJOIN is preferred when the query needs ordered output or when both tables are large but have matching indexed join predicates.

Hash Join (HSJOIN)

The optimizer builds a hash table on the outer input using the join predicate, then probes the inner input. Approximate cost: cost ≈ outer‑scan + inner‑scan HSJOIN works well for large tables and multiple join predicates, but if the hash table overflows memory it can cause heavy I/O.

Semi‑join

Semi‑joins appear in EXISTS/ANY/ALL sub‑queries. The plan may not show an explicit join operator; instead the optimizer fetches rows from the inner query and stops scanning as soon as a match is found.

Join order selection

When more than two tables are involved, the optimizer chooses a pairwise join order. The estimated row count after each join influences the next choice. Inaccurate statistics can lead to a poor order and large performance penalties.

Practical commands and tips

Trace full execution : use db2trc or db2pd –stack to capture a trace and stack dump.

Rebind after statistics refresh : required for static SQL (plan stored at bind time); dynamic SQL is automatically re‑optimized.

Index selection : a single‑column index that matches the table’s clustering order can outperform a multi‑column index if the extra columns are not used.

Hash‑join cost reduction : create appropriate indexes on the probe (large) table to avoid full scans.

When NLJOIN is costly : ensure the driver table is the smallest possible; consider rewriting the query or adding indexes to enable MSJOIN or HSJOIN.

When MSJOIN is chosen : verify that both inputs are sorted or have matching index order; if sorting cost dominates, add indexes to eliminate the sort.

When HSJOIN shows high cost : check memory allocation for the sort heap; if overflow occurs, increase SORTHEAP or add indexes to reduce the need for hashing.

Key Q&A excerpts

How is the driver (outer) table chosen? The optimizer decides based on table size, predicate selectivity and available indexes; the SQL text only influences the logical join order.

What factors affect join‑method selection? Table cardinality, index availability, whether join columns are already sorted, and estimated row counts after each join.

Why can a plan change without statistics updates? Physical layout, partitioning, and runtime information (e.g., sort‑heap availability) are also considered during compilation.

Can a hash join be optimized when the probe table is large? Yes – create a suitable index on the probe table so the optimizer can use an index scan instead of a full table scan.

Is rebinding required after a statistics refresh? For static SQL objects (prepared statements, packages, stored procedures) you should rebind; dynamic SQL is re‑optimized automatically.

By dissecting DB2 execution plans—identifying scans, join operators, and join order—you can target the most expensive operations and apply concrete changes (index redesign, query rewrite, optimizer hints) to improve production workload performance.