Explain EXPLAIN and Access Path Analysis in IBM Mainframes

EXPLAIN shows which access path the optimiser chose. It is the first tool for any slow DB2 query. The results land in PLAN_TABLE, which you then query. The

All topics

35 / 60

DB2advanced

EXPLAIN and Access Path Analysis

Use EXPLAIN to see exactly which access path DB2's optimizer chose for a query and evaluate whether it's actually efficient.

EXPLAIN shows which access path the optimiser chose. It is the first tool for any slow DB2 query.

EXPLAIN is like asking a GPS app to show its planned route and reasoning before you actually start driving — you can see whether it's taking the highway (an efficient index) or routing you through every side street in town (a full table space scan) before committing any time to the trip.

Key Concepts

    EXEC SQL EXPLAIN PLAN SET QUERYNO = 1 FOR
         SELECT ... END-EXEC.

The results land in PLAN_TABLE, which you then query.

PLAN_TABLE

The columns that matter.

    ACCESSTYPE   how the table is reached
    ACCESSNAME   which index, if any
    MATCHCOLS    how many index columns the predicates matched
    METHOD       the join method
    SORTN_*, SORTC_*   whether a sort is needed

ACCESSTYPE first.

    I    index used
    I1   one-fetch index scan -- very efficient
    N    index scan for an IN list
    R    table space scan -- every row read
    MX   multiple index access

ACCESSTYPE

ACCESSTYPE = R on a large table with a selective filter is the classic finding. Either no usable index exists, or the predicate defeats the one that does.

ACCESSTYPE = R

MATCHCOLS is the column people overlook, and it is often where the real problem is:

MATCHCOLS

    index on (REGION, STATUS, CREATED_DATE)
    WHERE REGION = ? AND STATUS = ?          -> MATCHCOLS = 2
    WHERE REGION = ? AND CREATED_DATE = ?    -> MATCHCOLS = 1

In the second case the index is used — ACCESSTYPE says I, which looks fine — but only the first column narrows the search. Everything matching that region is read and then filtered.

ACCESSTYPEI

So an index being used is not the same as an index being used well. MATCHCOLS is what tells you.

MATCHCOLS

What defeats index matching. The same rules as any database:

    WHERE YEAR(CREATED_DATE) = 2026      function on the column
    WHERE ACCT_NUM = 12345               numeric literal, character column
    WHERE NAME LIKE '%son'               leading wildcard

The workflow. Run EXPLAIN. Compare ACCESSTYPE and MATCHCOLS against the predicates and the available indexes. Then decide whether the plan is actually wrong.

EXPLAINACCESSTYPEMATCHCOLS

That last step matters. A table space scan on a small table is correct, and so is one on a query returning most of the rows. Adding an index because a scan looked alarming costs write performance for nothing.

Check the statistics too. A plan that looks unreasonable is often a plan built from stale statistics — so RUNSTATS and REBIND before redesigning the query.

RUNSTATSREBIND

What the interviewer is probing.1. "What does EXPLAIN tell you?" Probing: the purpose. Stalls: "If it is slow." Moves up: the access path DB2 intends — which index, the join method and order, and whether a sort or table space scan is involved.

2. "What do you look for first?" Probing: the method. Stalls: "The cost." Moves up: a table space scan where you expected matching index access, and the MATCHCOLS value showing how much of the index was actually usable.

3. "Why might an index not be used?" Probing: the causes. Stalls: "A bug." Moves up: a function or mismatched data type on the column, poor statistics, or low selectivity making a scan cheaper.

4. "What must be true for EXPLAIN to be trustworthy?" Probing: the precondition. Stalls: "Nothing." Moves up: current RUNSTATS on representative data volumes — EXPLAIN on an empty test table tells you nothing about production.