INDEXED COLUMNS GOING FOR TABLESPACE SCAN

[login to unmask email]

INDEXED COLUMNS GOING FOR TABLESPACE SCAN
Hi ALL,
I have a typical case where all the columns of the WHERE clause goes
for a Tablespace scan though there is an Index defined for that and the
sequence of the index also matches the sequence of the WHERE PREDICATE.
Actually it should give Matchcols = 5 and Accesstype should be N .
Due to this the plan runs into hours .The plan has only two queries
and the other query has an indexed access with matchcol = 1 .So the
problem is only with this query.
The QUERY IS :

SELECT COUNT(*)
INTO :WS-COUNT-1
FROM HRIA01.V_LV_CONS
WHERE
LVC_YR_MTH = :WS-DATE-PREV3
AND LVC_PERNO = :LVC-PERNO
AND LVC_DATE = :WS-DATE-PREV
AND LVC_LEAVE_CD IN ('06','21')
AND LVC_PAY_REC_IND = :LVC-PAY-REC-IND


The INDEX on the Table is
Ixname : HRIA01.I_LV_CONS
1. LVC_YR_MTH
2. LVC_PERNO
3. LVC_DATE
4. LVC_LEAVE_CD
5. LVC_PAY_REC_IND
6. LVC_TRANS_ID

CLUSTERING = Y
CLUSTER RATIO = 100
FIRSTKEYCARD = 7
FULLKEYCARD = 208684

Could anyone in the list explain this.
Thanx in Advance.


Regards
Ujjal Bhattacharya



Raymond Bell

Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
(in response to ujjwal@LOT.TATASTEEL.COM)
Ujjal,

Do your host variables' definitions match the columns' definitions? If not,
this might explain why this query is only matchcols=1. Actually, it's not
clear to me that this is the case with the statement you've provided. You
say this must be the problem query becaus the other one only has
matchcols=1. Don't quite follow the logic. It may well be the other query
that is the culprit. Have you checked the access path for the query below?

Hope this helps,


Raymond


> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, 11 January 2001 3:14 pm
> To: [login to unmask email]
> Subject: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
>
> Hi ALL,
> I have a typical case where all the columns of the WHERE clause
> goes
> for a Tablespace scan though there is an Index defined for that and the
> sequence of the index also matches the sequence of the WHERE PREDICATE.
> Actually it should give Matchcols = 5 and Accesstype should be N .
> Due to this the plan runs into hours .The plan has only two queries
> and the other query has an indexed access with matchcol = 1 .So the
> problem is only with this query.
> The QUERY IS :
>
> SELECT COUNT(*)
> INTO :WS-COUNT-1
> FROM HRIA01.V_LV_CONS
> WHERE
> LVC_YR_MTH = :WS-DATE-PREV3
> AND LVC_PERNO = :LVC-PERNO
> AND LVC_DATE = :WS-DATE-PREV
> AND LVC_LEAVE_CD IN ('06','21')
> AND LVC_PAY_REC_IND = :LVC-PAY-REC-IND
>
>
> The INDEX on the Table is
> Ixname : HRIA01.I_LV_CONS
> 1. LVC_YR_MTH
> 2. LVC_PERNO
> 3. LVC_DATE
> 4. LVC_LEAVE_CD
> 5. LVC_PAY_REC_IND
> 6. LVC_TRANS_ID
>
> CLUSTERING = Y
> CLUSTER RATIO = 100
> FIRSTKEYCARD = 7
> FULLKEYCARD = 208684
>
> Could anyone in the list explain this.
> Thanx in Advance.
>
>
> Regards
> Ujjal Bhattacharya
>
>
>
>
>



[login to unmask email]

Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
(in response to Raymond Bell)
Hi Raymond ,
The accesspath for the querybelow is Matchcols=0,AccessType= R . The
other query I cannot do anything because the first column of that
particular query is a range predicate .

Regards
Ujjal




"Bell, Raymond W" <[login to unmask email]>@RYCI.COM> on 01/11/2001
09:56:17 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN


Ujjal,

Do your host variables' definitions match the columns' definitions? If
not,
this might explain why this query is only matchcols=1. Actually, it's not
clear to me that this is the case with the statement you've provided. You
say this must be the problem query becaus the other one only has
matchcols=1. Don't quite follow the logic. It may well be the other query
that is the culprit. Have you checked the access path for the query below?

Hope this helps,


Raymond


> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, 11 January 2001 3:14 pm
> To: [login to unmask email]
> Subject: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
>
> Hi ALL,
> I have a typical case where all the columns of the WHERE clause
> goes
> for a Tablespace scan though there is an Index defined for that and the
> sequence of the index also matches the sequence of the WHERE PREDICATE.
> Actually it should give Matchcols = 5 and Accesstype should be N .
> Due to this the plan runs into hours .The plan has only two queries
> and the other query has an indexed access with matchcol = 1 .So the
> problem is only with this query.
> The QUERY IS :
>
> SELECT COUNT(*)
> INTO :WS-COUNT-1
> FROM HRIA01.V_LV_CONS
> WHERE
> LVC_YR_MTH = :WS-DATE-PREV3
> AND LVC_PERNO = :LVC-PERNO
> AND LVC_DATE = :WS-DATE-PREV
> AND LVC_LEAVE_CD IN ('06','21')
> AND LVC_PAY_REC_IND = :LVC-PAY-REC-IND
>
>
> The INDEX on the Table is
> Ixname : HRIA01.I_LV_CONS
> 1. LVC_YR_MTH
> 2. LVC_PERNO
> 3. LVC_DATE
> 4. LVC_LEAVE_CD
> 5. LVC_PAY_REC_IND
> 6. LVC_TRANS_ID
>
> CLUSTERING = Y
> CLUSTER RATIO = 100
> FIRSTKEYCARD = 7
> FULLKEYCARD = 208684
>
> Could anyone in the list explain this.
> Thanx in Advance.
>
>
> Regards
> Ujjal Bhattacharya
>
>
>
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can
>








Raymond Bell

Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
(in response to ujjwal@LOT.TATASTEEL.COM)
OK, so back to plan A and check that your host variable declarations match
your table column data types. Also check that runstats is current. Maybe
DB2 thinks there's hardly any rows, so a TS scan is OK. If so, runstats and
rebind might fix it.

Just some ideas.


Raymond


> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, 11 January 2001 3:30 pm
> To: [login to unmask email]
> Subject: Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
>
> Hi Raymond ,
> The accesspath for the querybelow is Matchcols=0,AccessType= R . The
> other query I cannot do anything because the first column of that
> particular query is a range predicate .
>
> Regards
> Ujjal



Sanjeev (CTS) S

Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
(in response to Raymond Bell)
Hi Ujjal,
I am sure you do have Omegamon and you can check which query is taking
the time. Suppose its first presuming you have checked that and wrote over
here.
Just few ideas and guesses without looking at the complete explain
statements:

1) Check the host variable because i know how the WS-DATE-PREV and other
host variables are defined in your shop. As i have found about the year
month variables at your place earlier, it causes some good problems. All the
other things should be tried if this is not correct.
2) Check your Index Levels. I hope it is correct, mean not more than 3.
3) Try selecting LVC_YR_MTH only instead of COUNT(*) from QMF just for
testing if it uses the INDEXONLY

Let's see what happens

Regards,
Sanjeev
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Rajiva Sinha

Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
(in response to Sanjeev (CTS) S)
Hi Ujjal !

It was nostalgic to see the HRIA code and column names. 8 years back, I was
working on
the data modeling part of it. Seems like sanjeev has worked in that group
too.


Rajiv Sinha




> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Wednesday, January 10, 2001 10:30 PM
> To: [login to unmask email]
> Subject: Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
>
> Hi Raymond ,
> The accesspath for the querybelow is Matchcols=0,AccessType= R . The
> other query I cannot do anything because the first column of that
> particular query is a range predicate .
>
> Regards
> Ujjal
>
>
>
>
> "Bell, Raymond W" <[login to unmask email]>@RYCI.COM> on
> 01/11/2001
> 09:56:17 AM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
> Subject: Re: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
>
>
> Ujjal,
>
> Do your host variables' definitions match the columns' definitions? If
> not,
> this might explain why this query is only matchcols=1. Actually, it's not
> clear to me that this is the case with the statement you've provided. You
> say this must be the problem query becaus the other one only has
> matchcols=1. Don't quite follow the logic. It may well be the other
> query
> that is the culprit. Have you checked the access path for the query
> below?
>
> Hope this helps,
>
>
> Raymond
>
>
> > -----Original Message-----
> > From: [login to unmask email] [SMTP:[login to unmask email]
> > Sent: Thursday, 11 January 2001 3:14 pm
> > To: [login to unmask email]
> > Subject: INDEXED COLUMNS GOING FOR TABLESPACE SCAN
> >
> > Hi ALL,
> > I have a typical case where all the columns of the WHERE clause
> > goes
> > for a Tablespace scan though there is an Index defined for that and the
> > sequence of the index also matches the sequence of the WHERE PREDICATE.
> > Actually it should give Matchcols = 5 and Accesstype should be N .
> > Due to this the plan runs into hours .The plan has only two
> queries
> > and the other query has an indexed access with matchcol = 1 .So the
> > problem is only with this query.
> > The QUERY IS :
> >
> > SELECT COUNT(*)
> > INTO :WS-COUNT-1
> > FROM HRIA01.V_LV_CONS
> > WHERE
> > LVC_YR_MTH = :WS-DATE-PREV3
> > AND LVC_PERNO = :LVC-PERNO
> > AND LVC_DATE = :WS-DATE-PREV
> > AND LVC_LEAVE_CD IN ('06','21')
> > AND LVC_PAY_REC_IND = :LVC-PAY-REC-IND
> >
> >
> > The INDEX on the Table is
> > Ixname : HRIA01.I_LV_CONS
> > 1. LVC_YR_MTH
> > 2. LVC_PERNO
> > 3. LVC_DATE
> > 4. LVC_LEAVE_CD
> > 5. LVC_PAY_REC_IND
> > 6. LVC_TRANS_ID
> >
> > CLUSTERING = Y
> > CLUSTER RATIO = 100
> > FIRSTKEYCARD = 7
> > FULLKEYCARD = 208684
> >
> > Could anyone in the list explain this.
> > Thanx in Advance.
> >
> >
> > Regards
> > Ujjal Bhattacharya
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can
> >
>
>
>
>
>
>
>
>
>
>