RDS LIMIT reached - List prefetch failed

ramesh bala

RDS LIMIT reached - List prefetch failed
Folks, I am wondering how I can tackle this RDS LIMIT issue. My sysprog complains that increasing the DBM1 region size is mute since it is already at 306M. How would I go about tackling this issue
Any pointers would be appreciated
Thanks

LIST PREFETCH STATISTICS
---------------------------------
SUCCESSFUL 6451
FAILED NO STORAGE 0
FAILED OVER LIMIT 134

RID LIST STATISTICS
---------------------------------
RIDLIST RDS LIMIT 134
RIDLIST DM LIMIT 0
RIDLIST MAX BLOCKS 250
RIDLIST AVG BLOCKS 0
RIDLIST NO STORAGE 0
RIDLIST PROC LIMIT 0



Susan Lawson

Re: RDS LIMIT reached - List prefetch failed
(in response to ramesh bala)
The RDS limit will not be solved by an increase in the RID pool.

This is showing the number of times list prefetch is turned off because a
RID list built for a single set of index entries is greater that 25% of
number of rows in the table.

If this is the case, DB2 determines that instead of using list prefetch to
satisfy a query it would be more efficient to perform a tablespace scan,
which may or may not be good depending on the size of the table accessed and
increasing the size of the RID pool will not help in this case.

This is an application issue and access paths and need to be evaluated for
queries using list prefetch.

If you are V6 be sure you have APAR PQ15670 applied, which enables index
screening.

Regards,
Susan Lawson

-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Ramesh Balasubramanyan
Sent: Wednesday, January 15, 2003 6:37 PM
To: [login to unmask email]
Subject: RDS LIMIT reached - List prefetch failed


Folks, I am wondering how I can tackle this RDS LIMIT issue. My sysprog
complains that increasing the DBM1 region size is mute since it is already
at 306M. How would I go about tackling this issue
Any pointers would be appreciated
Thanks

LIST PREFETCH STATISTICS
---------------------------------
SUCCESSFUL 6451
FAILED NO STORAGE 0
FAILED OVER LIMIT 134

RID LIST STATISTICS
---------------------------------
RIDLIST RDS LIMIT 134
RIDLIST DM LIMIT 0
RIDLIST MAX BLOCKS 250
RIDLIST AVG BLOCKS 0
RIDLIST NO STORAGE 0
RIDLIST PROC LIMIT 0

================