storage manager tool in db2cc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dunleav1 - 05 Mar 2007 20:56 GMT

I ran the storage manager against a test database I have. One index
comes up with a red box next to it's name.
The cluster ratio shows as 1.
If I collect detailed statistics against the index, the red box
doesn't show up in the storage manager tool and the cluster ratio
shows as a -1.

Can someone explain what the storage manager thinks is wrong?
Thanks,
JD

Reply to this Message

Pierre Saint-Jacques - 05 Mar 2007 23:54 GMT

I guess maybe, perhaps you've encountered a "hitch" ?
If, after running detailed index stats, you fet cluster ratio to -1, that's
normal. Detailed stats. will change clusterratio to -1 and cluster factor
to a value between 0 and 1. (Please do not ask why cluster ratio is % and
cluster factor is between 0 and 1).

Maybe the Red Box does not show after detailed stats. because the cluster
factor is higher than the % value you specified for clusterratio in the
storage manager. This may be part of the "undocumented, unannounced,
unsupported" features of DB2.
Regards, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>I ran the storage manager against a test database I have. One index
> comes up with a red box next to it's name.
[quoted text clipped - 6 lines]
> Thanks,
> JD

Reply to this Message

dunleav1 - 06 Mar 2007 15:23 GMT

With normal stats I get a cluster ration of 4 and cluster factor of
-1.
With detailed stats I get a cluster ratio of -1 and cluster factor of .
223.

The warning level I set within storage manager for cluster ratio is
10% and alram level is 10%.

I think this is another bug I've found with one of the DB2 gui tools.
I'll just ignore the warning.
Thanks,
JD

Reply to this Message

Pierre Saint-Jacques - 07 Mar 2007 01:22 GMT

Getting a -1 value for one metric when you get an actual value for the other
is normal behavior. Setting one metric to a value causes the other to
be -1.

As to your actual values, I'm puzzled. Your warning and alarm settings seem
to be very low. Ithought the one wants to know when cluster ratio is =< 80
(percent) so 10% would mean you really don't care. I may be wrong as to how
to specify for storage manager but I know that it's the value 80 that the
optimizer will start to question whether to use the index or not.
With normal stats, a value of 4 would mean that your index clusters to 4% of
the rowid sequence which basically means the clustering is useless. But atr
4%, you woulf get your warnings and alrms because they're set to 10%.
For detailed stats., the value means 0.223 as the cluster factor is between
0 and 1. I'm puzzled, 4% normal and 22.3% (0,223) detailed. You're right
you may have found a bug !!!!
Regards, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> With normal stats I get a cluster ration of 4 and cluster factor of
> -1.
[quoted text clipped - 8 lines]
> Thanks,
> JD

Reply to this Message

dunleav1 - 08 Mar 2007 19:54 GMT

I am using the default thresholds for Storage Management.
I am seeing this same issue on another database V9.1 SP2 - just for
another object.

With normal stats the cluster ratio is 1 (first example) or 3 (this
example). The Cluster ratio warning threshold is 20 and the alarm
threshold is 10.
So these values are failing below the alarm threshold, the monitor is
setting an alarm, so the monitor is working correctly.

Basically I have a table (with high # of rows and columns) with six
indexes on it. One index is showing up with a low cluster ratio (1 or
3). The other indexes on the table have cluster ratios at 100. I ran
a reorg on the table and the indexexes.

My guess is that the index is not used by the optimizer because of the
structure of the index and the data distribution within it.

I'm going to retract my statement that it's a bug. I'll have to
investigate it out closer.

Reply to this Message