DB2 9 on z/os Access path/Optimizer

Dave Nance

DB2 9 on z/os Access path/Optimizer
   I've been told several times that the ordering of tables/predicates no longer
matters to the optimizer. I just found a case of this in V9 and was wondering if
anyone else had as well.

Here is the original query that was provided to me.
SELECT  A.B_SYS_ID
      , F.B_ALT_ID
      , B.B_LAST_NAM
      , B.B_FST_NAM
      , C.B_PRIM_PHON_NUM
      , A.E_RECIP_REC_CLO_RSN_CD
      , A.E_RECIP_RISK_CD
      , E.B_CS_ID_NUM
      , E.B_CS_HEAD_FST_NAM
      , E.B_CS_HEAD_LAST_NAM            
   FROM NCAPABT1.E_RECIP_TB        A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
   ON   A.B_SYS_ID       = C.B_SYS_ID
  AND   C.R_PAYR_ID      = 1
  AND   C.B_ADDR_TY_CD   = 'R'  
  AND   C.B_ADDR_BEG_DT <= '2010-12-01'
  AND   C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB          B
   ON   A.B_SYS_ID       = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB   E
   ON   B.B_CS_ID_NUM    = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB            F
   ON   B.B_SYS_ID = F.B_SYS_ID
  AND   F.B_ALT_ID_TY_CD = 'C'
  AND   F.B_ALT_ID       = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
  AND B.B_LTST_CNTY_CD  IN (SELECT  EHCT.E_NTWRK_CNTY_CD
                               FROM NCAPABT1.E_CURR_STAFF_TB ECST
                                  , NCAPABT1.E_HCC_CNTY_TB   EHCT
                            WHERE ECST.E_STAFF_ID         = EHCT.E_STAFF_ID
                              AND ECST.E_STAFF_NCID       = 'SHCHCCR'
                              AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
                              AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

   The problem with the above is that even though there is an index on the
B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB            F, the
optimizer is choosing a tablespace scan on the A table in the above. That column
is close to unique, but may have a dup or 2 for some entries. The above query
runs in just under a minute with almost 30 seconds of CPU. When I change the
order of the joins(in bold below) around I get the index access I think I should
have had to start and the query runs subsecond. Appreciate any feed back.


SELECT  A.B_SYS_ID
      , F.B_ALT_ID
      , B.B_LAST_NAM
      , B.B_FST_NAM
      , C.B_PRIM_PHON_NUM
      , A.E_RECIP_REC_CLO_RSN_CD
      , A.E_RECIP_RISK_CD
      , E.B_CS_ID_NUM
      , E.B_CS_HEAD_FST_NAM
      , E.B_CS_HEAD_LAST_NAM            
   FROM NCAPABT1.E_RECIP_TB        A 
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
   ON   A.B_SYS_ID       = C.B_SYS_ID
  AND   C.R_PAYR_ID      = 1
  AND   C.B_ADDR_TY_CD   = 'R'  
  AND   C.B_ADDR_BEG_DT <= '2010-12-01'
  AND   C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB            F
   ON   A.B_SYS_ID       = F.B_SYS_ID
  AND   F.B_ALT_ID_TY_CD = 'C'
  AND   F.B_ALT_ID       = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB          B
   ON   F.B_SYS_ID       = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB   E
   ON   B.B_CS_ID_NUM    = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
  AND B.B_LTST_CNTY_CD  IN (SELECT  EHCT.E_NTWRK_CNTY_CD
                               FROM NCAPABT1.E_CURR_STAFF_TB ECST
                                  , NCAPABT1.E_HCC_CNTY_TB   EHCT
                            WHERE ECST.E_STAFF_ID         = EHCT.E_STAFF_ID
                              AND ECST.E_STAFF_NCID       = 'SHCHCCR'
                              AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
                              AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR 
David Nance





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 9 on z/os Access path/Optimizer
(in response to Dave Nance)
Dave,

IIRC there was a recent 'IBM Database Magazine' (i.e. the magazine formerly known as DB2 Mag) by Bonnie Baker (again, IIRC) suggesting that yes, shock/horror, sometimes predicate order does matter. Don't have it to hand but pretty sure it was the current one, so have a quick butcher's in there and see what she says.

Cheers,


Raymond


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: 01 December 2010 17:36
To: [login to unmask email]
Subject: [DB2-L] DB2 9 on z/os Access path/Optimizer

I've been told several times that the ordering of tables/predicates no longer matters to the optimizer. I just found a case of this in V9 and was wondering if anyone else had as well.

Here is the original query that was provided to me.
SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB B
ON A.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

The problem with the above is that even though there is an index on the B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB F, the optimizer is choosing a tablespace scan on the A table in the above. That column is close to unique, but may have a dup or 2 for some entries. The above query runs in just under a minute with almost 30 seconds of CPU. When I change the order of the joins(in bold below) around I get the index access I think I should have had to start and the query runs subsecond. Appreciate any feed back.

SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB F
ON A.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB B
ON F.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR
David Nance



________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

James Campbell

Re: DB2 9 on z/os Access path/Optimizer
(in response to Raymond Bell)
Here is the link for the latest issue....

http://www.ibmdmmagazinedigital.com/dmmagazine/issue_4_2010#pg1


Jim Campbell
Sr. Database Administrator
360-704-4015
[login to unmask email]<mailto:[login to unmask email]>

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Wednesday, December 01, 2010 9:40 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer

Dave,

IIRC there was a recent 'IBM Database Magazine' (i.e. the magazine formerly known as DB2 Mag) by Bonnie Baker (again, IIRC) suggesting that yes, shock/horror, sometimes predicate order does matter. Don't have it to hand but pretty sure it was the current one, so have a quick butcher's in there and see what she says.

Cheers,


Raymond


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: 01 December 2010 17:36
To: [login to unmask email]
Subject: [DB2-L] DB2 9 on z/os Access path/Optimizer

I've been told several times that the ordering of tables/predicates no longer matters to the optimizer. I just found a case of this in V9 and was wondering if anyone else had as well.

Here is the original query that was provided to me.
SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB B
ON A.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

The problem with the above is that even though there is an index on the B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB F, the optimizer is choosing a tablespace scan on the A table in the above. That column is close to unique, but may have a dup or 2 for some entries. The above query runs in just under a minute with almost 30 seconds of CPU. When I change the order of the joins(in bold below) around I get the index access I think I should have had to start and the query runs subsecond. Appreciate any feed back.

SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB F
ON A.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB B
ON F.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR
David Nance



________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Roy Boxwell

Re: DB2 9 on z/os Access path/Optimizer
(in response to James Campbell)
whoever told you that was wrong! Picture the following simple scenario

The optimizer costs out the access paths and ends up with two with "the
same" cost - Which one does it take? All things being equal the "first"
one in the list...

Now of course you have to "know" which is first and "why" the optimizer
thinks the access paths have "same" costs. Im my experience the access
path needed stats were old/wrong and DB2 then basically flipped a coin to
get the access path order.

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Dave Nance <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
01.12.2010 18:36
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] DB2 9 on z/os Access path/Optimizer






I've been told several times that the ordering of tables/predicates no
longer matters to the optimizer. I just found a case of this in V9 and was
wondering if anyone else had as well.

Here is the original query that was provided to me.
SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB B
ON A.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID =
EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

The problem with the above is that even though there is an index on the
B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB F,
the optimizer is choosing a tablespace scan on the A table in the above.
That column is close to unique, but may have a dup or 2 for some entries.
The above query runs in just under a minute with almost 30 seconds of CPU.
When I change the order of the joins(in bold below) around I get the index
access I think I should have had to start and the query runs subsecond.
Appreciate any feed back.

SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB F
ON A.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB B
ON F.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID =
EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR
David Nance





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 9 on z/os Access path/Optimizer
(in response to Roy Boxwell)
That's the one, Jim. Page 50, Bonnie Baker's article entitled, 'Does the order of SQL predicates matter?'. Short answer; in a few specific conditions, yes. Long answer; read the article!

Cheers,


Raymond

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Campbell, Jim
Sent: 01 December 2010 21:02
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer

Here is the link for the latest issue....

http://www.ibmdmmagazinedigital.com/dmmagazine/issue_4_2010#pg1


Jim Campbell
Sr. Database Administrator
360-704-4015
[login to unmask email]<mailto:[login to unmask email]>

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Wednesday, December 01, 2010 9:40 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer

Dave,

IIRC there was a recent 'IBM Database Magazine' (i.e. the magazine formerly known as DB2 Mag) by Bonnie Baker (again, IIRC) suggesting that yes, shock/horror, sometimes predicate order does matter. Don't have it to hand but pretty sure it was the current one, so have a quick butcher's in there and see what she says.

Cheers,


Raymond


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: 01 December 2010 17:36
To: [login to unmask email]
Subject: [DB2-L] DB2 9 on z/os Access path/Optimizer

I've been told several times that the ordering of tables/predicates no longer matters to the optimizer. I just found a case of this in V9 and was wondering if anyone else had as well.

Here is the original query that was provided to me.
SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB B
ON A.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

The problem with the above is that even though there is an index on the B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB F, the optimizer is choosing a tablespace scan on the A table in the above. That column is close to unique, but may have a dup or 2 for some entries. The above query runs in just under a minute with almost 30 seconds of CPU. When I change the order of the joins(in bold below) around I get the index access I think I should have had to start and the query runs subsecond. Appreciate any feed back.

SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB F
ON A.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB B
ON F.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR
David Nance



________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Terry Purcell

Re: DB2 9 on z/os Access path/Optimizer
(in response to Raymond Bell)
Bonnie's article discusses predicate coding sequence. Good article, and although Dave mentions predicate order in his original post, the issue here is related to table order.

In general, the order of tables in the FROM clause does not impact the optimizer's ability to determine the lowest cost access path. However, there are some outer join situations where DB2's query transformation will introduce materialization to resolve LEFT JOIN/INNER JOIN combinations - meaning that matching index access is not an available choice to optimizer.

One way to completely avoid this issue is to group all inner joins together (where join conditions permit) and code left joins after inner joins.

Regards
Terry Purcell


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Wednesday, December 01, 2010 9:40 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer


That’s the one, Jim. Page 50, Bonnie Baker’s article entitled, ‘Does the order of SQL predicates matter?’. Short answer; in a few specific conditions, yes. Long answer; read the article!

Cheers,

Raymond



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Campbell, Jim
Sent: 01 December 2010 21:02
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer



Here is the link for the latest issue….



http://www.ibmdmmagazinedigital.com/dmmagazine/issue_4_2010#pg1





Jim Campbell
Sr. Database Administrator
360-704-4015 begin_of_the_skype_highlighting 360-704-4015 end_of_the_skype_highlighting
[login to unmask email]



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Wednesday, December 01, 2010 9:40 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer



Dave,



IIRC there was a recent ‘IBM Database Magazine’ (i.e. the magazine formerly known as DB2 Mag) by Bonnie Baker (again, IIRC) suggesting that yes, shock/horror, sometimes predicate order does matter. Don’t have it to hand but pretty sure it was the current one, so have a quick butcher’s in there and see what she says.



Cheers,





Raymond





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: 01 December 2010 17:36
To: [login to unmask email]
Subject: [DB2-L] DB2 9 on z/os Access path/Optimizer



I've been told several times that the ordering of tables/predicates no longer matters to the optimizer. I just found a case of this in V9 and was wondering if anyone else had as well.



Here is the original query that was provided to me.

SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB B
ON A.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR



The problem with the above is that even though there is an index on the B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB F, the optimizer is choosing a tablespace scan on the A table in the above. That column is close to unique, but may have a dup or 2 for some entries. The above query runs in just under a minute with almost 30 seconds of CPU. When I change the order of the joins(in bold below) around I get the index access I think I should have had to start and the query runs subsecond. Appreciate any feed back.


SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB F
ON A.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB B
ON F.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

David Nance





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 9 on z/os Access path/Optimizer
(in response to Terry Purcell)
Hi David

As you can imagine, here at Cogito we're always interested in peoples more "esoteric" SQL, so your question caught our eye

On examining it closer though, our Martin Cox noticed a difference between the two statements

The first one has

JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID

The second one has

JOIN NCAPABT1.B_XREF_TB F
ON A.B_SYS_ID = F.B_SYS_ID

So, that ON clause actually specifies different tables. Perhaps THAT has more to do with the change of access path rather than the predicate order?

So far as I know, DB2 can't do ON clause transitive closure yet either
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: 01 December 2010 17:36
To: [login to unmask email]
Subject: [DB2-L] DB2 9 on z/os Access path/Optimizer

I've been told several times that the ordering of tables/predicates no longer matters to the optimizer. I just found a case of this in V9 and was wondering if anyone else had as well.

Here is the original query that was provided to me.
SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB B
ON A.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

The problem with the above is that even though there is an index on the B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB F, the optimizer is choosing a tablespace scan on the A table in the above. That column is close to unique, but may have a dup or 2 for some entries. The above query runs in just under a minute with almost 30 seconds of CPU. When I change the order of the joins(in bold below) around I get the index access I think I should have had to start and the query runs subsecond. Appreciate any feed back.

SELECT A.B_SYS_ID
, F.B_ALT_ID
, B.B_LAST_NAM
, B.B_FST_NAM
, C.B_PRIM_PHON_NUM
, A.E_RECIP_REC_CLO_RSN_CD
, A.E_RECIP_RISK_CD
, E.B_CS_ID_NUM
, E.B_CS_HEAD_FST_NAM
, E.B_CS_HEAD_LAST_NAM
FROM NCAPABT1.E_RECIP_TB A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
ON A.B_SYS_ID = C.B_SYS_ID
AND C.R_PAYR_ID = 1
AND C.B_ADDR_TY_CD = 'R'
AND C.B_ADDR_BEG_DT <= '2010-12-01'
AND C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB F
ON A.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
AND F.B_ALT_ID = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB B
ON F.B_SYS_ID = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
AND B.B_LTST_CNTY_CD IN (SELECT EHCT.E_NTWRK_CNTY_CD
FROM NCAPABT1.E_CURR_STAFF_TB ECST
, NCAPABT1.E_HCC_CNTY_TB EHCT
WHERE ECST.E_STAFF_ID = EHCT.E_STAFF_ID
AND ECST.E_STAFF_NCID = 'SHCHCCR'
AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR
David Nance



________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Dave Nance

Re: DB2 9 on z/os Access path/Optimizer
(in response to Phil Grainger)
Phil,
   That had to change for the F table, as that table was moved prior to the join
to the B table. I did not, however need to change the join condition on the B
table, as I did in what I sent out, meaning the B table being joined to F or A,
I still get the desired access path. Also, I moved the indexable predicate into
the ON clause where the developer had it in the WHERE clause, that made
absolutely no difference in access path, it was just my preference in writing.
 
David Nance




________________________________
From: Phil Grainger <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, December 2, 2010 4:41:35 AM
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer


Hi David
 
As you can imagine, here at Cogito we’re always interested in peoples more
“esoteric” SQL, so your question caught our eye
 
On examining it closer though, our Martin Cox noticed a difference between the
two statements
 
The first one has
 
JOIN NCAPABT1.B_XREF_TB            F
   ON   B.B_SYS_ID = F.B_SYS_ID


The second one has
 
JOIN NCAPABT1.B_XREF_TB            F
   ON   A.B_SYS_ID       = F.B_SYS_ID
 
So, that ON clause actually specifies different tables. Perhaps THAT has more to
do with the change of access path rather than the predicate order?
 
So far as I know, DB2 can’t do ON clause transitive closure yet either
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk
 
Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic


 
From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: 01 December 2010 17:36
To: [login to unmask email]
Subject: [DB2-L] DB2 9 on z/os Access path/Optimizer
 
   I've been told several times that the ordering of tables/predicates no longer
matters to the optimizer. I just found a case of this in V9 and was wondering if
anyone else had as well.
 
Here is the original query that was provided to me.
SELECT  A.B_SYS_ID
      , F.B_ALT_ID
      , B.B_LAST_NAM
      , B.B_FST_NAM
      , C.B_PRIM_PHON_NUM
      , A.E_RECIP_REC_CLO_RSN_CD
      , A.E_RECIP_RISK_CD
      , E.B_CS_ID_NUM
      , E.B_CS_HEAD_FST_NAM
      , E.B_CS_HEAD_LAST_NAM            
   FROM NCAPABT1.E_RECIP_TB        A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
   ON   A.B_SYS_ID       = C.B_SYS_ID
  AND   C.R_PAYR_ID      = 1
  AND   C.B_ADDR_TY_CD   = 'R'  
  AND   C.B_ADDR_BEG_DT <= '2010-12-01'
  AND   C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB          B
   ON   A.B_SYS_ID       = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB   E
   ON   B.B_CS_ID_NUM    = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB            F
   ON   B.B_SYS_ID = F.B_SYS_ID
  AND   F.B_ALT_ID_TY_CD = 'C'
  AND   F.B_ALT_ID       = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
  AND B.B_LTST_CNTY_CD  IN (SELECT  EHCT.E_NTWRK_CNTY_CD
                               FROM NCAPABT1.E_CURR_STAFF_TB ECST
                                  , NCAPABT1.E_HCC_CNTY_TB   EHCT
                            WHERE ECST.E_STAFF_ID         = EHCT.E_STAFF_ID
                              AND ECST.E_STAFF_NCID       = 'SHCHCCR'
                              AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
                              AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR
 
   The problem with the above is that even though there is an index on the
B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB            F, the
optimizer is choosing a tablespace scan on the A table in the above. That column
is close to unique, but may have a dup or 2 for some entries. The above query
runs in just under a minute with almost 30 seconds of CPU. When I change the
order of the joins(in bold below) around I get the index access I think I should
have had to start and the query runs subsecond. Appreciate any feed back.

SELECT  A.B_SYS_ID
      , F.B_ALT_ID
      , B.B_LAST_NAM
      , B.B_FST_NAM
      , C.B_PRIM_PHON_NUM
      , A.E_RECIP_REC_CLO_RSN_CD
      , A.E_RECIP_RISK_CD
      , E.B_CS_ID_NUM
      , E.B_CS_HEAD_FST_NAM
      , E.B_CS_HEAD_LAST_NAM            
   FROM NCAPABT1.E_RECIP_TB        A 
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
   ON   A.B_SYS_ID       = C.B_SYS_ID
  AND   C.R_PAYR_ID      = 1
  AND   C.B_ADDR_TY_CD   = 'R'  
  AND   C.B_ADDR_BEG_DT <= '2010-12-01'
  AND   C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB            F
   ON   A.B_SYS_ID       = F.B_SYS_ID
  AND   F.B_ALT_ID_TY_CD = 'C'
  AND   F.B_ALT_ID       = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB          B
   ON   F.B_SYS_ID       = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB   E
   ON   B.B_CS_ID_NUM    = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
  AND B.B_LTST_CNTY_CD  IN (SELECT  EHCT.E_NTWRK_CNTY_CD
                               FROM NCAPABT1.E_CURR_STAFF_TB ECST
                                  , NCAPABT1.E_HCC_CNTY_TB   EHCT
                            WHERE ECST.E_STAFF_ID         = EHCT.E_STAFF_ID
                              AND ECST.E_STAFF_NCID       = 'SHCHCCR'
                              AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
                              AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR 
David Nance
 
 
 

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Dave Nance

Re: DB2 9 on z/os Access path/Optimizer
(in response to Dave Nance)
Terry,
   Thanks for the reply. It fits right along with other queries I have put to
the list in the past or to you or Pat. I did as you suggested below and do get
the desired access path. By moving the LEFT OUTER JOIN to the E table to just
after the INNER join to the F table, I still get the desired access path. As:

Original:
....
JOIN NCAPABT1.B_DETAIL_TB          B
  ON  A.B_SYS_ID      = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB  E
  ON  B.B_CS_ID_NUM    = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB            F
  ON  B.B_SYS_ID = F.B_SYS_ID
  AND  F.B_ALT_ID_TY_CD = 'C'
WHERE A.E_RECIP_DELQ_IND = 'N'
  AND  F.B_ALT_ID      = '101013102I'
....

Re-ordered
...
JOIN NCAPABT1.B_DETAIL_TB B
ON A.B_SYS_ID = B.B_SYS_ID
JOIN NCAPABT1.B_XREF_TB F
ON B.B_SYS_ID = F.B_SYS_ID
AND F.B_ALT_ID_TY_CD = 'C'
LEFT OUTER JOIN NCAPABT1.B_CS_TB E
ON B.B_CS_ID_NUM = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
AND F.B_ALT_ID = '101013102I'.....
    I think this illustrates my point even better, as I leave the query more
like the original, yet get the desired access path, just rearranging the order
of tables in the from clause. The rewritten query I sent yesterday, I did a bit
more than change the order, because of changing the order, I, also, had to
change which table I joined to for my F table.

David Nance




________________________________
From: Terry Purcell <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, December 2, 2010 4:07:26 AM
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer

Bonnie's article discusses predicate coding sequence. Good article, and although
Dave mentions predicate order in his original post, the issue here is related to
table order.

In general, the order of tables in the FROM clause does not impact the
optimizer's ability to determine the lowest cost access path. However, there are
some outer join situations where DB2's query transformation will introduce
materialization to resolve LEFT JOIN/INNER JOIN combinations - meaning that
matching index access is not an available choice to optimizer.

One way to completely avoid this issue is to group all inner joins together
(where join conditions permit) and code left joins after inner joins.

Regards
Terry Purcell


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Wednesday, December 01, 2010 9:40 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 9 on z/os Access path/Optimizer



Dave,



IIRC there was a recent ‘IBM Database Magazine’ (i.e. the magazine formerly
known as DB2 Mag) by Bonnie Baker (again, IIRC) suggesting that yes,
shock/horror, sometimes predicate order does matter.  Don’t have it to hand but
pretty sure it was the current one, so have a quick butcher’s in there and see
what she says.



Cheers,





Raymond





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: 01 December 2010 17:36
To: [login to unmask email]
Subject: [DB2-L] DB2 9 on z/os Access path/Optimizer



  I've been told several times that the ordering of tables/predicates no longer
matters to the optimizer. I just found a case of this in V9 and was wondering if
anyone else had as well.



Here is the original query that was provided to me.

SELECT  A.B_SYS_ID
      , F.B_ALT_ID
      , B.B_LAST_NAM
      , B.B_FST_NAM
      , C.B_PRIM_PHON_NUM
      , A.E_RECIP_REC_CLO_RSN_CD
      , A.E_RECIP_RISK_CD
      , E.B_CS_ID_NUM
      , E.B_CS_HEAD_FST_NAM
      , E.B_CS_HEAD_LAST_NAM           
  FROM NCAPABT1.E_RECIP_TB        A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
  ON  A.B_SYS_ID      = C.B_SYS_ID
  AND  C.R_PAYR_ID      = 1
  AND  C.B_ADDR_TY_CD  = 'R' 
  AND  C.B_ADDR_BEG_DT <= '2010-12-01'
  AND  C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_DETAIL_TB          B
  ON  A.B_SYS_ID      = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB  E
  ON  B.B_CS_ID_NUM    = E.B_CS_ID_NUM
JOIN NCAPABT1.B_XREF_TB            F
  ON  B.B_SYS_ID = F.B_SYS_ID
  AND  F.B_ALT_ID_TY_CD = 'C'
  AND  F.B_ALT_ID      = '101013102I'
WHERE A.E_RECIP_DELQ_IND = 'N'
  AND B.B_LTST_CNTY_CD  IN (SELECT  EHCT.E_NTWRK_CNTY_CD
                              FROM NCAPABT1.E_CURR_STAFF_TB ECST
                                  , NCAPABT1.E_HCC_CNTY_TB  EHCT
                            WHERE ECST.E_STAFF_ID        = EHCT.E_STAFF_ID
                              AND ECST.E_STAFF_NCID      = 'SHCHCCR'
                              AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
                              AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR



  The problem with the above is that even though there is an index on the
B_ALT_ID(in bold above column in table NCAPABT1.B_XREF_TB            F, the
optimizer is choosing a tablespace scan on the A table in the above. That column
is close to unique, but may have a dup or 2 for some entries. The above query
runs in just under a minute with almost 30 seconds of CPU. When I change the
order of the joins(in bold below) around I get the index access I think I should
have had to start and the query runs subsecond. Appreciate any feed back.


SELECT  A.B_SYS_ID
      , F.B_ALT_ID
      , B.B_LAST_NAM
      , B.B_FST_NAM
      , C.B_PRIM_PHON_NUM
      , A.E_RECIP_REC_CLO_RSN_CD
      , A.E_RECIP_RISK_CD
      , E.B_CS_ID_NUM
      , E.B_CS_HEAD_FST_NAM
      , E.B_CS_HEAD_LAST_NAM           
  FROM NCAPABT1.E_RECIP_TB        A
LEFT OUTER JOIN NCAPABT1.B_ADDR_TB C
  ON  A.B_SYS_ID      = C.B_SYS_ID
  AND  C.R_PAYR_ID      = 1
  AND  C.B_ADDR_TY_CD  = 'R' 
  AND  C.B_ADDR_BEG_DT <= '2010-12-01'
  AND  C.B_ADDR_END_DT >= '2010-12-01'
JOIN NCAPABT1.B_XREF_TB            F
  ON  A.B_SYS_ID      = F.B_SYS_ID
  AND  F.B_ALT_ID_TY_CD = 'C'
  AND  F.B_ALT_ID      = '101013102I'
JOIN NCAPABT1.B_DETAIL_TB          B
  ON  F.B_SYS_ID      = B.B_SYS_ID
LEFT OUTER JOIN NCAPABT1.B_CS_TB  E
  ON  B.B_CS_ID_NUM    = E.B_CS_ID_NUM
WHERE A.E_RECIP_DELQ_IND = 'N'
  AND B.B_LTST_CNTY_CD  IN (SELECT  EHCT.E_NTWRK_CNTY_CD
                              FROM NCAPABT1.E_CURR_STAFF_TB ECST
                                  , NCAPABT1.E_HCC_CNTY_TB  EHCT
                            WHERE ECST.E_STAFF_ID        = EHCT.E_STAFF_ID
                              AND ECST.E_STAFF_NCID      = 'SHCHCCR'
                              AND EHCT.E_HCC_CNTY_BEG_DT <= CURRENT DATE
                              AND EHCT.E_HCC_CNTY_END_DT >= CURRENT DATE)
WITH UR

David Nance





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA
*
*  If you are going to attend only one conference this year, this is it!  *
**  The most DB2 technical sessions of any conference
**  Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the
home of IDUG's Listserv





_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv