Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Reorgchk cannot clear * on F3

    DB2 8.2 on AIX for SAP

    Hey there,

    I'm trying to reorg the tables and indexes as best they can be and there are about a dozen tables with a stubborn * that I can't seem to shift.

    For example:

    Code:
    SCHEMA    NAME                  CARD    OV    NP    FP ACTBLK    TSIZE  F1  F2  F3 REORG
    ----------------------------------------------------------------------------------------
    SAPR3     TBLX1                72925     0   288   288      -  3208700   0  68 100 -*-
    SAPR3     TBLX2                 1809     0     8     8      -    61506   0  53 100 -*-
    I've tried an inplace reorg, an offline reorg, a reorg with the primary index, and an index reorg.

    So 2 questions:
    1) Is there somewhere that I can get a list of the meanings of F1-8 beyond the scant Command ref doco (some are more obvious than others) and recommendations on how to deal with each

    2) Does anyone have any suggestions for why I can't clear it on F3 and perhaps other things I can try?

    Cheers

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    To answer you questions:

    1) I have not found anything else but what is in the the "Command Reference" for REORGCHK. It usually takes me a few readings of them to get what they are. This is my interpretation of what the are:

    Table info:
    F1: Percentage of rows that were updated and took up more space then before the updates. This should be less than 5%.
    F2: Percentage of total space for the table that is not being used. This is a calculated value based on the average column length. It should be greater than 68%.
    F3: Percentage of pages that are empty. This should be greater than 80%.

    Index Info:
    F4: Cluster ratio of index (how in sequence it is). Should be greater than 80%.
    F5: Percent of non empty space for index. Should be greater than 50%.
    F6: Checks if there are too many levels in the index tree.
    F7:Percentage of deleted RIDs. Should be less than 20%.
    F8: Percentage of empty leaf pages. Should be less than 20%.

    2) You are reading the recommendations wrong. The -*- is showing that F2 is recommending a REORG, not F3. The first position is F1, second is F2, and third is F3. (For indexes the first is F4, second is F5, third F6, fourth F7, and fifth is F8). There are some conditions that will not go away after a reorg. If you have a lot of wasted space on each data page then F2 will may not go away (as is the case you are seeing). What is the pagesize of the tables in question? What is the table DDL for those tables also?

    Andy

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    1)....
    Cheers, that's a handy reference to have about!

    2) You are reading the recommendations wrong. The -*- is showing that F2 is recommending a REORG, not F3. The first position is F1, second is F2, and third is F3. (For indexes the first is F4, second is F5, third F6, fourth F7, and fifth is F8). There are some conditions that will not go away after a reorg. If you have a lot of wasted space on each data page then F2 will may not go away (as is the case you are seeing). What is the pagesize of the tables in question? What is the table DDL for those tables also?

    Andy
    Oh dear, I was a bit braindead when typing that...the F3 was a repeated typo. I was thinking of the right column, just the wrong name.
    I've looked into it a bit further and the tables in question are in 16k tablespaces.... I'm not sure why they are 16k but I'm guessing because many are v. large tables (20-40GB) and possibly there was concern they may hit the upper limit on 4k tablespaces.

    Again thanks for the info

Posting Permissions

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