EXPLAIN

EXPLAIN tells you how DB2 plans to run your SQL before you run it. It shows which indexes will be used, whether a sort is needed, and how tables will be joined. It is the first tool for any slow query.

What EXPLAIN does

• EXPLAIN asks the DB2 optimizer to reveal the access path it chose for a SELECT, INSERT, UPDATE or DELETE.
• The result is stored as rows in a table called PLAN_TABLE. One row per query block, per table accessed.
• On the BIND command, EXPLAIN(YES) loads the access paths of all static SQL in the package into PLAN_TABLE. EXPLAIN(NO) is the default and loads nothing.
• For dynamic SQL, run the EXPLAIN statement yourself in SPUFI or QMF before tuning the query.

Running EXPLAIN

• First create your own PLAN_TABLE. DB2 ships a sample CREATE TABLE for it; create it once under your own authid.
• Then run EXPLAIN PLAN FOR followed by your query. Use SET QUERYNO to tag the rows so you can find them later.
• Query PLAN_TABLE to read the access path. Filter by your QUERYNO.
• Example:-
  EXPLAIN PLAN SET QUERYNO = 100 FOR SELECT EMPNO, LASTNAME FROM EMP WHERE DEPTNO = 'D01'; SELECT QBLOCKNO, ACCESSTYPE, MATCHCOLS, INDEXONLY, SORTN_JOIN, SORTC_JOIN FROM PLAN_TABLE WHERE QUERYNO = 100;

Reading the important columns

• ACCESSTYPE: how the table is read. R means tablespace scan (reads everything), I means index used, N means index used for IN-list.
• MATCHCOLS: how many index columns are matched. More matched columns usually means a tighter, faster access.
• INDEXONLY: Y means all needed columns came from the index, so the table pages were never touched. This is very fast.
• SORTN_JOIN / SORTC_JOIN: Y means DB2 must sort for a join or for ORDER BY. Sorts cost CPU and time.
• PREFETCH: S, L or D shows sequential, list or dynamic prefetch. Prefetch is good; it reads ahead in bulk.