Record length of DB2 Table

How to Find the Record length of DB2 Table

Recently I was working on an assignment which required me to prepare a list of all the DB2 Tables and their corresponding lengths.

The list of all the DB2 tables can easily be found from the SYSIBM.SYSTABLES, by extracting the list of “NAMES”  from this table by running a query which would filter the data based on the “CREATOR”.

SELECT NAME, RECLENGTH FROM SYSIBM.SYSTABLES WHERE CREATOR LIKE ‘%creator%’

; ; ; ;

Here ‘creator’ is specific to my database settings and signifies the owner which created the table.

For length I could use the “RECLENGTH” column of this table or I could check the length of the table by browsing the Table details from DB2 File manager.

I assumed that this length would match with the length of the COBOL declaration of the Table, But I learnt that the length of the COBOL declaration differed in most of the cases. 

Upon getting in touch with my DBA, I got to know that the length of the DATE and TIMESTAMP variables is different in COBOL declaration and that in the TABLE.

The other interesting fact that I came across in this excercise is that the SYSIBM.SYSTABLES is an LPAR dependent table i.e. each LPAR or TSO  region has its own SYSIBM.SYSTABLES.