Replacing optimization hints

Complete these optional tasks in order to reduce or eliminate the use of optimization hints in your queries.

About this task

The situation can arise in which you want to reduce or eliminate the use of optimization hints in your queries. Some of the reasons for this include maintenance overhead, inflexibility, the possibility of overriding better plans, and others.

Db2ZAI 1.6.0 provides a couple of methods to achieve that goal.

(Method 1) Using the access path exploration process to replace optimization hints:

Procedure

This method compares the currently-hinted access path to other potential access paths. If a better performing access path is found, a recommendation will be made to switch to that access path.

By deploying the recommended access path, the deployed access path has priority over the hinted access path. This allows the hinted access path to be removed.

  1. Add the packages or dynamic statements that involve optimization hints to scope (see Updating the SQL optimization settings).
  2. Follow the process to begin access path exploration (see Improving query performance).
  3. Review the access path exploration results (see Evaluating query performance).
  4. Deploy the access path recommendations (see Deploying recommended access paths).

(Method 2) Using the access path lock feature to replace optimization hints

Procedure

This method uses the access path lock feature to lock in the current access path. The current access path should be the hinted access path, if the optimization hint has been successfully applied by Db2®. If the optimization hint has not been successfully applied by Db2, then it cannot be replaced using this method.

Note: This method does not address the issue of overriding a better-performing access path. Method 1 is needed to address that issue. Use this method only when the goal is to remove the optimization hint to avoid maintenance overhead and keep the current access path as it is.
To use the access path lock feature to replace optimization hints:

  1. Add packages or dynamic statements that involve optimization hints to scope (see Updating the SQL optimization settings).
  2. For static statements, navigate to the package that contains the statement that has the optimization hint, then follow process to lock the access path for that statement (see Evaluating query performance).
  3. For dynamic statements, navigate to the dynamic statement that has the optimization hint, then follow the process to lock the access path for that statement (see Evaluating query performance).