DB2 EXPLAIN overview

What EXPLAIN is

The SQL EXPLAIN statement captures access-path information for an explainable statement and inserts rows into EXPLAIN tables. A statement is explainable if it is SELECT, MERGE, TRUNCATE, or INSERT, or the searched form of UPDATE or DELETE. PLAN_TABLE describes each step. If DSN_STATEMNT_TABLE exists, Db2 adds an estimated cost. If DSN_FUNCTION_TABLE exists, it records how user-defined functions were resolved. Other DSN_* tables fill when present.

EXPLAIN does not include referential-constraint enforcement steps. For system-period temporal tables, output can show both the base table and the history table when both are needed. SQL against a declared temporary table must be explained in the same application process that declared the table; a static EXPLAIN of that SQL is incrementally bound at run time, not at BIND.

Create the tables first

PLAN_TABLE must exist before EXPLAIN or BIND EXPLAIN(YES) can succeed. It must be a base table, optionally with aliases named PLAN_TABLE for other authids. It cannot be a view or a synonym. The owner (or alias owner) needs SELECT and INSERT.

• Sample DDL: member DSNTESC in prefix.SDSNSAMP. Change the qualifier to your TSO id or a shared explainer id.
• Or call ADMIN_EXPLAIN_MAINT to create or upgrade tables to the current release format.

Only PLAN_TABLE is required for basic EXPLAIN. Create the others when you need predicate, filter, sort, or detailed-cost data. After migration, upgrade old-format tables; Db2 12+ issues SQLCODE -20520 (and may skip EXPLAIN) if the tables are still at a retired format.

How to run EXPLAIN

SQL EXPLAIN PLAN FOR

1
2
3
4
5
EXPLAIN PLAN SET QUERYNO = 1001
  FOR
  SELECT EMPNO, LASTNAME
    FROM HR.EMPLOYEE
   WHERE WORKDEPT = 'A00';

PLAN and ALL have the same effect. SET QUERYNO = integer tags every inserted row so you can find them later. If you omit QUERYNO, an embedded EXPLAIN uses the precompiler statement number; a dynamic EXPLAIN gets a Db2-assigned number. The FOR clause must be statement text—not a statement-name or host-variable. To explain dynamic SQL, PREPARE the entire EXPLAIN statement. The explained SQL must not contain its own QUERYNO clause. Host variables are allowed in embedded EXPLAIN; parameter markers are allowed when EXPLAIN is dynamically prepared.

Authorization: the privileges of the explained statement, plus PLAN_TABLE ownership or alias with SELECT and INSERT. EXPLAIN privilege, SQLADM, or system DBADM can also satisfy PLAN/ALL. STMTCACHE and PACKAGE forms need SQLADM, SYSADM, or related authorities (STMTCACHE ALL is SQLADM / system DBADM / SYSADM; without that, you only see statements with your own authid).

EXPLAIN STMTCACHE

Extracts paths that are already in the dynamic statement cache on this data-sharing member. No new optimization. Variants:

• ALL — one row per cached statement in DSN_STATEMENT_CACHE_TABLE (identifying data and execution statistics). Other EXPLAIN tables are not filled.
• STMTID — PLAN_TABLE, DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE, and the cache table for one statement id (from IFCID 316/124). QUERYNO becomes that id.
• STMTTOKEN — same tables for statements tagged with an application token (RRSAF SET_ID / sqleseti).

EXPLAIN PACKAGE

Copies existing static paths from a bound package into the current user’s PLAN_TABLE. Other EXPLAIN tables are not populated. Scope by COLLECTION, PACKAGE, optional VERSION, and COPY CURRENT / PREVIOUS / ORIGINAL (PLANMGMT copies). HINT_USED is set to EXPLAIN PACKAGE: copy-id. This is how you document what production is running without rebinding.

EXPLAIN STABILIZED DYNAMIC QUERY

From function level 500, extracts cataloged stabilized dynamic SQL into PLAN_TABLE, DSN_STATEMNT_TABLE, and DSN_FUNCTION_TABLE by STMTID and COPY CURRENT or INVALID. QUERYNO is 0; COLLID is DSNSTBLQRYEXPLAIN.

Static versus dynamic EXPLAIN

Static SQL

The usual capture is the bind option:

1
2
REBIND PACKAGE(PAYROLL.PAYUPD.(VPROD)) -
  EXPLAIN(YES)

EXPLAIN(YES) binds (or rebinds) and inserts rows for every explainable statement in that package or plan. It does not explain SQL inside packages that a plan merely lists. EXPLAIN(ONLY) runs the same optimizer work and locking but does not replace the directory package—use it to trial APREUSE. owner.PLAN_TABLE must exist; VALIDATE(BIND) fails the bind if it does not. QUERYNO is the DBRM statement number. You can also embed SQL EXPLAIN inside the program even if the package was bound EXPLAIN(NO).

Dynamic SQL

Three patterns:

• PREPARE a full EXPLAIN PLAN FOR ... statement (the FOR text cannot be a host variable holding only the user SQL; the EXPLAIN keyword must be in the prepared string).
• SET CURRENT EXPLAIN MODE = YES — each later dynamic statement is explained and executed. = EXPLAIN explains without executing. = NO turns it off. Good for a test script; dangerous in production if you forget YES and double the work.
• EXPLAIN STMTCACHE for what is already running.

1
2
3
SET CURRENT EXPLAIN MODE = EXPLAIN;
-- next PREPARE/EXECUTE or JDBC execute is explained, not run
SET CURRENT EXPLAIN MODE = NO;

Reading EXPLAIN output

Start with PLAN_TABLE. One statement often produces several rows: one per query block (QBLOCKNO) and per step (PLANNO) inside that block (each table in a join, each sort).

1
2
3
4
5
6
7
SELECT QUERYNO, QBLOCKNO, PLANNO, TNAME,
       ACCESSTYPE, ACCESSNAME, MATCHCOLS, INDEXONLY,
       METHOD, PREFETCH, SORTN_UNIQ, SORTC_GROUPBY,
       JOIN_TYPE, HINT_USED, BIND_EXPLAIN_ONLY
  FROM PLAN_TABLE
 WHERE QUERYNO = 1001
 ORDER BY QBLOCKNO, PLANNO;

Column cheat-sheet for a first read:

• TNAME — table (or work file) for this step.
• ACCESSTYPE — how that table is read (see table below).
• ACCESSNAME — index name when ACCESSTYPE is an index method.
• MATCHCOLS — how many index columns are matching predicates.
• INDEXONLY — Y if the index supplies every needed column (no data page).
• METHOD — 0 first table, 1 nested loop, 2 merge scan, 4 hybrid join, plus other codes for sorts and materialization.
• PREFETCH — S sequential, L list, D dynamic, blank random.
• SORTN_* / SORTC_* — sorts for uniqueness, join, GROUP BY, ORDER BY.
• HINT_USED — OPTHINT name, APREUSE, or EXPLAIN PACKAGE copy id.
• BIND_EXPLAIN_ONLY — Y if EXPLAIN(ONLY) produced the row.

Join DSN_STATEMNT_TABLE on QUERYNO (and APPLNAME/PROGNAME when present) for estimated milliseconds and service units. That cost is the optimizer’s model, not a promise of wall-clock time. DSN_PREDICAT_TABLE lists each predicate and whether it is stage 1 or index matching. DSN_DETCOST_TABLE and DSN_FILTER_TABLE add more detail once you know which step is the problem.

Bind EXPLAIN rows for a package also carry COLLID, PROGNAME, VERSION. Always filter on those plus QUERYNO and a recent EXPLAIN_TIME; PLAN_TABLE accumulates history unless you delete old rows.

A beginner workflow

1. Create PLAN_TABLE (and DSN_STATEMNT_TABLE) under your id.
2. For a standalone SELECT, run EXPLAIN PLAN SET QUERYNO = n FOR ... then SELECT from PLAN_TABLE WHERE QUERYNO = n.
3. For a COBOL package, REBIND with EXPLAIN(ONLY) first, read HINT_USED and ACCESSTYPE, then EXPLAIN(YES) when you are ready to keep the path.
4. For JDBC SQL already running, EXPLAIN STMTCACHE STMTID from IFCID 316.
5. If ACCESSTYPE is R on a large table, look at predicates and statistics next—not immediately at a new index—unless MATCHCOLS on an existing index is zero.

Explain It Like I'm Five

EXPLAIN is asking the GPS for the route without driving the car. PLAN_TABLE is the turn-by-turn list: which street (table), whether you used a shortcut (index), and whether you waited at a merge (sort or join). BIND EXPLAIN(YES) writes the route into the notebook when you pack the suitcase (the package). EXPLAIN(ONLY) draws the route on scrap paper without repacking. CURRENT EXPLAIN MODE is a sticker on your backpack that copies every new sticky-note recipe into the notebook. STMTCACHE is photocopying the route that cars in the parking lot already used, without asking GPS again.

Exercises

1. Create PLAN_TABLE from DSNTESC under your userid. Run EXPLAIN PLAN SET QUERYNO = 1 FOR SELECT * FROM SYSIBM.SYSDUMMY1 and display the rows.
2. Compare EXPLAIN(ONLY) versus EXPLAIN PACKAGE for one production package. Which HINT_USED values do you see, and did SYSPACKAGE.BINDTIME change?
3. Set CURRENT EXPLAIN MODE = EXPLAIN, run a dynamic SELECT, then set NO. Confirm no result set appeared and PLAN_TABLE has a new QUERYNO.
4. For a three-table join, list PLANNO, TNAME, METHOD, and ACCESSTYPE in order. Which table is the first (METHOD 0)?
5. Join PLAN_TABLE to DSN_STATEMNT_TABLE for QUERYNO 1. What estimated cost columns appear, and why might they disagree with actual elapsed time?