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 is DB2's window into the optimizer's decision-making, and it's one of the highest-value skills a mainframe developer can bring to a performance-tuning interview, because 'my query is slow' is one of the most common real production problems, and EXPLAIN is the first diagnostic tool anyone reaches for.
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
1
Running EXPLAIN PLAN FOR against a query populates PLAN_TABLE (and related tables like DSN_STATEMNT_TABLE for cost estimates) with structured rows describing the optimizer's chosen access path: which access method and index (if any) is used per table, how many index columns matched actual predicates (MATCHCOLS), the join method and sequence for multi-table queries, and whether a sort is required for ORDER BY, GROUP BY, or a join strategy. Developers query PLAN_TABLE directly with SQL to review these details, comparing ACCESSTYPE and index usage against expectations for the query's predicates and available indexes.
2
A core skill is recognizing ACCESSTYPE values: 'I' means an index is used, 'R' means a full table space scan, and various join methods (nested loop, merge scan, hybrid) appear in the METHOD column for multi-table queries. Seeing ACCESSTYPE=R on a large table where you expected index usage is the classic red flag that sends a developer looking for a missing or unusable index, or a predicate written in a way that defeats index matching (like wrapping an indexed column in a function).
3
A strong interview answer goes beyond reciting PLAN_TABLE column names and explains the investigative workflow: run EXPLAIN, check ACCESSTYPE and MATCHCOLS against the query's WHERE clause and available indexes, and only conclude a table space scan is a problem after confirming it isn't actually the optimizer's genuinely best choice for a small table or a query returning most of the table's rows anyway.