Results 1 to 8 of 8

Thread: reorg issue

  1. #1
    Join Date
    Dec 2010
    Posts
    123

    Unanswered: reorg issue

    Dear all,

    i am presently workin on db2 9.1. on AIX, i am trying to reorg one table, reorg command is succefully exicuted ,but reorgchk command again showing *(need for reorg), how can i solve this problem.
    The commands used by me are,


    db2 "RUNSTATS ON TABLE DB2INST1.ADMINISTRATION WITH DISTRIBUTION AND INDEXES ALL"

    DB2 "REORG TABLE DB2INST1.ADMINISTRATION"

    after this two commands exicuton, Reorgchk output is

    $ db2 "reorgchk update statistics on table DB2INST1.ADMINISTRATION"

    Doing RUNSTATS ....


    Table statistics:

    F1: 100 * OVERFLOW / CARD < 5
    F2: 100 * (Effective Space Utilization of Data Pages) > 70
    F3: 100 * (Required Pages / Total Pages) > 80

    SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
    ----------------------------------------------------------------------------------------
    Table: DB2INST1.ADMINISTRATION
    608194 0 27879 27879 - 8.98e+08 0 98 100 ---
    ----------------------------------------------------------------------------------------

    Index statistics:

    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
    F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
    F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
    F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

    SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD F4 F5 F6 F7 F8 REORG
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    Table: DB2INST1.ADMINISTRATION
    Index: DB2INST1.ADMINISTRATION1
    608194 801 0 3 0 608194 26 26 3534 3534 100 91 85 0 0 -----
    Index: DB2INST1.ADMINISTRATION2
    608194 170 0 2 0 932 15 15 6036 6036 38 94 - 0 0 *----
    Index: DB2INST1.I_OBJSEARCHCURRENT
    608194 782 0 3 0 608194 25 25 3938 3938 0 91 87 0 0 *----
    Index: DB2INST1.I_SECURITY_POLICY
    608194 169 0 2 0 2 3 3 6036 6036 100 94 - 0 0 -----
    Index: DB2INST1.UI_U40_PUBLICATION
    608194 169 0 2 0 1 3 3 6036 6036 100 94 - 0 0 -----
    Index: DB2INST1.UI_U41_OWNERDOCUME
    608194 169 0 2 0 1 3 3 6036 6036 100 94 - 0 0 -----
    Index: SYSIBM.SQL090826165345690
    608194 661 0 2 0 608194 18 18 6036 6036 0 93 - 0 0 *----
    --------------------------------------------------------------------------------------------------------------------------------------------------------------

    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
    for indexes that are not in the same sequence as the base table. When multiple
    indexes are defined on a table, one or more indexes may be flagged as needing
    REORG. Specify the most important index for REORG sequencing.

    Tables defined using the ORGANIZE BY clause and the corresponding dimension
    indexes have a '*' suffix to their names. The cardinality of a dimension index
    is equal to the Active blocks statistic of the table.

    pls anybody can advice me, wats the mistake i am coming across,
    Thnaks in advance.
    Last edited by ravichandrapratap; 01-04-11 at 05:36.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The "*" indicate that those indexes are not clustering indexes for the table. Since the rows in a table can be ordered only by 1 criteria and not by 2 different ones, you will always have a "*" showing on those indexes by which the rows are not ordered. The only exception is if there is a functional dependency between the clustering index and the other indexes.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2010
    Posts
    123
    thnx, for ur reply stolze, pls can u give me clear explanation, is my reorg is successful???,

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You didn't get an error message or warning, so your reorg was successful.

    As for a more detailed explanation, I suggest you familiarize yourself with clustering and sorting of rows. It is simply that if the rows are physically sorted according to one criteria (index), it is not possible to sort them - at the same time - to a different criteria (index). You have to choose one sort order and accept that you cannot satisfy all others.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2010
    Posts
    123
    thanku so much stolze for ur reply, i wil work out on clustering and sorting .............

  6. #6
    Join Date
    Dec 2010
    Posts
    123
    can anybody tell wat are the best syntexes for runsats and reorg..........
    Last edited by ravichandrapratap; 01-05-11 at 06:29.

  7. #7
    Join Date
    Oct 2007
    Posts
    246
    offline reorg and
    reorg table tabname
    for runstats as marcus informed
    db2 "runstats on table db2inst1.books with distribution on key columns and detailed indexes all"
    regds
    Paul

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The question is which statistics you really need. Collecting everything that is possible (all columns, all indexes, with distribution, etc.) is good but it also takes time to collect the stats. If you don't have that time, you can't use all those options. So the answer is as usual: it depends.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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