When you create an index as clustered, db2 tries to maintain the rows in the table in the same order as the index as much as possible. But as the data grows and rows are displaced, clustering does not succed in maintaining 100% ordering with the table ...
Now, The degree of data clustering of the index, represented as a percentage between 0 to 100 is the cluster ratio ...
ie, if 10% of rows are not in clustered fashion, the clusterratio is 90% ...
Originally posted by eureka
I m interested to know what DB2 Cluster Ratio is ? Eagerly expecting reply regarding the same
Visit the new-look IDUG Website , register to gain access to the excellent content.
To add to what Sathyaram said, the cluster ratio changes to 100 (%) when you reorg the table. In order for the current cluster ratio to be updated in the catalog tables, use the runstats command.
If your cluster ratio is frequently lower than 90%, and you cannot do frequent reorgs to fix this (because of concurrency problems with your application), then increase the percent free of the table and the indexes. This will allow room (in-between reorgs) for DB2 to maintain the clustering sequence during inserts.
Clusterratio has also the very important role of influencing the optmizer when it comes to creating the access plan for an SQL statement.
As Sathyaram was saying, the higher the index's clusterratio, the more rows are arranged in the same physical sequence as the index. The optimizer will prefer less jumping around, therefore, will lean towards choosing an index whose clusterratio is high. In reverse, it means, the optmizer may reject an index the developer would want to use (for reasons of good key coverage for a query) if the clusterratio is too low, sometimes resulting in tablespace scans even if there are is a possible index.
One other thing. As Marcus mentioned, the clusterratio value in the index's catalog entry is set by RUNSTATS. However, this value is editable making certain indexes more or less attractive to the optmizer than they actually are, forcing it to choose different indexes than it normally would. This, of course, is not where one would start if applications are not performing well.