any db2 resources need to aware if the db2 did not down or restart 6 months? thanks

Tsui Yuk Kai

any db2 resources need to aware if the db2 did not down or restart 6 months? thanks
2010/12/2, Dave Nance <[login to unmask email]>:
> 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

--
±q§Úªº¦æ°Ê¸Ë¸m¶Ç°e

_____________________________________________________________________
* 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