Why aren't my optimization hints being used?
Solution
You can tell DB2® in the z/OS® and OS/390® environments how to process a query by giving hints to DB2. For information on how to give hints to DB2, see Giving optimization hints to DB2 in the DB2 Administration Guide for your version of DB2 at the DB2 for z/OS and OS/390 library Web page.
( http://www-306.ibm.com/software/data/db2/zos/library.html )
Message: Hint not found
If SQLCODE +395 is returned, DB2 did not find the desired optimization hint. For the hint to be found, all of the following columns must match:
QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID, OPTHINT. To see if the columns match, perform EXPLAIN against the application, and then compare the new PLAN_TABLE records to the old PLAN_TABLE records for the hint.
For static SQL, the OPTHINT column must match the OPTHINT supplied in the bind parameter. For dynamic SQL, the OPTHINT column must match the CURRENT OPTIMIZATION HINT special register.
Optimizer hint limitations:
One limitation is that optimization hints cannot affect view or table expression merge or materialization, or cannot affect subquery to join transformations. The query block structure for the hint must match the query block structure for the SQL statement that requires the hint. If the hint explain does not have the same query block structure as the target explain, you cannot use the hint.
For example:
Assume EXPLAIN 1 has two query blocks due to materialization of table T1.
Assume EXPLAIN 2 has one query block containing both tables T1 and T2.
There is no materialization.
EXPLAIN 1 and 2 are not interchangeable. You cannot use EXPLAIN 1 to drive materialization for an SQL statement that has an explain like EXPLAIN 2. In addition, you cannot use EXPLAIN 2 to force view or table expression merge for an SQL statement that produces an explain like EXPLAIN 1.
EXPLAIN 1
QUERYNO QBLOCKNO PLANNO METHOD TNAME
1 1 1 0 DSNWFQB(2)
1 1 2 2 T2
1 2 1 0 T1
EXPLAIN 2
QUERYNO QBLOCKNO PLANNO METHOD TNAME
1 1 1 0 T1
1 1 1 1 T2
Another limitation is that optimization hints allow you to select an index, but the optimizer chooses how to use the index:
You cannot use optimization hints to override the index only field to force data access when index only is allowed.
Optimization hints might not honor the index access type. When access type I, I1, or N is supplied, the optimizer treats this type as "use this index". The optimizer then determines whether to use index scan(I), one fetch index access (I1), or in-list index access(N).
For more information about optimization hints, see the DB2 Administration Guide for your version of DB2 at the DB2 for z/OS and OS/390 library Web page.
P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.
With kind regards
Michael Dewert, Software GroupTechnical Sales DB2
