PLAN_TABLE is the primary DB2 explain table. After you EXPLAIN a statement, reading PLAN_TABLE correctly is how you translate cryptic codes into a story: which table is accessed first, whether an index matches, how tables join, and where sorts appear. This how-to focuses on the columns beginners must master and a repeatable query pattern.
Each explained statement can produce many rows. QUERYNO (or package identifiers) groups your statement. QBLOCKNO separates the outer query from subqueries, materialized views, and other blocks. PLANNO orders steps inside a block. Read rows ordered by QBLOCKNO, PLANNO so the join sequence matches execution order.
METHOD ties a row to a join or sort step. CREATOR and TNAME name the table (or work-file-like object) involved in that step. ACCESSTYPE describes how that table is accessed. ACCESSNAME and ACCESSCREATOR name the index when access is index-based. MATCHCOLS counts leading index columns that match predicates.
Do not judge a plan from one column alone. ACCESSTYPE = I with MATCHCOLS = 0 can still be a poor screening index. ACCESSTYPE = R on a tiny dimension table inside a nested loop can be fine. Always combine METHOD, table size knowledge, predicate selectivity, and accounting data.
You need PLAN_TABLE rows from a successful EXPLAIN (see Explain a query). Know which QUERYNO or package name you used. Have a mental model of the SQL: which tables, join predicates, and filter predicates exist, and which indexes you believe should match.
Optional but helpful: DSN_STATEMNT_TABLE for statement-level cost, catalog stats (CARD, FIRSTKEYCARD, FULLKEYCARD), and a simple estimate of table size. Reading PLAN_TABLE without knowing whether a table has 100 or 100 million rows leads to false alarms.
Start with a projection of the core columns. Filter tightly. Order by QBLOCKNO and PLANNO. Add REMARKS or HINT columns only when your site uses optimization hints.
Walk row by row. For METHOD 0 (first table / continuation), note ACCESSTYPE. For METHOD 1 (nested loop), 2 (merge join), or 4 (hybrid/hash join depending on version documentation), note which table is the “new” table in that step and how it is accessed. METHOD 3 rows are sort/work steps that do not introduce a new base table in the same way—read IBM’s METHOD meanings for your release carefully.
123456789101112SELECT QUERYNO, QBLOCKNO, PLANNO, METHOD, CREATOR, TNAME, TABNO, ACCESSTYPE, MATCHCOLS, ACCESSCREATOR, ACCESSNAME, INDEXONLY, JOIN_TYPE, PREFETCH, SORTN_JOIN, SORTC_JOIN, SORTN_ORDERBY, SORTC_ORDERBY, SORTN_GROUPBY, SORTC_GROUPBY, QBLOCK_TYPE, PRIMARY_ACCESSTYPE FROM TRAIN01.PLAN_TABLE WHERE QUERYNO = 9001 ORDER BY QBLOCKNO, PLANNO;
R means a table space (or table) scan: Db2 reads the table without a matching index path for that step. I means index access; look at ACCESSNAME and MATCHCOLS. I1 is a one-fetch index access (often MIN/MAX style). N often relates to IN-list index access. M / MX / MI / MU describe multiple-index strategies. Blank can appear on rows that are not table-access steps.
MATCHCOLS greater than zero means leading key columns participate in matching predicates. Higher matching is often better, but only if those columns are selective. INDEXONLY = Y means the index alone satisfies the needed columns—no data-page fetch for that access—which is frequently a win.
PREFETCH of S (sequential) or L (list) hints how Db2 expects to bring pages in. Sequential prefetch on a scan of a huge table can still be expensive even when efficient per page.
Join order is visible in PLANNO sequence within the block. The first table accessed is usually the one Db2 expects to filter early. Nested loop (METHOD 1) repeats inner access for outer rows—great when the outer result is small and the inner index is excellent; painful when the outer is huge and the inner becomes a scan.
Merge and hash joins can be better for large-to-large joins but may introduce sorts or work files (watch SORTN_JOIN / SORTC_JOIN). ORDER BY, GROUP BY, DISTINCT, and some quantified predicates introduce sort flags. A sort is not automatically bad; an unexpected sort plus a huge composite is a performance smell.
JOIN_TYPE and QBLOCK_TYPE help when outer joins, star joins, or subquery blocks obscure the story. Correlate each QBLOCKNO back to a subquery or UNION leg in the SQL text.
Narrate the plan in one paragraph of plain English and have a peer check it against the SQL. Example: “Db2 starts at ORDERS with index ORDX1 matching two columns, then nested-loops to CUSTOMER with CUSTX1 matching CUST_ID, index-only.” If you cannot narrate it, you do not yet understand the rows.
Cross-check with runtime evidence when available: accounting class 3, performance traces, or SQL Monitor tools. A “good looking” PLAN_TABLE can still suffer from lock waits or poor buffer-pool hit ratios. Conversely, a scan of a 200-row table may be optimal.
Save before/after PLAN_TABLE exports when you change indexes or statistics so you can prove the path changed the way you intended.
Suppose PLAN_TABLE for QUERYNO 9001 shows two rows in QBLOCKNO 1. Row PLANNO 1: METHOD 0, TNAME ORDERS, ACCESSTYPE I, MATCHCOLS 2, ACCESSNAME ORDX_DATE_CUST, INDEXONLY N. Row PLANNO 2: METHOD 1, TNAME CUSTOMER, ACCESSTYPE I, MATCHCOLS 1, ACCESSNAME CUSTX_PK, INDEXONLY Y. Narration: Db2 starts at ORDERS using a two-column matching index that still needs data pages, then nested-loop joins to CUSTOMER through the primary key index with index-only access.
If instead PLANNO 1 showed ACCESSTYPE R on ORDERS with a selective date predicate, you would ask why the date index was not matched: predicate shape, stats, or missing index leading columns. That single contrast is the core skill of reading PLAN_TABLE—connect codes back to SQL text and to what you expected.
When a third row appears with METHOD 3 and SORTC_ORDERBY Y, add to the narration that Db2 sorts the composite for ORDER BY. Decide whether the sort is cheap relative to the join or whether an index already ordered the result and the sort is surprising.
Build a personal cheat sheet of ACCESSTYPE values you see in your shop. Official IBM tables are complete; your cheat sheet should highlight the five codes you encounter weekly so you stop translating every time.
Reading the wrong QUERYNO or an old BIND_TIME set of rows mixed with new ones. Always filter and consider deleting or archiving old explain rows for that QUERYNO before re-explaining.
Assuming METHOD numbers without checking your Db2 version’s documentation—hybrid join numbering and newer access types evolve.
Treating any R as a defect. Small tables, inner dimension tables, or post-filter tiny sets can scan cheaply.
Ignoring QBLOCKNO and mixing subquery steps into the outer join story.
Looking only at PLAN_TABLE while the real problem is cardinality mis-estimate visible in other explain tables or in actual row counts versus estimates.
Misreading INDEXONLY = N as “index not used.” INDEXONLY concerns whether data pages are needed after index access; ACCESSTYPE still tells you whether an index path was chosen.
PLAN_TABLE is a comic strip of how Db2 will find your data. Each box says which toy box (table) it opens, whether it uses the labeled drawers (index), and which box it opens next when combining toys. If you read the boxes out of order, the story sounds silly—so always read them in QBLOCKNO and PLANNO order.
1. In which order should you read PLAN_TABLE rows?
2. ACCESSTYPE = I usually means:
3. MATCHCOLS counts:
4. INDEXONLY = Y means:
5. Why is ACCESSTYPE = R not always bad?