You can use the DB2_MAXIMAL_PUSHDOWN server option in conjunction
with the Explain utilities to determine whether a particular operator was
not pushed down to execute at a data source because of a cost-based optimizer
decision or because pushdown analysis determined it was not possible.
About this task
Procedure
To run the Explain tools on a query with the DB2_MAXIMAL_PUSHDOWN
server option:
Procedure
-
Set the DB2_MAXIMAL_PUSHDOWN server option to 'N'. This is the
default setting for this option. Pushdown analysis determines which parts
of the SQL can be pushed down. Then the query optimizer generates all the
alternative plans that do not violate the criteria set by pushdown analysis.
The query optimizer estimates the cost of each plan, and will select the plan
with the lowest estimated cost. You can analyze the operators that were pushed
down to the data source by viewing the details of the appropriate SHIP operator.
If an operator you expect to be pushed down was not pushed down, proceed to
step 2.
-
Set the DB2_MAXIMAL_PUSHDOWN server option to 'Y'. Use the Explain
tools to analyze the SQL statement again. The plan displayed in the Explain
output shows all of the SQL operations that can be pushed down to the data
source.
- If the operator is pushed down after resetting the option to 'Y', the
optimizer determined that it was more cost-efficient to execute the operator
locally, rather than remotely. If the operator is not pushed down after resetting
the option to 'Y', it is likely that pushdown analysis did not allow the operator
to be executed remotely.
- If the optimizer made a cost-based decision not to push down the operator,
consider checking the nickname statistics to ensure that they are accurate.
If pushdown analysis made the decision not to push down the operator, consider
checking server options, data type mappings, and function mappings.