Hi, I've run online reorgs on all indexes for a table and afterwards (reorgchk) i can't see any improvements whatsoever...in fact reorgchk-output looks exactly the same as before.. any ideas?

My commands was:
db2 reorg indexes all for table TABNAME allow write access
db2 runstats on table TABNAME and detailed indexes all

reorgchk shows no problem for F1,F2,F3 (no table reorg needed)
but shows clusteratio(F4): 37,3,3,73,46,3,84 thus 7 of 8 indexes marked for reorg

I've not looked into the actual layout of the indexes but could it be that these indexes are poorly created and not really useful?
Or must i perhaps reorg the table using the most important index and then reorg all other indexes?

Or could it be a flaw in the online index functionality so that we really should run offline reorg instead?

The problem is we haven't got the diskspace for the offline reorg thus failing and leaving the indexes corrupt.

Any help or tips is much appreciated

Our environment:
DB2 V9.1 FP4a
Windows2003 SP2

//Leo

leolej, Only one index on a table can be Clustered at 100 percent. All other indexes will not reach that number (except in very unusual circumstances). So having 7 of 8 indexes with less than 100 percent in normal.

By the way, having an Index in 100 percent Clustered order means that the Data rows in the Table are in the same order as the Index Key(s). If your Table is not Reorged and you just Reorg the Clustering Index, you won't get to 100 percent clustered. You would have to Reorg the Table data.

PS I am assuming you are running Runstats before the ReorgChk.

It is possible to have two indexes with clusterratio = 100%, but it would be coincidental.

For example, consider a table with a PK that is generated always (in ascending sequence). There is an index on the PK.

If there is also another column with TIMESTAMP data type in the table that is always populated with current_timestamp, and this column is indexed, then it is possible that this index would also be 100% cluster ratio after a reorg if the table was reorged on either on the PK or the TIMESTAMP column.

Hi, yes i've run runstats before reorgchk, but it seems none of the 8 indexes are reorganized in any way. I'd expect at least one of them to have a clusterratio higher than 84% and the other ones to have better than 3%. According to reorgchk, the table doesn't need reorg but i could perhaps try reorg on the table first and then reorg the indexes.

Looks like we're going to get more diskspace in the next maintenance window, but still i think this online reorg is weird...

Another question is why i can't see the progress of the index reorg in any way... I've tried db2pd,list utilities, sysibmadm-view (snaptab_reorg) and get snapshot but none of them shows any information about the progress of the index reorg. Seems a bit bugged if it only works for table reorg.

Only thing I could see is in event viewer (start and stop time), but that is probably just the same info as db2diag.log... any ideas?

leolej, it may be a little counter-intuitive but to affect the Index cluster ratio, you need to reorg the Table. This is because the cluster ration is based on how closely the Table rows match the Index key(s) order. Just reorging the Index will have no effect on the cluster ratios.

I am going from memory, but I believe another thread mention that only reorg Table information is displayed and not Index Reorgs. I am not sure why.

Ok, i thought i could just reorg the index in according to the table but alright
i'll try reorging the table first then even though reorgchk hasnt marked it for reorg and the reorg the index. Hopefully i'll get a little more clusterratio than 3% which two of the indexes now have :-)
Thanks for your answers

Does anyone have an aspirin?

leolej, All you can do is get your Clustering index as close to 100 percent as you can by Reorging the table. After that, Cluster ratio is meaningless for the other indexes. If they happen to get closer to 100 percent than 0, that is great (and a coincident of the column values being indexed). But with 7 non-clustered indexes, you will most almost assuredly have at least one (and most likely 2 or 3) that has a very low cluster ratio. It is just the nature of the data that in can only be in order by one set of key value(s) (other than a few special situations). I wouldn't spend any time trying to force these indexes to a higher cluster ratio.

Ok, i see what you meen with cluster ratio. It makes sense that only one index at a time could match the table data.
Maybe i should look for some other ways of improving the performance of this database than just looking at clusterratio of the indexes.

I just saw the ddl with the table and index layout. This table I spoke of earlier hasnt got a primary key contraint, instead 7 regular indexes and one unique index. The index with the highest clusterratio now is the 8th one (84%), but it seems that index doesnt have the bookid indexed which 4 of the other indexes have (they have really low clusteratio). I guess i'll have to dig into how they are using the database to know how and where to really tune it.
It's just we have some old reorg maintenance routines that have always been run in a schedule and now we haven't been able to run it for 6 month and some users are complaining about the application sometimes being slow with bad responsetimes.