Investigating SQL performance by using EXPLAIN

You capture detailed information about the access paths that DB2® chooses to process a statement, the cost of processing statements, and which functions DB2 uses.

Begin program-specific programming interface information.
The information in EXPLAIN tables can help you to:
  • Design databases, indexes, and application programs
  • Determine when to rebind an application
  • Determine the access path that DB2 chooses for a query
EXPLAIN data contains information about the access path that DB2 uses to process SQL statements. The primary use of EXPLAIN data is to investigate the access paths for the SELECT parts of your statements. For example, the data in EXPLAIN tables describes:
  • 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.

End program-specific programming interface information.

Tip: Various query optimization and administration tools, such as IBM® Data Studio or IBM Data Server Manager and DB2 Query Workload Tuner for z/OS®, provide a feature called Visual Explain that enables you to create visual diagrams of the access paths for your SQL statements. To learn more about using this feature, see Generating visual representations of access plans (IBM Data Studio).