Can anyone provide me with a query which can give me the names of those indexes (from the catalog tables/views) that needs a reorg.

What DB2 Version and OS version are you using?

Andy

db2 version and os have nothing to see with this problem. it is identical on all platforms. if udb is involved have a reorgchk done and see the output
first part is for table reasons and second part of output is because of index reason. the reorg needed is indicated with * in condition f4......
the formula can be found in the online info center or in the books.

Guy,
Actually the DB2 version does matter. This person stated that they wanted to use a query to get this info. REORGCHK is a command and not a query. There is a stored procedure available at V 8.2 that can return a result set that supplies the information.

Andy

First of all thank you for the reply.

I think I am close to what Andy is trying to say. Its UDB and version 8.2. Can you provide me with more information about the stored procedure you are referring to.

Just one question for Guy. I know a bit about REORGCHK. Can you please let me know if f4 refers to a need for index reorg. I mean are you asking me query the catalog tables to find where the clusterratio or clusterfactor > 80 for an index and try to do a reorg for that index. Please confirm. In that case what about the formulas for f5, f6, f7 and f8. Will they not need to be considered for index reorg.

The procedure can be found at:

http://publib.boulder.ibm.com/infoce...n/r0011871.htm

There is also one for tables.

The formulae F4 through F8 (as also F1 to F3 for table reorg) indicate a possibility that a reorg in necessary. I have seen these marked, performed a reorg and they still stay marked.

Andy

Hi Andy, Thanks for the link. I will check the procedure and will try to use the same.

Regarding the REORGCHK issue where the tables/indexes stay marked even after performing a REORG, did you try a RUNSTATS after the REORG. I guess the formula uses catalog tables and they need to be updated. You can try REORGCHK with the "update statistics" option.

Yes, RUNSTATS was run after the REORG. It is almost always an index that stays marked as needing reorg. And alot of those are F4 which will happen when there are more than one index and the clustering is on a different one.

Andy