Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Posts
    11

    Unanswered: Which indexes need to undergo reorg ?

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 Version and OS version are you using?

    Andy

  3. #3
    Join Date
    Jun 2006
    Posts
    471

    reorg

    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.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Oct 2006
    Posts
    11
    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.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Oct 2006
    Posts
    11
    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.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •