-------------------------------------------------------------------------------- 
-- 10.02.2009/GR: Berechnung der UNLOAD File Größe   
--                Record Length - 8 + Overhead für Date/Time/Timestamp 
--                Compression und VARCHAR's berücksichtigen (s. Avg Row Len) 
--                umgerechnet in Cyl (je ca. 840 (750) KB/Cyl) aufgerundet, 
--                Kann nur Näherungswert ergeben, weil VARCHAR-Anteil nicht exakt ermittelbar! 
--                Bei NOPAD Minimum-, bei PAD Maximum-Wert verwenden. 
-------------------------------------------------------------------------------- 
WITH EO AS (SELECT TBCREATOR,TBNAME, 
                   SUM( CASE COLTYPE 
                          WHEN 'DATE' THEN 6 
                          WHEN 'TIME' THEN 5 
                          WHEN 'TIMESTMP' THEN 16 
                          ELSE 0 
                        END ) AS EXTERNAL_OVERHEAD 
         FROM   SYSIBM.SYSCOLUMNS 
         WHERE  TBCREATOR = 'TEST' 
         AND    TBNAME LIKE 'T4711%' 
         GROUP  BY TBCREATOR,TBNAME ) , 
    PS AS (SELECT DBNAME,TSNAME, 
                  AVG(AVGROWLEN) AS AVG_ROWLEN, 
                  AVG(PAGESAVE) AS AVG_PAGESAVE, 
                  -- PAGESAVE=25 GLEICH 25% GESPART, D.H. AVG ROWLEN = 75% 
                  CAST (CASE AVG(PAGESAVE) 
                       WHEN 0 THEN AVG(AVGROWLEN) 
                       ELSE ROUND(AVG(AVGROWLEN) /(1-AVG(PAGESAVE)/100.00),0) 
                  END AS INTEGER) AS AVG_RECLEN_UNCOMPRESSED, 
                  COMPRESS 
           FROM   SYSIBM.SYSTABLEPART 
           WHERE  DBNAME LIKE 'DBS%' 
           GROUP  BY DBNAME,TSNAME,COMPRESS)   
SELECT TB.DBNAME,TB.TSNAME,TB.NAME,TB.CARDF, TB.RECLENGTH, 
       EO.EXTERNAL_OVERHEAD, 
       PS.AVG_ROWLEN,PS.AVG_PAGESAVE, PS.AVG_RECLEN_UNCOMPRESSED, PS.COMPRESS, 
       -- DIES IST DIE RECLEN + OVERHEAD IN CYLS, MEIST ALS OBERWERT ZU SEHEN 
       INTEGER(ROUND(MAX(1,((TB.RECLENGTH-8+EO.EXTERNAL_OVERHEAD)*TB.CARDF/1024)/750),0)) AS SYSREC_CYL_PAD, 
       -- DIES IST DURCHSCHNITT-RECLEN (Z.B. WG. VARCHAR) + OVERHEAD IN CYLS, MEIST ALS UNTERER WERT ZU SEHEN 
       INTEGER(ROUND(MAX(1,((PS.AVG_RECLEN_UNCOMPRESSED+EO.EXTERNAL_OVERHEAD)*TB.CARDF/1024)/750),0)) AS SYREC_CYL_NOPAD 
FROM   SYSIBM.SYSTABLES TB, 
       EO, 
       PS 
WHERE  TB.CREATOR = 'TEST' 
AND    TB.NAME    LIKE 'T4711%' 
AND    TB.TYPE    = 'T' 
AND    TB.CREATOR = EO.TBCREATOR 
AND    TB.NAME    = EO.TBNAME 
AND    TB.DBNAME  = PS.DBNAME 
AND    TB.TSNAME  = PS.TSNAME