NPI Cluster Ratio of 0% on Table Based Partitioned Tables in DB2 9

Lori Ann Galluzzo

NPI Cluster Ratio of 0% on Table Based Partitioned Tables in DB2 9
Does anyone know of an issue with RUNSTATS or with Table Based partitioning where an NPI goes to a 0% clustered ratio even though the other statistics look good (first key & full key cardinality). Is there a PTF out on it? Even after reorg, it still is 0%. The same index in another region is at 50% but that table isn't table based partitioned (uses a different index for its partitioning). We are on DB2 9 in the region with the issue, the 50% ratio is a DB2 region in DB2 V8.1.

Any ideas? Let me know.
Thanks!

Lori Ann Galluzzo
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 for z/OS

HP Enterprise Services

US Healthcare/ASFOA-US
MS:B-2
225 Grandview Ave.
Camp Hill, PA 17011, USA

Tel: 717-975-5482
Fax: 717-975-4219
Mobile: 717-215-0587
E-mail: [login to unmask email]<[login to unmask email]%20>


We deliver on our commitments
so you can deliver on yours.



Lori Ann Galluzzo

FW: NPI Cluster Ratio of 0% on Table Based Partitioned Tables in DB2 9
(in response to Lori Ann Galluzzo)
Does anyone know of an issue with RUNSTATS or with Table Based partitioning where an NPI goes to a 0% clustered ratio even though the other statistics look good (first key & full key cardinality). Is there a PTF out on it? Even after reorg, it still is 0%. The same index in another region is at 50% but that table isn't table based partitioned (uses a different index for its partitioning). We are on DB2 9 in the region with the issue, the 50% ratio is a DB2 region in DB2 V8.1.

Any ideas? Let me know.
Thanks!

Lori Ann Galluzzo
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 for z/OS

HP Enterprise Services

US Healthcare/ASFOA-US
MS:B-2
225 Grandview Ave.
Camp Hill, PA 17011, USA

Tel: 717-975-5482
Fax: 717-975-4219
Mobile: 717-215-0587
E-mail: [login to unmask email]<[login to unmask email]%20>


We deliver on our commitments
so you can deliver on yours.



Stan Goodwin

Re: FW: NPI Cluster Ratio of 0% on Table Based Partitioned Tables in DB2 9
(in response to Lori Ann Galluzzo)
Lori, I have heard this before. My suggestion is Open a PMR with IBM...

Stan Goodwin, DB2 Advisor
IBM US East Extended Team
System z SW Technical Professional
Phone: 720-396-2100
Email: [login to unmask email]




From: "Galluzzo, Lori (HC Product Support)" <[login to unmask email]>
To: [login to unmask email]
Date: 02/03/2011 02:53 PM
Subject: FW: NPI Cluster Ratio of 0% on Table Based Partitioned
Tables in DB2 9
Sent by: IDUG Regional Group - Central PA
<[login to unmask email]>



Does anyone know of an issue with RUNSTATS or with Table Based
partitioning where an NPI goes to a 0% clustered ratio even though the
other statistics look good (first key & full key cardinality). Is there a
PTF out on it? Even after reorg, it still is 0%. The same index in
another region is at 50% but that table isn?t table based partitioned
(uses a different index for its partitioning). We are on DB2 9 in the
region with the issue, the 50% ratio is a DB2 region in DB2 V8.1.

Any ideas? Let me know.
Thanks!

Lori Ann Galluzzo
IBM Certified Database Associate ? DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 for z/OS

HP Enterprise Services

US Healthcare/ASFOA-US
MS:B-2
225 Grandview Ave.
Camp Hill, PA 17011, USA

Tel: 717-975-5482
Fax: 717-975-4219
Mobile: 717-215-0587
E-mail: [login to unmask email]

We deliver on our commitments
so you can deliver on yours.






The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

Roy Mathur

RE: NPI Cluster Ratio of 0% on Table Based Partitioned Tables in DB2 9
(in response to Lori Ann Galluzzo)

We have noticed a similar issue.  We have a number of large tables that were recently RUNSTAT'd in which the Indexes suddenly have a ClusterRatioF < 2.  I know that at least one of them was 50% clustered a month ago (I haven't checked the others yet).  The data is not volatile and the ClusterRatioF should still be around 50%.  The tables are index based partitioned.

We recently applied DB2 Maintenance and at the same time changed the ZPARM for STATCLUS to ENHANCED.  I do not yet know whether they are related to the issue or not.  We are currently on DB2 v9 NFM for z/OS. 

If possible, please let me know what causes this. 

Thanks, -Roy