RID Pool Failures - Finding the cause ??

Ricky Wafford

RID Pool Failures - Finding the cause ??
We are getting several failures a day on the RID pool because of not enough
storage. We detect this failure in a daily "exception report" job that scans
SMF accounting records looking for problems. These problems just started
recently and some days are really bad and the next day will be problem free.
Obviously, it is workload related. The definition from the exception job
is:

RID POOL FAILURE - STORAGE
NUMBER OF TIMES DB2 DETECTED NO STORAGE WAS AVAILABLE TO HOLD
A LIST OF RIDS DURING A GIVEN RID LIST PROCESS INVOLVING ONE
INDEX (SINGLE INDEX ACCESS WITH LIST PREFETCH) OR INVOLVING
MULTIPLE INDEXES (MULTIPLE INDEX ACCESS). (QXNSMIAP)

After researching these failures, it appears that our exception report is
really identifying the victims of the RID pool storage problem. Another
application job is apparently running and consuming a large portion of the
RID pool which causes these users to fail because of a lack of storage.
Our RID pool is currently set at 40MB. Yes, I can increase it and see if the
problem goes away, but I'm looking for ways to identify the jobs that
consume the RID pool and get those jobs corrected. If those jobs seem to be
legitimate,,, then I will increase the RID pool.
Has anyone tackled this issue, or does anyone have input on how to tackle
this issue ??

Ricky Wafford
MBNA Technology Inc.
Manager - DB2 Technical Support
(469) 201-6271
Mailto:[login to unmask email]

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

Missy Case

Re: RID Pool Failures - Finding the cause ??
(in response to Ricky Wafford)
Ricky,

The first thing to look for to find POTENTIAL culprits is the accesstype
column in the explain table. Many multiple index accesses cause RID pool
usage, then when the number of rows qualifying exceeds the limit, or DB2
decides a TS scan would be cheaper, it discards the RID work & starts a
scan.

If you have CA Subsystem analyzer, there is a RID FLIM (Fail Limit) & other
RID columns that we've found useful. This can nail it down by database,
which helped us realize that the dynamic SQL was our biggest culprit.
Finding the database name was a huge step in the right direction for us.

Otherwise, it's the proverbial needle in a haystack.

Have fun!
Missy Case
FDR
701-275-6358




"Wafford, Ricky"
<[login to unmask email] To: [login to unmask email]
NA.COM> cc:
Sent by: DB2 Data bcc:
Base Discussion Subject: RID Pool Failures - Finding the cause ??
List
<[login to unmask email]
ORG>


05/12/04 11:00 AM
Please respond to
DB2 Database
Discussion list
at IDUG






We are getting several failures a day on the RID pool because of not enough
storage. We detect this failure in a daily "exception report" job that
scans
SMF accounting records looking for problems. These problems just started
recently and some days are really bad and the next day will be problem
free.
Obviously, it is workload related. The definition from the exception job
is:

RID POOL FAILURE - STORAGE
NUMBER OF TIMES DB2 DETECTED NO STORAGE WAS AVAILABLE TO HOLD
A LIST OF RIDS DURING A GIVEN RID LIST PROCESS INVOLVING ONE
INDEX (SINGLE INDEX ACCESS WITH LIST PREFETCH) OR INVOLVING
MULTIPLE INDEXES (MULTIPLE INDEX ACCESS). (QXNSMIAP)

After researching these failures, it appears that our exception report is
really identifying the victims of the RID pool storage problem. Another
application job is apparently running and consuming a large portion of the
RID pool which causes these users to fail because of a lack of storage.
Our RID pool is currently set at 40MB. Yes, I can increase it and see if
the
problem goes away, but I'm looking for ways to identify the jobs that
consume the RID pool and get those jobs corrected. If those jobs seem to be
legitimate,,, then I will increase the RID pool.
Has anyone tackled this issue, or does anyone have input on how to tackle
this issue ??

Ricky Wafford
MBNA Technology Inc.
Manager - DB2 Technical Support
(469) 201-6271
Mailto:[login to unmask email]

---------------------------------------------------------------------------------

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

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

Max Scarpa

Re: RID Pool Failures - Finding the cause ??
(in response to Missy Case)
I had the same problem here, mainly with 3rd party application we cannot
modify.

We've MainView for DB2 here and every day I check its table to see if there
are problems. I had always RDS limit reached as reason for RID FAILURE
(number of RIDs > 4075 and > 25% table rows, in this case increasing RID
pool size doesn't help) and I check the time when the failure happened and
then I check in MainView table the plan/package with some values
(MULTINDEXNOM,MULTINDEXNOS) > 0 during these time frames. I think you can
do the same with other monitors. Or you can use IFCID 125 and a trivial
combination of DFSORT (or other sort product) and a REXX.

Many of the problems were solved with RUNSTATS and REBIND so the access
path was modified from MIA to 1 index access, other were solved using more
restrictive predicates, in few cases adding one index. Not excluding that
for your shop the size of RID pool coud be small as you've or more than 16M
RID entries (maybe) or > 50% of RID pool usage for 1 SQL, if the error is
STORAGE RID pool failure. But I'd like to know who's the offending SQL
statement(s) first.

HTH

Max Scarpa
Titanic's deck DB2 system programmer

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

Db2System Process

RID failures
(in response to Max Scarpa)
All:

I am trying to enable Performance Trace Class(30,8) IFCID(125) to collect
RID failures and generate report with SQLACTIVITY REPORT WORKLOAD(SCAN).

Is there any other better way to capture the RID failures and the threads
that are having them through DB2 PM?

TIA.

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

Martin Kenney

Re: RID failures
(in response to Db2System Process)
Do you have BMC's Activity Monotor?
If you do I have a job that extracts the previous days DB2 program
statistics and uses SAS to identify statements that may require tuning.
One of the things I list are any statements that have at least 1 RID
list failure.

I could send this to you if you want.

Marty Kenney
Sr. DB2 DBA
Railinc
(919) 651-5211


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of db2sysc
Sent: Tuesday, December 07, 2004 8:35 AM
To: [login to unmask email]
Subject: RID failures

All:

I am trying to enable Performance Trace Class(30,8) IFCID(125) to
collect
RID failures and generate report with SQLACTIVITY REPORT WORKLOAD(SCAN).

Is there any other better way to capture the RID failures and the
threads
that are having them through DB2 PM?

TIA.

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

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