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?

See if this explains

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

Cheers
sathyaram

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?

UPDATE STATISTICS ...

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

HTH

Sathyaram

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****EAF
, 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