Hinting Optimizer to choose the access path of your choice

Let us analyse the following SQL:

EXPLAIN ALL SET QUERYNO=999 FOR
SELECT TACCONT.AC_NA , TACCONT.AC_TYP_CD
FROM TACCONT
WHERE TACCONT.AC_NR = '123456' AND
TACCONT.CNY_CD = 'US' and
TACCONT.AC_TYP_CD = 'A';

Suppose that DB2 would pick up index IACCONT1, which is an
index based on AC_NR.
In the event of DB2 picking up the normal index, the PLAN_TABLE
entry for the above query would appear as follows:

Query # MTH TNAME TABNO ACCESS MATCH ACCESS INDEX
TYPE COLS NAME ONLY
999 Ø TACCONT 1 I Ø1 IACCONT1 Y

But, based on our knowledge of the data, we want DB2 to pick up
IACCONT2, a secondary index based on CNY_CD and AC_TYP_CD.
In earlier versions of DB2 we could not influence the access path, but
illustrated below is a feature provided by DB2 Version 6 by which we
can give hints to the DB2 optimizer to pick up IACCONT2.
Some new columns to the PLAN_TABLE have been added in DB2
Version 6 to facilitate this feature of DB2. They are OPTHINT and
HINT_USED.

Now, let’s update the OPTHINT field to name our new access path.
Let’s call that new plan for accessing the secondary index PICKSECX.
We need to update the PLAN_TABLE as follows:

UPDATE PLAN_TABLE
SET OPTHINT = 'PICKSECX'
WHERE OPTHINT = '' AND QUERYNO = 999;

Now let’s update the PLAN_TABLE to indicate the access path for
our new plan,PICKSECX. In this case, you will update the
ACCESSNAME column of PLAN_TABLE, with the following SQL
statement:

UPDATE PLAN_TABLE
SET ACCESSNAME = 'IACCONT2'
WHERE OPTHINT = 'PICKSECX' AND QUERYNO = 999;

When updating the PLAN_TABLE for the second time, do make sure
you are updating the right row, by predicating the OPTHINT column
by the plan – which picks the secondary index.
After updating the PLAN_TABLE, the row of interest to us would
look like:

Query # MTH TNAME TABNO ACCESS MATCH ACCESS INDEX OPT_HINT HINT_USED
TYPE COLS NAME ONLY
999 Ø TACCONT 1 I Ø1 IACCONT2 Y PICKSECX

Now after setting the hint to the optimizer in the PLAN_TABLE, we
should bind our static SQL accordingly.
For static SQL, let’s use the following parameter in our bind package
or bind plan cards:

OPTHINT('PICKSECX').

For dynamic SQL, issue a new special DB2 register statement as follows:
SET CURRENT QUERY OPTIMIZATION HINT = 'PICKSECX' WHERE QUERYNO=999;
And then execute the same SQL as in the first example above as
follows:

SELECT TACCONT.AC_NA , TACCONT.AC_TYP_CD
FROM TACCONT
WHERE TACCONT.AC_NR = '123456' AND
TACCONT.CNY_CD = 'US' and
TACCONT.AC_TYP_CD = 'A'
QUERYNO 999;

The difference between the two SQL examples is the presence of
QUERYNO in the second example.
After execution, the PLAN_TABLE row suggests the following:

Query # MTH TNAME TABNO ACCESS MATCH ACCESS INDEX OPT_HINT HINT_USED
TYPE COLS NAME ONLY
999 Ø TACCONT 1 I Ø2 IACCONT2 Y PICKSECX

After executing the second SQL with hints to the optimizer, if we then
run an EXPLAIN, PLAN_TABLE shows that plan PICKSECX has
moved from column OPT_HINT to HINT_USED. This confirms that
DB2 has indeed picked the secondary index for the second SQL
example. MATCHCOLS for the second SQL, on picking up the
secondary index, IACCONT2, has become 02.

The BIND statement for static SQL and the PREPARE statement for
dynamic SQL indicates a SQLCODE of +394, suggesting that the hint
was successfully picked up for execution.

If IACCONT2 is a non-existent secondary index, then the BIND
statement for static SQL and the PREPARE statement for dynamic
SQL indicates a SQLCODE of +395, suggesting that the hint was
found, but execution was found to be invalid.