DB2 Index Hint Syntax

[Former Member]

I want to force DB2 to utilize a particular table index. Beyond the "Use values for optimization", etc. hints, is there a way to point to a specific index in Open SQL select in a DB2 (release 8.1.5) system?

[Former Member]

Hi Dave ,

Ya there is way , we use Something with select statement , %HINT , press F1 and check.

Hope this helps you.

[mvoros]

Hi,

as it was mentioned you can use pass hints with your SQL query. The keyword is %_HINTS. You can put something similar after your WHERE conditions.

WHERE <conditions>
%_HINTS DB2 'INDEX(&quot;VBRK&quot; &quot;VBRK~0&quot;)'

Search here on SDN for some additional examples. Also have a look at very useful OSS note 129385.

Cheers

[Former Member]

Thanks, Martin and Seigfreid. I have read a variety of notes on this subject. I tried the specific hint, and I'm disappointed to see that the optimizer still grabs the secondary index I'm trying to avoid. I'll investigate DB2 system settings; perhaps hints are not allowed in our environment.

[former_member192616]

Hi Dave,

the hint mentioned by Martin is an ORACLE hint and not a DB2 hint.

For DB2 you need a so called CTE hint which is not mentioned in note 162034.

Get in contact with your database administrator in order to check:

- if the index and statistics are in place

- if the parameters for the database are set up according to the recomendations

- if the system is setup in a way that supports CTE hints

- how the CTE hint syntax for specifying an index looks like

Kind regards,

Hermann

[former_member194613]

there is on OSS note which you should know: 129385

That is the general hint, from that one you should find a note for your database release.

[former_member194613]

Hi,

I simple test use SE16 and in parallel ST05, select exactly one line by a selection which specifies the exactöly the fields of the index (primary or secondary). Check ST05, which index is used, be aware the MSSQL and SAP DB will use the primary key additionally.

Here you secondary key should be used, otherwise there is probably a problem with the index, only defined not built on the database or no statistics available.

If this check is o.k., but your hint does not work, then you should check very carefully the writing, there is no syntax check, small bugs can easily be overlooked.

Siegfried