EXPLAIN
The DB2 feature that shows how DB2 has chosen to run a query, such as which indexes it will use.
Also written access path, PLAN_TABLE, EXPLAIN(YES), table space scan
For every SQL statement, DB2 chooses an access path: the steps it will take to find the data. It might use an index to go straight to a few rows, or read the whole table space, or join two tables in a particular order. EXPLAIN shows you that choice.
You can run EXPLAIN for a single statement, or ask for it when a package is bound with EXPLAIN(YES). DB2 writes the results into a table called PLAN_TABLE, one row for each step, and you query it like any other table. Many tools show the same information as a diagram.
The things to look for are simple at first. Is an index used, and which one? Is there a table space scan, where DB2 reads every row? Is there a sort? A table space scan on a large table, for a query that should return a few rows, is the classic sign of a missing index or out-of-date statistics.
DB2 chooses access paths using statistics collected by RUNSTATS. If the statistics do not reflect the real data, the choice may be poor. That is why performance work often starts with EXPLAIN, then RUNSTATS, then a rebind.
Where can I learn this hands-on?
Right here, at ZedBank. You take a junior job at a simulated bank and learn each part of the work by doing it, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- IndexA structure that lets a database find rows by value without reading the whole table.
- BINDPreparing a program's SQL for execution, working out how each statement will access the data and storing the result.
- DB2 utilitiesThe batch programs that load, back up, reorganise and check DB2 for z/OS data, run as jobs outside normal SQL.
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- Table spaceThe storage structure in DB2 for z/OS that holds the data of one or more tables, kept in VSAM datasets that DB2 manages.