topic Re: DB2 optimizer in Application Development and Automation Discussions

DB2 optimizer

Former Member — Wed, 18 Jun 2008 09:26:59 GMT

Hi Gurus!

My DB2 optimizer makes a mistake and choose a secondary index, instead of

primary and thefore we have performance problems.

Can I add some hints to SQL statement, to prevent his mistake?

I know in Oracle it looks like this

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'

Thanx.

Re: DB2 optimizer

Former Member — Wed, 18 Jun 2008 10:50:13 GMT

Hi Andrey,

yes you can add hints. Please read note #150037.

Regards

Ralph

Re: DB2 optimizer

Former Member — Wed, 18 Jun 2008 12:26:18 GMT

Thank you for reply.

I've already seen this note, but there is no any example with hint INDEX and also with JOIN as in my occasion.

Please advice.

Here is SQL statement:

SELECT  P~BWASL
          A~LNRAN
          P~ANLN1 P~ANLN2 K~MONAT P~AFABE
          G~GITCOL
          NAFAV          SAFAV
          AAFAV          MAFAV
          AUFNV          NAFAL
          SAFAL          AAFAL
          MAFAL
     FROM ANEA AS A
                JOIN ANEK AS K ON K~BUKRS = A~BUKRS
                              AND K~ANLN1 = A~ANLN1
                              AND K~ANLN2 = A~ANLN2
                              AND K~GJAHR = A~GJAHR
                              AND K~LNRAN = A~LNRAN
                JOIN ANEP AS P ON P~BUKRS = A~BUKRS
                              AND P~ANLN1 = A~ANLN1
                              AND P~ANLN2 = A~ANLN2
                              AND P~GJAHR = A~GJAHR
                              AND P~LNRAN = A~LNRAN
                              AND P~AFABE = A~AFABE
                              AND P~ZUJHR = A~ZUJHR
                              AND P~ZUCOD = A~ZUCOD
                JOIN TABW AS T ON T~BWASL = P~BWASL
                JOIN TABWG AS G ON G~BWAGRP = T~BWAGRP
     INTO LS_AMSUM_EXT
                WHERE
                      A~BUKRS = 'SNG'
                  AND A~ANLN1 IN s_anln1
                  AND A~ANLN2 =  '0000'
                  AND A~GJAHR = '2008'
                  AND A~AFABE IN ('01' , '02', '10' , '11')
                  AND K~MONAT <= '6'

 %_HINTS DB2 'INDEX("ANEP" "ANEP~Z1")'
.

Re: DB2 optimizer

Former Member — Wed, 18 Jun 2008 13:15:00 GMT

It looks like you are trying to force the SELECT to use a specific index on table ANEP when there are no fields from that table in the WHERE, so it uses a full table scan anyway.

Rob

Re: DB2 optimizer

Former Member — Wed, 18 Jun 2008 13:23:38 GMT

Hi Andrey,

do you really run DB2 on zOS, or do you run DB6 (DB2 on LUW (Linux-Unix-Windows)?

I would suggest first to test SUBSTITUTE LITERALS hint. This is in most cases enough on DB2 as well as DB6. If not you should open a ticket at SAP. There are no Hints in DB2. DB2 has a similar feature called Optimizer Guidelines (check DB2 9.5 documentation), it is much more flexible than Hints in Oracle but there is no documentation how to use it in SAP applications.

Regards

Ralph

Re: DB2 optimizer

Former Member — Thu, 19 Jun 2008 03:27:55 GMT

Hi Ralph and Rob .

Thanks a lot for very usefull advises.

We use DB2 for Z/OS.

I'll try to implement your recomendations and will write about results later.