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...