Investigating SQL performance by using EXPLAIN
You capture detailed information about the access paths that Db2 chooses to process explainable statements, the cost of processing statements, and which functions Db2 uses. A statement is explainable if it is a SELECT, MERGE, TRUNCATE, or INSERT statement, or the searched form of an UPDATE or DELETE statement.
- Design databases, indexes, and application programs
- Determine when to rebind an application
- Determine the access path that Db2 chooses for a query
- Whether an index access or table space scan is used for each access to a table.
- When index access is used, how many indexes and index columns are used
- Which types of I/O methods are used to read the data pages.
- The join methods and types that are used, and the order in which Db2 joins the tables.
- When and why Db2 sorts data rows.
For UPDATE and DELETE WHERE CURRENT OF, and for INSERT, somewhat less information is provided. EXPLAIN data does not describe all or every type of access. For example, the access to LOB values, which are stored separately from the base table, and access to parent or dependent tables needed to enforce referential constraints, are not shown in EXPLAIN table data.
The access paths shown for the example queries are intended only to illustrate those examples. If you execute the same queries on your system, Db2 might choose different access paths.