[DB2] How to determine rowsize in table

Solution1: using syscat.tables

using syscat.tables field AVGROWSIZE

SELECT TABNAME, AVGROWSIZE  FROM syscat.tables
WHERE TABNAME LIKE '%INV%'

Solution2: using CTE

using CTE or you can create stored procedure

with compr as (
  select 
    TABSCHEMA
    , TABNAME
    , CASE WHEN compression in ('B', 'V') THEN 2 ELSE 0 END as compression_mod
    FROM SYSCAT.TABLES
),
col as (
  SELECT TABSCHEMA
         , TABNAME
         , COLNAME
     ,COALESCE(D.SOURCENAME, C.TYPENAME) AS TYPENAME 
         ,COALESCE(D.LENGTH, C.LENGTH) AS LENGTH
         ,C.SCALE, C.NULLS, C.INLINE_LENGTH, D.METATYPE
         ,D.INLINE_LENGTH AS STRUCT_INLINE_LENGTH
     , CASE WHEN C.inline_length <> 0 
                    THEN C.inline_length
                WHEN metatype = 'R' THEN D.inline_length 
                WHEN COALESCE(D.SOURCENAME, C.TYPENAME) IN ('CLOB', 'BLOB', 'DBCLOB') 
                    THEN CASE WHEN COALESCE(D.LENGTH, C.LENGTH) <=       1024 THEN 68
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <=       8192 THEN 92
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <=      65536 THEN 116                                       
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <=     524000 THEN 140
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <=    4190000 THEN 164
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <=  134000000 THEN 196
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <=  536000000 THEN 220
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <= 1070000000 THEN 252
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <= 1470000000 THEN 276
                              WHEN COALESCE(D.LENGTH, C.LENGTH) <= 2147483647 THEN 312
                         ELSE -2 END
                WHEN COALESCE(D.SOURCENAME, C.TYPENAME) IN ('LONG VARCHAR', 'LONG VARGRAPHIC')
                           THEN 20
                WHEN COALESCE(D.SOURCENAME, C.TYPENAME) = 'XML' THEN 80
           ELSE 0 END as LOBLENGTH
    FROM SYSCAT.COLUMNS AS C
         LEFT OUTER JOIN SYSCAT.DATATYPES AS D
              ON D.typeschema = C.typeschema 
              AND D.typename = C.typename
              AND D.typemodulename IS NULL
              AND C.typeschema <> 'SYSIBM  '
),
tot as (select  
    col.TABSCHEMA as tabschema
    , col.TABNAME as tabname
        ,SUM(CASE TYPENAME 
            WHEN 'SMALLINT'        THEN length + compression_mod
            WHEN 'INTEGER'         THEN length + compression_mod
            WHEN 'BIGINT'          THEN length + compression_mod
            WHEN 'REAL'            THEN length + compression_mod
            WHEN 'DOUBLE'          THEN length + compression_mod
            WHEN 'DECFLOAT'        THEN length + compression_mod
            WHEN 'DECIMAL'         THEN TRUNC(length / 2) + 1 + compression_mod
            WHEN 'CHARACTER'       THEN length + compression_mod
            WHEN 'VARCHAR'         THEN length + 4 - compression_mod
            WHEN 'GRAPHIC'         THEN length * 2 + compression_mod
            WHEN 'VARGRAPHIC'      THEN length * 2 + 4 - compression_mod
            WHEN 'LONG VARCHAR'    THEN 24 - compression_mod
            WHEN 'LONG VARGRAPHIC' THEN 24 - compression_mod
            WHEN 'CLOB'            THEN loblength + 4 - compression_mod
            WHEN 'BLOB'            THEN loblength + 4 - compression_mod
            WHEN 'DBCLOB'          THEN loblength + 4 - compression_mod
            WHEN 'XML'             THEN loblength + 3 - compression_mod
            WHEN 'DATE'            THEN length + compression_mod
            WHEN 'TIME'            THEN length + compression_mod
            WHEN 'TIMESTAMP'       THEN length + compression_mod
            ELSE CASE WHEN metatype = 'R' THEN loblength + 4 - compression_mod
                     ELSE -3 END
            END 
          + CASE WHEN compression_mod = 0 AND NULLS = 'Y' THEN 1 ELSE 0 END) as row_size

  FROM compr join col on compr.tabname=col.tabname
        and compr.tabschema=col.tabschema
  GROUP BY col.tabschema, col.tabname)
select 
       substr(tot.tabschema,1,24) as tabschema
       , substr(tot.tabname,1,40) as tabname
-- comment the above two rows and uncomment the below two rows for full, non-truncated table names
--     tot.tabschema
--     , tot.tabname
       , row_size + CASE WHEN compr.compression_mod <> 0 THEN 2 ELSE 0 END AS SIZE_IN_BYTE
-- uncomment the following two rows if you have created the stored procedure and want to compare results
--       , GetRowSize(tot.tabschema, tot.tabname) as sp_row_size
--       , GetRowSize(tot.tabschema, tot.tabname) - row_size + CASE WHEN compr.compression_mod <> 0 THEN 2 ELSE 0 END as diff
  FROM tot join compr on tot.tabschema=compr.tabschema and tot.tabname=compr.tabname
  --WHERE tot.tabname LIKE '%INV%'
;

Reference

• How to calculate Row size of table in db2 - Stack Overflow
• DB2 Table Row (Tuple) Size (virtual-dba.com)
• Legacy Communities - IBM TechXchange Community by Serge Rileau. (Very Old doc for CTE and Store Procedure)