topic Re: DB2 Index Hint Syntax in Application Development and Automation Discussions

DB2 Index Hint Syntax

Former Member — Wed, 06 Jan 2010 15:45:26 GMT

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?

Re: DB2 Index Hint Syntax

Former Member — Wed, 06 Jan 2010 16:08:04 GMT

Hi Dave ,

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

Hope this helps you.

Re: DB2 Index Hint Syntax

mvoros — Wed, 06 Jan 2010 22:59:34 GMT

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

Re: DB2 Index Hint Syntax

Former Member — Thu, 07 Jan 2010 08:29:37 GMT

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.

Re: DB2 Index Hint Syntax

Former Member — Thu, 07 Jan 2010 12:04:12 GMT

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.

Re: DB2 Index Hint Syntax

Former Member — Thu, 07 Jan 2010 12:23:58 GMT

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

Re: DB2 Index Hint Syntax

HermannGahm — Thu, 07 Jan 2010 12:58:05 GMT

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