IBM Support

How to find record length in a table in DB2 LUW

Technical Blog Post


Abstract

How to find record length in a table in DB2 LUW

Body

One way to  find record  length in a specific  table in a specific schema  is  to run following query,

$ db2 "select tabname, sum(length) from syscat.columns where
tabschema='<schema>' and tabname='<table-name>' group by tabname"

 

Example  with a  DB2 LUW sample database table :

 

$ db2 describe table  db2inst1.org

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DEPTNUMB                        SYSIBM    SMALLINT                     2     0 No
DEPTNAME                        SYSIBM    VARCHAR                     14     0 Yes
MANAGER                         SYSIBM    SMALLINT                     2     0 Yes
DIVISION                        SYSIBM    VARCHAR                     10     0 Yes
LOCATION                        SYSIBM    VARCHAR                     13     0 Yes

  5 record(s) selected.

 

Total  record length in this table is 41

 

$ db2 "select tabname, sum(length) from syscat.columns where
> tabschema='DB2INST1' and tabname='ORG' group by tabname"

TABNAME                                                                                                                          2    
-------------------------------------------------------------------------------------------------------------------------------- -----------
ORG                                                                                                                                       41

  1 record(s) selected.

 

To find the record  length of all tables in a schema,

$ db2 "select tabname, sum(length) from syscat.columns where tabschema='<schema>' group by tabname"

 

To find the record length in all the tables,

$ db2 "select tabname, sum(length) from syscat.columns group by tabname"

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140730