Analyzing global optimization

Detailed information about access plans, including some of the information that the global optimizer uses to choose the optimal plan, is kept in explain tables separate from the actual access plan itself.

This information allows for in-depth analysis of an access plan. The explain tables are accessible on all supported operating systems, and contain information for both static and dynamic SQL statements. You can access the explain tables using SQL statements. This allows for easy manipulation of the output, for comparison among different queries, or for comparisons of the same query over time.

There are multiple ways to get global access plan information from the Explain tables:
  • You can use the Explain table format tool, db2exfmt, to present the information from the explain tables in a predefined format.
  • You can use the db2expln tool to understand the access plan that is chosen for a particular SQL statement.

    To fully understand the output of db2exfmt, Visual Explain, or db2expln you must understand:

    • The different SQL statements supported and the terminology related to those statements (such as predicates in a SELECT statement)
    • The purpose of a package (access plan)
    • The purpose and contents of the system catalog tables
    • Basic query processing operators such as joins, group-by, aggregation, and sorts