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.