RID POOL CONCURRENT PROCESSES

Ken Hynes

RID POOL CONCURRENT PROCESSES
I'm a bit "confused here. We have a system that appears to have plenty of
available storage, but still shows failures due to Concurrent Processes
(Storage) in the STATS records. If anyone has any suggestions please let me
know. While we can incrase the RID Pool something just does not add up
based on the numbers below --- as far as truly needing to. Is there an
internal "Cap" on concurrent processes that is not tied to storage? Not
seen this before and more than a bit curious.

.....Ken Hynes


RID Pool Failures on Concurrent Process

DSNZPARM allocates 2048 16K blocks = 33,554,432 bytes

Peak RID Blocks show as 4098 4K blocks = 16,785,406 bytes

BPool Total = 260,096,000 bytes
50% of this = 130,048,000 bytes

If the RID Pool "Can" grow to 50% of the BPool and the DSNZPARM shows an
intial request of approximately 32MEG why would we have "Any" failures on
concurrent processes as we haven't reached the maximum requested RID Pool
nor have we reached 50% of the Buffer Pools?

NOTE: Virtual Private shows 1091M of available storagfe so lack of Virtual
Private is not an issue.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: RID POOL CONCURRENT PROCESSES
(in response to Ken Hynes)
Ken,
There are two 50% numbers in RID POOL management I know off

1. A single thread can not use more than 50% of the RID pool
2. If no RID Pool is allocated the size is calculated as the lesser of
50% of BP0 BP1 BP2 BP32K
or 200K
The RID pool does not grow to 50% of total buffer pool size by default. and the 50% total size rule does not apply if you have MAXRBLK specified in ZPARM

There are many different causes for RID Pool failues, Have you checked a STAT report or IFCID 2?

As a example:
One of the causes of failure is more than 25% of the rows in a table are expected to be pointed to in the RID Pool. This failure is called "RDS LIMIT Reached". For a small talble, according to RUNSTATS it can occur with a very small number of rows.

My own personal crystal ball suggests the long term solution to the RID pool design problems dateing back to DB2 V2 is that Star Join will be enhanced to replace RID pool processing. Star Join has the advantage on the entries being managed in DB07 rather than an old limited VS storage method.

The short term fix for RID pool problems is to over populate indexs with additional columns so single index access works.

Normal rules like makeing sure RUNSTATS are current and tables / indexes are reorged if needed should be followed as well.


Disclaimers:
I am a systems programmer not a DBA so not 100% qualified to speak of any data base design issue.
I am not privy to any unanounced IBM design plans and indeed do not even know all of the announced ones.

Ken Hynes <[login to unmask email]> wrote:
I'm a bit "confused here. We have a system that appears to have plenty of
available storage, but still shows failures due to Concurrent Processes
(Storage) in the STATS records. If anyone has any suggestions please let me
know. While we can incrase the RID Pool something just does not add up
based on the numbers below --- as far as truly needing to. Is there an
internal "Cap" on concurrent processes that is not tied to storage? Not
seen this before and more than a bit curious.

.....Ken Hynes


RID Pool Failures on Concurrent Process

DSNZPARM allocates 2048 16K blocks = 33,554,432 bytes

Peak RID Blocks show as 4098 4K blocks = 16,785,406 bytes

BPool Total = 260,096,000 bytes
50% of this = 130,048,000 bytes

If the RID Pool "Can" grow to 50% of the BPool and the DSNZPARM shows an
intial request of approximately 32MEG why would we have "Any" failures on
concurrent processes as we haven't reached the maximum requested RID Pool
nor have we reached 50% of the Buffer Pools?

NOTE: Virtual Private shows 1091M of available storagfe so lack of Virtual
Private is not an issue.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ken Hynes

Re: RID POOL CONCURRENT PROCESSES
(in response to Avram Friedman)
On Wed, 7 Dec 2005 19:15:36 -0800, Avram Friedman <[login to unmask email]>
wrote:

>Ken,
> There are two 50% numbers in RID POOL management I know off
>
> 1. A single thread can not use more than 50% of the RID pool
> 2. If no RID Pool is allocated the size is calculated as the lesser of
> 50% of BP0 BP1 BP2 BP32K
> or 200K
> The RID pool does not grow to 50% of total buffer pool size by default.
and the 50% total size rule does not apply if you have MAXRBLK specified in
ZPARM
>
> There are many different causes for RID Pool failues, Have you checked
a STAT report or IFCID 2?
>
> As a example:
> One of the causes of failure is more than 25% of the rows in a table are
expected to be pointed to in the RID Pool. This failure is called "RDS
LIMIT Reached". For a small talble, according to RUNSTATS it can occur
with a very small number of rows.
>
> My own personal crystal ball suggests the long term solution to the RID
pool design problems dateing back to DB2 V2 is that Star Join will be
enhanced to replace RID pool processing. Star Join has the advantage on
the entries being managed in DB07 rather than an old limited VS storage
method.
>
> The short term fix for RID pool problems is to over populate indexs with
additional columns so single index access works.
>
> Normal rules like makeing sure RUNSTATS are current and tables / indexes
are reorged if needed should be followed as well.
>
>
> Disclaimers:
> I am a systems programmer not a DBA so not 100% qualified to speak of
any data base design issue.
> I am not privy to any unanounced IBM design plans and indeed do not even
know all of the announced ones.
>
>Ken Hynes <[login to unmask email]> wrote:
> I'm a bit "confused here. We have a system that appears to have plenty of
>available storage, but still shows failures due to Concurrent Processes
>(Storage) in the STATS records. If anyone has any suggestions please let me
>know. While we can incrase the RID Pool something just does not add up
>based on the numbers below --- as far as truly needing to. Is there an
>internal "Cap" on concurrent processes that is not tied to storage? Not
>seen this before and more than a bit curious.
>
>.....Ken Hynes
>
>
>RID Pool Failures on Concurrent Process
>
>DSNZPARM allocates 2048 16K blocks = 33,554,432 bytes
>
>Peak RID Blocks show as 4098 4K blocks = 16,785,406 bytes
>
>BPool Total = 260,096,000 bytes
>50% of this = 130,048,000 bytes
>
>If the RID Pool "Can" grow to 50% of the BPool and the DSNZPARM shows an
>intial request of approximately 32MEG why would we have "Any" failures on
>concurrent processes as we haven't reached the maximum requested RID Pool
>nor have we reached 50% of the Buffer Pools?
>
>NOTE: Virtual Private shows 1091M of available storagfe so lack of Virtual
>Private is not an issue.
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>
>
>
>
>Avram Friedman
>(877)311-0480 Voice Mail
>[login to unmask email]
>Http://www.IBMsysProg.com
>
>
>
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>

Avram ,

Thanks for the input. I have a predefined RID Pool with the
allocations I gave earlier so your right the 50% of the Pool doesn't
apply. I am aware of the 50% of the Pool and 25% of the table rules for
the RIDS. What I haven't been able to find in the Doc is anything directly
related to the Failed Processes. Since I have available storage I think an
increase in the RID Pool wouldn't hurt as we are showing some other
failures. Normally I'm not too "excited about the RDS limit failures or the
RID Limit failures based on the resons you outlined -- as long as they are
a small percentage of the Times Used. One thing interesting is the Failed
Processes maps directly to the List Prefetch.

Heres a "Snapshot" of some STATS.

LIST PREFETCH
Number of Times Used 37181K
Failed - No Storage 4
Failed - RID Limit 4283

RID POOL
RID Pool Current Blks 0
RID Pool Peak Blocks 8042
RID Failed RDS Limit 1841
RID Failed DM Limit 0
RID Failed No Storage 0
RID Failed Processes 4

<BTW> Love the Disclaimer

Regards.....Ken Hynes

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm