By Tom Beavin and Tim Hogan

Has your environment accumulated a large number of optimization hints over time? Is managing your collection of optimization hints painful? Are you unsure what we're talking about?

Db2 for z/OS uses optimization hints as a way to influence access path selection for SQL statements. Hints are applied when the value of the OPTHINTS subsystem parameter is set to YES. Examples of optimization hints include:

• Statement-level predicate selectivity overrides

• Statement-level access paths

• Statement-level optimization parameters

• PLAN_TABLE access path hints

There are several reasons why you might want to reduce or eliminate the use of optimization hints, including reducing maintenance overhead, inflexibility, the possibility of overriding better plans, and others.

However, once you've started using optimization hints, it can be difficult to remove them due to the possible risks of performance degradation associated with the new access path updates.

Db2 AI for z/OS 1.6 can help you to relieve the pain of managing outdated optimization hints, with a couple of methods to achieve that goal.

Option 1:  Db2ZAI SQL optimization access path exploitation.

The first method involves using a recent enhancement to the SQL optimization capability, called access path exploration.

The SQL optimization exploration process internally compares access path plans and, optionally, compares execution performance statistics for the competing access paths. 

To use option 1, you would follow these steps:

1. Enable Db2ZAI SQL optimization and bring the target packages or statements into scope.

2. Allow Db2ZAI the time to collect data about the statements put into scope.

3. Kick-off the access path exploration process to compare the available access plans.

4. After exploring other access paths, Db2ZAI will recommend the access path that performs best, unless no other access path is found.
   
   If the best-performing access path is the one without the hint, then proceed to deploy the recommended access path. This involves using the user interface to indicate that you want to deploy the access path.
   
   If the better-performing access path is the hinted access path, you can lock the hinted access path and then remove the hint. That is, the access path lock mechanism can be used rather than the hint.
   
   If no other access path is found, then no recommendation is made. However, the hinted access path can be locked in so that the hint can be removed.

Option 2:  The second method simply uses the access path lock feature in Db2ZAI, without exploration.

Once the statement becomes the target of SQL optimization, and data about the statement has been collected, you have the option of locking the currently-hinted access path and removing the hint. This essentially keeps everything as-is but allows the hint to be replaced through the access path lock mechanism.

We hope that you explore the capabilities that Db2ZAI 1.6.0 has to offer to safely manage optimization hints and to potentially see performance improvements.  

To learn more about using Db2ZAI to replace optimization hints, see the Db2ZAI documentation.