Explain a DB2 query

What “explain a query” means

EXPLAIN does not tune the statement by itself. It records how Db2 plans to access tables and indexes: scan versus index, join method and order, sorts, parallelism hints in the explain tables, and estimated costs when populated. You then interpret those rows (see Read a PLAN_TABLE) and decide whether statistics, indexes, or SQL changes are needed.

Important distinction: some forms run access path selection now (EXPLAIN PLAN FOR …, BIND/REBIND EXPLAIN YES or ONLY, CURRENT EXPLAIN MODE). Others extract an existing path (EXPLAIN PACKAGE, EXPLAIN STMTCACHE) without choosing a new one. Use the form that matches your question—“what will Db2 do if I prepare this?” versus “what is the package already doing in production?”

Explainable statements include SELECT and many data-change statements (searched UPDATE/DELETE, INSERT, MERGE, TRUNCATE, and related forms per release). Cursor declarations are explained through the statements they contain when bound with EXPLAIN.

• Dynamic: EXPLAIN PLAN SET QUERYNO = n FOR sql-statement
• Session: SET CURRENT EXPLAIN MODE = YES | EXPLAIN | NO
• Static: BIND or REBIND with EXPLAIN(YES) or EXPLAIN(ONLY)
• Existing path: EXPLAIN PACKAGE or EXPLAIN STMTCACHE

Prerequisites

Create explain tables under the authorization ID that will own the EXPLAIN output. PLAN_TABLE is required for basic EXPLAIN. Optional tables such as DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE, and others enrich cost and function detail. SDSNSAMP member DSNTESC (or ADMIN_EXPLAIN_MAINT) is the supported way to create the correct column set for your Db2 version.

PLAN_TABLE must be a base table or an alias to a base table—not a view or synonym. Qualify objects carefully when multiple schemas exist. You need authority to INSERT into your PLAN_TABLE and to EXPLAIN the statement (privileges on referenced objects, or appropriate EXPLAIN authorities for packages you do not own).

Gather reasonably current RUNSTATS for the tables and indexes involved. Explaining against stale statistics produces a “correct” plan for wrong assumptions. Use a representative SQL text: parameter markers versus literals can change access path selection.

• Know your SQLID / package owner versus PLAN_TABLE owner rules.
• Pick a QUERYNO convention so you can find your rows later.
• Prefer a test subsystem that mirrors production statistics when predicting production paths.

Steps: explain dynamic SQL

In SPUFI, DSNTEP2, or an application connect, set the SQLID if needed so PLAN_TABLE resolves correctly. Issue EXPLAIN PLAN SET QUERYNO = n FOR followed by the statement. QUERYNO is your label; use something memorable like 9001.

Alternatively set CURRENT EXPLAIN MODE. YES explains and executes dynamic statements; EXPLAIN explains without executing; NO returns to normal. MODE is convenient when an application prepares many statements in one session.

1
2
3
4
5
6
7
8
9
10
11
12
13
SET CURRENT SQLID = 'TRAIN01';
 
EXPLAIN PLAN SET QUERYNO = 9001 FOR
  SELECT C.CUST_ID, C.NAME, O.ORDER_ID, O.AMOUNT
    FROM SALES.CUSTOMER C
    JOIN SALES.ORDERS O
      ON O.CUST_ID = C.CUST_ID
   WHERE C.REGION = 'EMEA'
     AND O.ORDER_DATE >= DATE('2024-01-01');
 
-- Optional session style:
-- SET CURRENT EXPLAIN MODE = EXPLAIN;
-- then PREPARE/EXECUTE the statement without running business side effects

Steps: explain static SQL

For packages, BIND or REBIND with EXPLAIN(YES) writes explain tables and replaces the package as usual. EXPLAIN(ONLY) runs access path selection and populates explain tables without replacing the operational package—ideal when you want a candidate path without changing production yet.

To document what is already bound, use EXPLAIN PACKAGE. That extracts the existing path into your PLAN_TABLE without re-optimizing. Use it when diagnosing “what is live now?” rather than “what would a rebind do?”

1
2
3
4
5
6
7
8
9
BIND PACKAGE (SALESCOL) -
  MEMBER(ORDERRPT) -
  OWNER(TRAIN01) -
  QUALIFIER(SALES) -
  EXPLAIN(ONLY) -
  VALIDATE(BIND)
 
/* Or extract the bound path: */
EXPLAIN PACKAGE COLLECTION 'SALESCOL' PACKAGE 'ORDERRPT'

Verify results

Query PLAN_TABLE filtered by QUERYNO (dynamic) or by COLLID, PROGNAME, and BIND_TIME / EXPLAIN_TIME columns (static). You should see one or more rows ordered by QBLOCKNO and PLANNO. Empty result usually means wrong schema, wrong QUERYNO, or EXPLAIN never ran successfully.

Confirm optional tables if you rely on them: DSN_STATEMNT_TABLE for statement cost text, and others your site uses. Compare ACCESSTYPE and ACCESSNAME to the indexes you expect. If you see R (table space scan) on a large table with a selective predicate, that is a signal for the next tutorial: diagnose a bad access path.

Re-run EXPLAIN after RUNSTATS or SQL changes with a new QUERYNO so you can diff old and new plans side by side.

1
2
3
4
5
6
7
SELECT QUERYNO, QBLOCKNO, PLANNO, METHOD,
       CREATOR, TNAME, ACCESSTYPE, MATCHCOLS,
       ACCESSCREATOR, ACCESSNAME, INDEXONLY,
       PREFETCH, JOIN_TYPE, QBLOCK_TYPE
  FROM TRAIN01.PLAN_TABLE
 WHERE QUERYNO = 9001
 ORDER BY QBLOCKNO, PLANNO;

Choosing the right EXPLAIN form

If you are learning SQL in SPUFI, start with EXPLAIN PLAN SET QUERYNO. It is immediate, easy to filter, and teaches PLAN_TABLE without bind machinery. Persist the SQL text next to the QUERYNO in your notes so you remember what you explained.

If you are diagnosing a production COBOL or Java package, begin with EXPLAIN PACKAGE or your monitor’s “explain bound path” action. That answers what is hurt users now. Only after you understand the live path should you experiment with EXPLAIN(ONLY) rebinds that might choose something different under today’s statistics.

Dynamic statement cache problems call for EXPLAIN STMTCACHE when you need the path that was chosen when the statement entered the cache. That can differ from a brand-new EXPLAIN PLAN FOR if literals, REOPT, or stats shifted. Match the diagnostic question to the EXPLAIN flavor.

Keep explain hygiene: delete or archive old PLAN_TABLE rows for reused QUERYNO values, or always increment QUERYNO. Mixed timestamps from three experiments under one QUERYNO make “read a PLAN_TABLE” needlessly confusing for the next person.

Common errors

PLAN_TABLE not found / wrong owner: EXPLAIN looks for the table under the explain owner rules for your path (SQLID, package owner, etc.). Create DSNTESC tables under that ID or use an alias.

Insufficient authority on base tables or EXPLAIN PACKAGE of someone else’s collection: grant the needed privileges or use an authorized performance ID.

Statement not explainable or syntax error inside the FOR clause: fix SQL first; EXPLAIN will not produce path rows for a statement that fails to parse.

Confusing EXPLAIN PACKAGE with a new optimization: PACKAGE extracts the old path. Use BIND EXPLAIN(ONLY) when you want a fresh selection without replacing the package.

Comparing dynamic EXPLAIN with production static packages without matching REOPT, literals, or special registers: paths can differ. Align environment settings when predicting production.

Using an outdated PLAN_TABLE definition from an older Db2 release: missing columns or ADMIN_EXPLAIN_MAINT drift can cause explain failures or incomplete optional-table population. Recreate from the DSNTESC that matches your current function level when IBM requires it.