By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

storage manager tool in db2cc

P: n/a
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

Mar 5 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"dunleav1" <jm*@dunleavyenterprises.coma écrit dans le message de news:
11**********************@t69g2000cwt.googlegroups. com...
>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
Mar 6 '07 #2

P: n/a
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

Mar 6 '07 #3

P: n/a
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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"dunleav1" <jm*@dunleavyenterprises.coma écrit dans le message de news:
11**********************@30g2000cwc.googlegroups.c om...
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
Mar 7 '07 #4

P: n/a
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.
Mar 8 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.