DB2 EXPLAIN tables: PLAN_TABLE and friends

What EXPLAIN tables are

EXPLAIN tables are not catalog tables. You (or a tool, or installation job) create them with CREATE TABLE. Db2 inserts rows when you invoke EXPLAIN: the SQL statement EXPLAIN, BIND or REBIND with EXPLAIN(YES) or EXPLAIN(ONLY), SET CURRENT EXPLAIN MODE for dynamic SQL, EXPLAIN PACKAGE, EXPLAIN STMTCACHE, or optimizer products that call the same services.

A statement is explainable if it is SELECT, MERGE, TRUNCATE, INSERT, or the searched form of UPDATE or DELETE. One row in PLAN_TABLE describes one step of the plan. A simple single-table SELECT might be one row. A three-table join with a sort can be several rows. Referential-constraint enforcement steps are not included.

Only PLAN_TABLE is required for basic EXPLAIN. Every other table on this page is optional output—or, in the case of DSN_VIRTUAL_INDEXES, an input table you fill before EXPLAIN so the optimizer can pretend an index exists or is gone.

Creating and upgrading the tables

IBM ships sample CREATE TABLE statements in prefix.SDSNSAMP(DSNTESC). Copy them, change the qualifier to your SQLID (or SYSIBM for a shared set), and run them. Prefer the current-release format. On recent Db2, EXPLAIN supports only current and previous-release formats; older formats fail with SQLCODE -20008. Deprecated previous-release format can warn with +20520.

• ADMIN_EXPLAIN_MAINT stored procedure — create, upgrade, or maintain EXPLAIN tables for a qualifier
• DSNTIJXA (REXX DSNTXTA) — upgrade existing tables to the current format during or after migration
• DSNTIJSG / installation jobs — often create SYSIBM-qualified tables for tools

Multiple users can share one PLAN_TABLE, but personal tables are safer for statement-level hints, virtual indexes, and not overwriting someone else's QUERYNO. Always filter by QUERYNO and EXPLAIN_TIME (or TIMESTAMP) so you do not mix yesterday's explain with today's.

1
2
3
4
5
6
7
8
9
10
11
12
EXPLAIN PLAN SET QUERYNO = 1001 FOR
  SELECT E.EMPNO, D.DEPTNAME
    FROM HR.EMPLOYEE E
    JOIN HR.DEPT D
      ON E.WORKDEPT = D.DEPTNO
   WHERE E.WORKDEPT = 'A00';
 
SELECT QUERYNO, QBLOCKNO, PLANNO, METHOD, TNAME,
       ACCESSTYPE, MATCHCOLS, ACCESSNAME, INDEXONLY, PREFETCH
  FROM PLAN_TABLE
 WHERE QUERYNO = 1001
 ORDER BY QBLOCKNO, PLANNO, MIXOPSEQ;

PLAN_TABLE

PLAN_TABLE is the access-path diary. Read it in order of QBLOCKNO, PLANNO, then MIXOPSEQ (used when multiple-index access has several sub-steps). The first table in a join has METHOD = 0. Later rows tell you how the next table was joined and how that table was accessed.

METHOD values

• 0 — first table accessed, continuation of the previous table, or not used for a new table
• 1 — nested loop join: for each qualifying outer row, find matching inner rows
• 2 — merge scan join: both sides scanned in join-column order
• 3 — a sort for ORDER BY, GROUP BY, DISTINCT, UNION, or certain predicates; does not access a new table
• 4 — hybrid join (RID list + list prefetch on the inner table)

ACCESSTYPE values you will see constantly

• I — index access (name in ACCESSNAME; MATCHCOLS tells how many key columns matched)
• I1 — one-fetch index (for example MIN/MAX on a leading indexed column)
• N — index access with an IN-list matching predicate
• R — table space (relational) scan
• M — multiple-index access; following rows use MX / MI / MU
• MX — index scan that feeds a RID list in multiple-index access
• MI — intersection (AND) of RID lists
• MU — union (OR) of RID lists

MATCHCOLS = 0 with ACCESSTYPE I is a nonmatching index scan: Db2 walks leaf pages without using the tree to start at a key. MATCHCOLS > 0 is a matching index scan. INDEXONLY = Y means the step does not need data pages. PREFETCH of S, L, or D is sequential, list, or dynamic prefetch. HINT_USED can show APREUSE or an optimization hint name when reuse or hints applied. REMARKS often holds APCOMPARE mismatch text after bind/rebind comparison.

Other useful columns: JOIN_TYPE (inner vs outer), PAGE_RANGE (partition pruning), PARALLELISM_MODE, SORTN_* and SORTC_* flags for sorts on the new or composite table, TSLOCKMODE, PRIMARY_ACCESSTYPE, and identifiers such as PROGNAME, COLLID, VERSION, SECTNOI so you can match a package statement.

DSN_STATEMNT_TABLE

The statement table holds one (typical) row per explained statement with the optimizer's estimated cost, not measured runtime. Join it to PLAN_TABLE on QUERYNO and EXPLAIN_TIME.

• PROCMS — estimated processor cost in milliseconds
• PROCSU — estimated processor cost in service units
• TOTAL_COST — overall cost number the optimizer compared
• COST_CATEGORY — A means the estimate used fairly complete information; B means something was missing (host variables without REOPT, missing stats, and similar). REASON explains why it is B
• STMT_TYPE — SELECT, INSERT, and so on
• STMT_TEXT — statement text when captured

Cost category B is not automatically a bad plan. It means “do not treat PROCMS as a precise prediction.” Compare category A costs across two explains of the same SQL after RUNSTATS more carefully than category B numbers from two different bind options.

1
2
3
4
5
6
7
8
9
SELECT A.QBLOCKNO, A.PLANNO, A.TNAME, A.METHOD,
       A.ACCESSTYPE, A.MATCHCOLS, A.ACCESSNAME,
       B.PROCMS, B.PROCSU, B.COST_CATEGORY, B.REASON
  FROM PLAN_TABLE A
  JOIN DSN_STATEMNT_TABLE B
    ON A.QUERYNO = B.QUERYNO
   AND A.EXPLAIN_TIME = B.EXPLAIN_TIME
 WHERE A.QUERYNO = 1001
 ORDER BY A.QBLOCKNO, A.PLANNO, A.MIXOPSEQ;

DSN_FUNCTION_TABLE

When the statement calls a user-defined function, Db2 records how it resolved the function: schema, specific name, function type, and related identifiers. Overloading and PATH matter here. If two functions share a name, this table tells you which specific function the binder picked. Built-in functions are not the main audience; you use this table when a UDF is in the SQL and you need to know which implementation ran in the plan.

DSN_STRUCT_TABLE

The structure table describes query blocks: parent and child blocks, block type (the outer SELECT, a subquery, UNION leg, and similar), and how blocks nest. PLAN_TABLE already has QBLOCKNO; DSN_STRUCT_TABLE is the map of those numbers. Use it when a statement has several subqueries and you cannot tell which PLAN_TABLE rows belong to which logical SELECT.

DSN_SORT_TABLE

Sorts show up as METHOD = 3 rows and as SORTN_* / SORTC_* flags on PLAN_TABLE. The sort table (with the related sort-key table in current formats) records why a sort exists and which keys it uses: join order, GROUP BY, ORDER BY, DISTINCT, uniqueness. A sort is not automatically a problem—merge join needs order—but unexpected sorts on huge composites are a classic elapsed-time surprise. Pair this table with work-file and sort-pool monitoring when the plan looks “fine” but the job sorts for minutes.

DSN_PREDICAT_TABLE

One row per predicate (not including predicates inside CASE WHEN). You see the predicate text, whether it is a boolean term, estimated filter factor, and often the stage (index matching vs residual). This is the table to open when MATCHCOLS is lower than you expected: the predicate you thought was matching may be stage 2, not a boolean term, or applied after a range predicate stopped matching.

DSN_PREDICAT_TABLE is also an input for BIND QUERY when you override predicate selectivities. That is an advanced hint technique: you are telling the optimizer “this predicate is more or less selective than catalog stats imply.”

DSN_DETCOST_TABLE

The detailed cost table breaks the optimizer's mini-plan estimates into finer pieces: I/O and CPU cost components, cardinalities at each step, and related numbers used while comparing alternative plans. When two indexes look similar in PLAN_TABLE, DETCOST is where you see why one mini-plan won. It is dense; start with PLAN_TABLE and DSN_STATEMNT_TABLE, then drop into DETCOST for a stubborn statement.

DSN_FILTER_TABLE

Closely related to the predicate table, DSN_FILTER_TABLE records how predicates are used as filters during processing: order of application, stage, and filter factor as applied. Use it with DSN_PREDICAT_TABLE when you need the evaluation sequence, not just the list of predicates. Index screening versus data screening shows up conceptually here: some predicates filter index entries; others wait until the data row is in hand.

DSN_COLDIST_TABLE

Catalog column distribution lives in SYSIBM.SYSCOLDIST. The EXPLAIN DSN_COLDIST_TABLE captures non-uniform column group statistics that Db2 obtained dynamically (for example from non-index leaf pages) while considering the statement. It helps explain a plan that suddenly “knew” a value was rare or common even when your last RUNSTATS was thin. Do not confuse it with the catalog table of the same idea; this one is explain output for that statement's optimization.

DSN_VIRTUAL_INDEXES

DSN_VIRTUAL_INDEXES is an input table. You INSERT a row that describes an index you might create or drop: table name, key columns, unique, clustering, and a mode such as create versus drop. Then you EXPLAIN. The optimizer considers that virtual index (or ignores a real one you virtually dropped) without DDL and without changing production packages.

This is how you answer “would an index on WORKDEPT, LASTNAME change this path?” before you spend REORG and DASD. Enable only the virtual rows you mean for this experiment. Shared virtual-index tables in a busy shop will mix experiments; use your own qualifier.

DSN_QUERYINFO_TABLE

The query information table holds extra facts that do not fit neatly in PLAN_TABLE: whether the statement is a candidate for an accelerator(IDAA) and why it was or was not offloaded, XML query information, and other query-level reason codes. If your shop uses query acceleration, this table is where “why didn't it go to the accelerator?” often lives. For ordinary OLTP packages you may ignore it until a statement is supposed to offload.

Current Db2 releases also define additional EXPLAIN and input tables (page-range, parallel groups, predicate selectivity overrides, statement cache, and others). If a column your tool mentions is missing, your tables are probably down-level—upgrade with ADMIN_EXPLAIN_MAINT or DSNTIJXA rather than adding one column by hand.

A practical read order

1. Filter PLAN_TABLE by QUERYNO and the latest EXPLAIN_TIME. Order by QBLOCKNO, PLANNO, MIXOPSEQ.
2. Note METHOD (join sequence), ACCESSTYPE, MATCHCOLS, ACCESSNAME, INDEXONLY, PREFETCH.
3. Read DSN_STATEMNT_TABLE for PROCMS, PROCSU, COST_CATEGORY.
4. If MATCHCOLS or stage surprises you, read DSN_PREDICAT_TABLE and DSN_FILTER_TABLE.
5. If a sort or work-file storm is the symptom, read DSN_SORT_TABLE and METHOD 3 rows.
6. Use DSN_STRUCT_TABLE when QBLOCKNO values are not obvious.
7. Use DSN_VIRTUAL_INDEXES only as a what-if input, then EXPLAIN again and compare.

Explain It Like I'm Five

Imagine Db2 is a librarian who must fetch books. PLAN_TABLE is the sticky note that says which shelf to walk, whether to use the card catalog (an index), and in what order to visit rooms (joins). DSN_STATEMNT_TABLE is the sticky note that says “this should take about this much time.” The predicate table lists the rules on the library card (“only books about cats written after 2010”). Virtual indexes are pretend extra card catalogs you draw on paper to see if building a real one would help—without actually building the wooden drawers yet.

Exercises

1. Find whether you already have a PLAN_TABLE (SELECT from SYSIBM.SYSTABLES where NAME = 'PLAN_TABLE'). Note the CREATOR.
2. Run EXPLAIN PLAN SET QUERYNO = 1 FOR a simple SELECT from SYSIBM.SYSDUMMY1 and list ACCESSTYPE and METHOD.
3. Join PLAN_TABLE to DSN_STATEMNT_TABLE for that QUERYNO. If the statement table is empty, explain why (table missing vs wrong qualifier vs wrong QUERYNO).
4. EXPLAIN a two-table join. Identify which row is METHOD 0 and which is METHOD 1, 2, or 4.
5. Read SDSNSAMP(DSNTESC) or ask how your shop runs ADMIN_EXPLAIN_MAINT, and list three companion tables you do not have yet.