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"
UID
ibm11140730