OPTHINT - A step by step approach

The word OPTHINT can be new to many, I will try to keep the post simple and easy to understand - So that you can along with a pop corn be able to grasp good amount of information posted here. To begin with - OPTHINT also called as an OPTimzer HINT is a method to teach DB2, yes teach DB2 to use a particular access path for your SQL query. When I say - particular access path, it means I have an access path in mind and I want DB2 to use it - Right away !!

If you look at the PLAN_TABLE of your application program, you will find two columns OPTHINT and HINT_USED. You may find some data populated or left blank in most of the cases when you query it with SELECT statement. These are the mastermind columns of the PLAN_TABLE which help us establish a user defined access path for the query.

Grab a test module in your test subsystem and try the following steps. Needless to mention, after seeking appropriate approvals etc from your application programers, DM's etc - Just to let them know you want to ' learn ' and ' help ' - It's a good practice and I follow it in my organization

Why do you need OPTHINT ?

I have a query that is performing bad/good/worse by accessing one of the secondary indexes present on the table. Lets name the index as IX_0002. I am keen to check the behavior of the query with IX_0001, which is the primary index for the table.

How do I establish a OPTHINT rule ?

Step 1 :

All the queries in the PLAN_TABLE are designated with a QUERYNO, So now you know your query for which you wish to apply the method. First query the PLAN_TABLE values for the QUERYNO of your choice using

SELECT  QUERYNO, ACCESSNAME, TNAME, ACCESSTPYE FROM PLAN_TABLE WHERE QUERYNO = 1234 ;

Notice the ACCESSNAME - IX_0002, it is the secondary index and we want DB2 optimizer to choose IX_0001 which is the primary Index for our table TAB0001

Step 2 : Optional

If your shop happened to use CA or BMC tools you can very well try to use the edit function and edit the PLAN_TABLE data and repeat the row on which you wish to apply OPTHINT

Step 3 :

Set your SPUFI session on ROLLBACK mode to avoid any unnecessary updates or wrong query executions etc.

Use the UPDATE statement to update the access path, i.e. ACCESSNAME column ( your Index name ) for the QUERYNO of your choice. You should also update the OPTHINT column with a value - Preferred value would be the Index name itself

Here is how the UPDATE statement would look for the above scenario

UPDATE APPLN.PLAN_TABLE
SET ACCESSNAME='IX_0001' WHERE QUERYNO = 1234 ;

Commit your SPUFI session if ' 1 ' row is affected else ROLLBACK and check your UPDATE query !! There are times where ' 2 ' or more rows can get affected due to the JOIN methods in your query - You can proceed with such results.

UPDATE APPLN.PLAN_TABLE
SET OPTHINT='IX_0001' WHERE QUERYNO = 1234 ;

Commit your SPUFI session if ' 1 ' row is affected else ROLLBACK and check your UPDATE query !! There are times where ' 2 ' or more rows can get affected due to the JOIN methods in your query - You can proceed with such results.

Step 4 :

In the BIND card of your application program use the OPTHINT parameter and pass the OPTHINT value as shown below and BIND the program. Once the BIND is successful you will be notified as to how many rows were involved in the OPTHINT operations.

Step 5 : Optional

Revisit the PLAN_TABLE and look for the value of the column HINT_USED against the same query no. Use the below query :

SELECT * FROM APPLN.PLAN_TABLE WHEREHINT_USED = 'IX_0001' ;

The SPUFI result should display one row for our test case. The ACCESSNAME would have changed from IX_0002 to IX_0001, the MATCHCOLS value also change depending upon the index arrangement and WHERE predicates.

Step 6 :

Run your application program and validate the savings if any. Most of the times OPTHINT does not give expected results - So you may want to roll it back ?

How to remove OPTHINT rule ?

Assuming we performed the above steps -

Step1  :

Populate the BIND card for the program and remove the OPTHINT parameter and issue a BIND to rollback. The populated BIND card for your program will by default contain the OPTHINT parameter and you will have to manually delete the line containing the OPTHINT from the BIND card

Step 2 :

Verify the access path roll back by using

SELECT * FROM APPLN.PLAN_TABLE WHERE QUERYNO = 1234 ORDER BY BIND_TIME

Two or more rows would be populated, the value of the OPTHINT in the first row should be '            ' ( spaces ) and ACCESSNAME should be IX_0002

Conclusion and Summary :

Its always good to test and check for a better access path. OPTHINT does not always provide or guarantee optimum results due to various factors such as Index definition, usage, cluster ratio etc. But you can always roll it back and bring the systems back to how they were prior to the OPTHINT operation.