Using explain utilities

An SQL query can have more than one possible way or path of execution (also known as access plan). Access plan is also known as explain plan, and these two terms are used interchangeably. The DB2 optimizer uses a cost-based optimization to choose the path with the most efficient cost. The cost of each plan is determined from the latest database statistics available. That's why it is very important to make sure that the system statistics are up-to-date. Access plans provide the following information:

• Decision criteria that were used to choose the access plan

• Sequence of operations that were performed to process the query

• Cost of each step performed

• Selectivity estimate for each predicate

• Values of special registers and other variables (host variables, parameter markers, and so on)

• Optimization level in effect

• All objects that were used to process the query

DB2 provides different utilities that can be used to get the access plan chosen for a query execution. The access plan...