Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    85

    Unanswered: REORGCHK Formula 2

    As documented on the Web:
    Formula F2:
    For regular tables:

    100*TSIZE / ((FPAGES-1) * (TABLEPAGESIZE-76)) > 68

    The table size in bytes (TSIZE) should be more than 68 percent of the total space allocated for the table. (There should be less than 32% free space.) The total space allocated for the table depends upon the page |size of the table space in which the table resides (minus an overhead of 76 bytes). Because the last page allocated is not usually filled, 1 is subtracted from FPAGES.


    How does one calculate TSIZE? There's no column in SYSCAT.TABLES for TSIZE. Is it somehow the CARD value times an average row size?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    See if this explains

    http://www.quest-pipelines.com/pipel...3.htm#november

    Cheers
    sathyaram

  3. #3
    Join Date
    Sep 2003
    Posts
    85
    Sathyaram,
    Thanks for the URL. Unfortunately, based on certain column(s) value(s), I got:
    SQL0802N Arithmetic overflow or other arithmetic exception occurred.
    SQLSTATE=22003

    So, the view must take into account some large values and probably CAST with larger datatype.

    By the way:
    What's the default for REORGCHK? UPDATE or CURRENT STATISTICS?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    UPDATE STATISTICS ...

    A common mistake when casting is doing BIGINT(VAL1*VAL2) ...
    The correct usage is BIGINT(VAL1)*BIGINT(val2)

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2003
    Posts
    85
    Thanks.

    Here's an updated view definition with the additional guidance:

    --If you create the following view, then the data provided by REORGCHK is available for SQL queries. This view returns the same Table Statistics as doing a REORGCHK.

    DROP VIEW DXTDB.REORGCHK
    ;
    CREATE VIEW DXTDB.REORGCHK AS (
    SELECT CHAR(T. TABSCHEMA,12) AS TABSCHEMA
    , CHAR(T.TABNAME,40) AS TABNAME
    , T.CARD
    , T.NPAGES
    , T.FPAGES
    , I.NLEAF
    , CAST(T.CARD * (C.AVGCOLLEN + 10) AS BIGINT) AS TSIZE
    , CASE WHEN T.CARD = 0 THEN NULL
    ELSE CAST(100*T.OVERFLOW/T.CARD AS DEC(13,2)) END AS F1
    , CASE WHEN BIGINT(T.FPAGES-1) * BIGINT(S.PAGESIZE-76) = 0 THEN NULL
    ELSE CAST(100*BIGINT(T.CARD) * (C.AVGCOLLEN + 10) / (BIGINT(T.FPAGES-1) * BIGINT(S.PAGESIZE-76)) AS DEC(17,2)) END AS F2
    , CASE WHEN T.FPAGES = 0 THEN NULL
    ELSE CAST(100*T.NPAGES/T.FPAGES AS DEC(17,2)) END AS F3
    , CASE WHEN T.CARD =0 THEN '-'
    ELSE CASE WHEN CAST(100*T.OVERFLOW/T.CARD AS DEC(13,2)) >= 5 THEN '*' ELSE '-' END END
    ||
    CASE WHEN BIGINT(T.FPAGES-1) * BIGINT(S.PAGESIZE-76) = 0 THEN '-'
    ELSE CASE WHEN CAST(100*BIGINT(T.CARD) * (C.AVGCOLLEN + 10) / (BIGINT(T.FPAGES-1) * BIGINT(S.PAGESIZE-76)) AS DEC(17,2)) <= 70
    THEN '*' ELSE '-' END END
    ||
    CASE WHEN T.CARD =0 THEN '-'
    ELSE CASE WHEN CAST(100*T.NPAGES/T.FPAGES AS DEC(17,2)) <= 80 THEN '*' ELSE '-' END END
    AS REORG
    FROM SYSCAT.TABLES T
    , SYSCAT.TABLESPACES S
    ,( SELECT TABSCHEMA, TABNAME,
    SUM(
    CASE WHEN LOGGED = '' THEN AVGCOLLEN
    WHEN LENGTH <= 1024 THEN 72
    WHEN length <= 8192 THEN 96
    WHEN length <= 65536 THEN 120
    WHEN length <= 524000 THEN 144
    WHEN length <= 4190000 THEN 168
    WHEN length <= 134000000 THEN 200
    WHEN length <= 536000000 THEN 224
    WHEN length <= 1070000000 THEN 256
    WHEN length <= 1470000000 THEN 280
    WHEN length <= 2147483647 THEN 316
    END
    + CASE WHEN LOGGED <> '' AND NULLS = 'Y' THEN 1 ELSE 0 END
    ) AS AVGCOLLEN
    FROM SYSCAT.COLUMNS
    GROUP BY TABSCHEMA, TABNAME
    ) AS C
    ,( SELECT TABSCHEMA, TABNAME, SUM(NLEAF) AS NLEAF, SUM(NLEVELS) AS NLEVELS
    FROM SYSCAT.INDEXES
    GROUP BY TABSCHEMA, TABNAME
    ) AS I
    WHERE
    T.TBSPACEID = S.TBSPACEID
    AND T.TABSCHEMA = C.TABSCHEMA
    AND T.TABNAME = C.TABNAME
    AND T.TABSCHEMA = I.TABSCHEMA
    AND T.TABNAME = I.TABNAME
    AND T.TYPE IN ( 'T','S')
    AND T.TABSCHEMA NOT IN ('SYSIBM','SYSCAT','SYSSTAT')
    )
    ;


    As a side note that this view doesn't produce the index info from a REORGCHK and it also assumes that one has run RUNSTATS and was going to issue a REORGCHK CURRENT STATISTICS ...

    HTH,
    Ruby

Posting Permissions

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