July 12, 2011

Using DB2 SQL Optimization Hints

Have you considered using DB2 optimization hints? The feature debuted on DB2 for z/OS Version 6 with the introduction of OPTHINT, HINT_USED and PRIMARY_ACCESSTYPE columns on the PLAN_TABLE. 

Hints provide:

  • A temporary fix to resolve an immediate crisis
  • Access path regression from previously good path
  • Lock in access path to stabilize desired access path

However, along with those benefits, using hints has these drawbacks:

  • Locked in access path will not change to take advantage of new improved access paths
  • Error prone due to changes in QUERYNO during program development
  • Complicated to understand and figure out what values should be used when defining the hint.

Before you can start using optimization hints, your DB2 environment must be set up to accomodate hints. First, during the DB2 install, set OPTIMIZATION HINTS on installation panel DSNTIP8 to YES. I also strongly suggest upgrading to the most current PLAN_TABLE definition. Under the default, the PLAN_TABLE used for hints must contain at least the 49 columns defined in DB2 Version 6. By upgrading, you can benefit from all the extra information DB2 can provide. DB2 10 install job hlq.SDSNSAMP(DSNTESC) creates the PLAN_TABLE with 65 columns.

For static SQL, I take these the steps:

  1. BIND package with EXPLAIN (YES) to populate the plan_table.
  2. Update the rows in the PLAN_TABLE with OPTHINT name (e.g., "goodpath").
  3. Set up the BIND package to use OPTHINT('goodpath').
  4. Query the PLAN_TABLE to see if the hint was used. Also note the messages in the BIND job, which tell you how many hints have been applied.

DB2 10 brings some useful enhancements for managing hints. "Instance-based" optimization hints help you avoid errors due to changing QUERYNO. I'll go into details about this next week.

Has your organization taken advantage of hints? What advice would you give to those who are looking to use hints? Please share your experiences, good and bad, in Comments.