There are several scenarios with DB2 where we need to know what the “worst-case” row length is. That is, if all varying fields are completely full and any LOBs are maximally inlined, how much space could a single row actually take up? SYSCAT.TABLES gives us the average row size, which is useful for some use cases, but not all of them.

## Using a Stored Procedure

My top resouce for this is Serge Rileau’s Blog article with a great stored procedure definition. This stored procedure works great. The problem comes when I’m working in a client’s environment where they don’t want me to create any objects of any kind, much less stored procedures. To deal with this frequent consultant’s conundrum, I’ve had a goal for a while to work this into an SQL I can run without creating any objects. I sat down to do it several months ago, but didn’t have a solid couple of hours to dedicate to it, so came away feeling frustraed. However, I recently had time to sit down and work it out, and it wasn’t as complicated as I thought it would be.

## My SQL to Calculate Row Sizes

My goal is to get a query that will give me the table name and row size for every table in the database. Using a couple of common table expressions, I’ve developed the following SQL. It is very heavily based on Serge Rileau’s stored procedure, and I’ve successfully compared results on a couple of systems:

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 -- 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 ;

Is this long and complicated? Yes, it is. The quick cheat I’ve run into others using is simply summing up length in syscat.columns for a table. That works as an approximation in some situations, but blows up when you hit XML, LOBS, and some other issues. I just can’t rely on a cheat like that on an ongoing basis.

The above is also less efficient if you were to run it for a single table than the stored procedure. Since my goal is a list of tables with their row sizes, this is not an issue for me. I would work more on optimization if I were running it for only a single table at a time and optimization mattered. Optimization for this will also matter more for environments with an excessively large number of tables, such as some SAP or PeopleSoft databases.

I’m also not sure how to work with this in an extended row size environment – I avoid extended row size for its performance implications.