Analyzing where a query is evaluated

Detailed query optimizer information is kept in Explain tables separate from the actual access plan itself. This information allows for in-depth analysis of an access plan. By examining the SHIP operator of a federated access plan, you can determine what SQL operations were pushed down to a data source and which operations were executed at the federated server.

Explain tables are accessible on all supported operating systems, and contain information for both static and dynamic SQL statements. The following tools are typically used to obtain access plan information from the explain tables:
  • Explain table format tool. Use the db2exfmt tool to present the information from the explain tables in a predefined format.
  • db2expln tool. You can use this tool to understand the access plan chosen for a particular SQL statement. Both dynamic and static SQL statements can be explained using the Explain Facility. To fully use the output of 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
    • General application tuning concepts

You can also access 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.