topic Re: %_HINTS DB2 syntax in Application Development and Automation Discussions

%_HINTS DB2 syntax

Former Member — Wed, 20 Jan 2010 14:26:29 GMT

I have the following select statement:

Select recn

evdat

from cciht_ial

into corresponding fields of table lt_header

for all entries in lt_ialrecn

where valfr le sy-datum

and valto ge sy-datum

and recn eq lt_ialrec-recn

%_HINTS DB2 'max_blocking_factor 500 max_in_blocking_factor 500'

I see no improvement in performance.

When I look in ST05 the SQL IN does not seem to be blocked by 500

I also tried with syntax %_HINTS DB2 '&max_blocking_factor 500&&max_in_blocking_factor 500&'

Which is correct syntax and should I see the blocking in an ST05 trace?

Re: %_HINTS DB2 syntax

HermannGahm — Wed, 20 Jan 2010 14:56:34 GMT

Hi,

> I also tried with syntax %_HINTS DB2 '&max_blocking_factor 500&&max_in_blocking_factor 500&'

this syntax should be correct.

this example works fine for me:

%_HINTS ORACLE '&max_in_blocking_factor 50&&max_blocking_factor 50&'

How much elements do your inlists have instead? the default values?

Have you tried with a smaller value than 500 (e.g. 100) as well?

Or maybe a silly question are you really using DB2 (DB2 UDB for zseries)

or DB4 (DB2 UDB for i series) or DB6 (DB2 for LUW)?

> Which is correct syntax and should I see the blocking in an ST05 trace?

yes you should see the blocking in ST05 in the statement details (nr. of bind variables) and less executions

of the statement compared with lower values for the blocking factors... .

Kind regards,

Hermann

Re: %_HINTS DB2 syntax

Former Member — Wed, 20 Jan 2010 15:03:49 GMT

Is that really what you want .. max_blocking_factor 500 ? ...

This one max_in_blocking_factor 500 has advantages, but 500 is a number which is unneccessarily high:

Do you really expect a big improvement, but a very large blocking fatcor, I would have doubts.

The ranges puts everything in one statement, and dumps when the statements becomes to complicated.

Additionally the advantage for large blockings factors is not so high.

It is a big difference whether you execute:

+ 1 entry per execution (SELECT in LOOP)

+ a few entries (blocking factors 5 - 10)

+ you can still gain with factors up to 100.

But then I would expect a saturation.

Re: %_HINTS DB2 syntax

Former Member — Wed, 20 Jan 2010 16:38:13 GMT

Your syntax is the correct one and I lowered factor to 100 as suggested and can see it in ST05.

Thanks

Re: %_HINTS DB2 syntax

HermannGahm — Wed, 20 Jan 2010 16:50:02 GMT

Hi Raynald,

thanks for the response.

It would be interesting for us to see the effect i think. Would you mind to share the

resulst regarding improvement? I'm interested in the blocking and runtime before and after

the change.

Kind regards,

Hermann

Re: %_HINTS DB2 syntax

Former Member — Thu, 21 Jan 2010 09:38:53 GMT

You can play around with simple mathematics:

100 ( offset + item) = T1

20 ( offset + 5 item ) = T2

1 ( offset + item ) = T3

Play in Excel, use different accumptions for cost for offset and for item and you will see the effect.

Siegfried