## Background

Have you ever seen:

"SQL0670N The row length of the table exceeded a limit of "<length>" bytes. (Table space "<tablespace-name>".)"

"SQL0670N The row length of the table exceeded a limit of "<length>" bytes. (Table space "<tablespace-name>".)"

When creating or altering a table DB2 imposes limits on the total worst case size of a row.

This size depends on the page size of the tablespace in which the table resides:

This size depends on the page size of the tablespace in which the table resides:

- 4005 bytes in a table space with a 4K page size
- 8101 bytes in a table space with an 8K page size
- 16293 bytes in a table space with an 16K page size
- 32677 bytes in a table space with an 32K page size

But when you create a table how do you know how big its signature will be?

How much space do you have left in the page to add additional column without having to migrate to a bigger pagesize?

How much space do you have left in the page to add additional column without having to migrate to a bigger pagesize?

How many rows can fit at least into the page?

To answer these questions there is a substantial table in the CREATE TABLE documentation of the Information center.

So it's either "Good luck!" to you in wading through it with a notepad, sharp pencil, and an eraser at hand.

Or you can just use the handy routine I have scribbled up below.

So it's either "Good luck!" to you in wading through it with a notepad, sharp pencil, and an eraser at hand.

Or you can just use the handy routine I have scribbled up below.

## Algorithm

In the end the algorithm isn't all that complex:

A

- For most data types such as numbers, strings and datetime the SYSCAT.COLUMNS.LENGTH column actually contains the physical length in bytes within the row.
- DECIMAL is the off man out because here the LENGTH column contains the PRECISION.

Since the data type is implemented using BCD formatting that works out to a byte for every two digits.

We need to round up for odd number of digits of course and we need to also store a sign.

In the end that leaves us with (precision / 2) + 2 bytes. - Long data types which are stored in LOB space use LOB descriptors.

So their actual length is irrelevant for the row size.

Their footprint within the row is stored in the INLINE_LENGTH for base tables.

For non views however the value needs to be derived from the LENGTH. - Variable length types such as VARCHAR or LOBs are not stored in a fixed position within a row.

Therefore two bytes for a row offset are required as well as two more bytes for their actual length in the row. - If the type is nullable, then an extra byte is needed to store the NULL

*value compression*as a table property.A

*value compressed*table uses a slightly different row format.In that format variable length data types take two bytes less and fixed length types take two bytes more.

In addition the fact that the row is compressed costs two more bytes per row.

Sounds confusing? Keep in mind that we are computing the defined row size here.

This is the

In addition the fact that the row is compressed costs two more bytes per row.

Sounds confusing? Keep in mind that we are computing the defined row size here.

This is the

**worst case**length. The goal of*value compression*is to reduce the**average, actual**row length.## GetRowSize(<tabschema>, <tabname>)

CREATE OR REPLACE FUNCTION GetRowSize(tabschema VARCHAR(128), tabname VARCHAR(128)) RETURNS INTEGER

SPECIFIC GETROWSIZE READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION BEGIN DECLARE rowsize INTEGER DEFAULT 0;

DECLARE loblength INTEGER;

DECLARE compression_mod INTEGER; SELECT CASE WHEN compression in ('B', 'V') THEN 2 ELSE 0 END INTO compression_mod FROM SYSCAT.TABLES AS T WHERE T.tabschema = getrowsize.tabschema AND T.tabname = getrowsize.tabname; FOR column AS SELECT 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 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 '

WHERE C.tabschema = getrowsize.tabschema AND C.tabname = getrowsize.tabname DO

SET loblength = CASE WHEN inline_length <> 0

THEN inline_length

WHEN metatype = 'R' THEN struct_inline_length

WHEN typename IN ('CLOB', 'BLOB', 'DBCLOB')

THEN CASE WHEN length <= 1024 THEN 68

WHEN length <= 8192 THEN 92

WHEN length <= 65536 THEN 116

WHEN length <= 524000 THEN 140

WHEN length <= 4190000 THEN 164

WHEN length <= 134000000 THEN 196

WHEN length <= 536000000 THEN 220

WHEN length <= 1070000000 THEN 252

WHEN length <= 1470000000 THEN 276

WHEN length <= 2147483647 THEN 312

ELSE raise_error('78000', 'LOB too long') END

WHEN typename IN ('LONG VARCHAR', 'LONG VARGRAPHIC')

THEN 20

WHEN typename = 'XML' THEN 80

ELSE 0 END; SET rowsize = rowsize + 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 raise_error('78000', 'Unknown type') END END + CASE WHEN compression_mod = 0 AND NULLS = 'Y' THEN 1 ELSE 0 END; END FOR; IF compression_mod <> 0 THEN SET rowsize = rowsize + 2; END IF;

RETURN rowsize;

END; / SELECT varchar(tabschema, 10), tabname, getrowsize(tabschema, tabname) as rowsize FROM SYSCAT.TABLES ORDER BY tabname FETCH FIRST 10 ROWS ONLY;

1 TABNAME ROWSIZE ---------- -------------------------------------------------- ----------- SYSIBMADM ADMINTABCOMPRESSINFO 354

SYSIBMADM ADMINTABINFO 476

SYSIBMADM ADMINTEMPCOLUMNS 1223

SYSIBMADM ADMINTEMPTABLES 719

SYSIBMADM APPLICATIONS 2188

SYSIBMADM APPL_PERFORMANCE 426

SYSCAT ATTRIBUTES 2271

SYSCAT AUDITPOLICIES 425

SYSCAT AUDITUSE 404

SYSIBMADM AUTHORIZATIONIDS 133 10 rows were retrieved.

## What about SQLCODE -670?

The example above displayed the row size for existing tables.

But when you get a SQLCODE -670 "table to wide" you never actually had a table.

How to find out by how much the row has "overshot"?

The easiest way to do that is to create a view with that same signature and then measure its rowsize:

But when you get a SQLCODE -670 "table to wide" you never actually had a table.

How to find out by how much the row has "overshot"?

The easiest way to do that is to create a view with that same signature and then measure its rowsize:

CREATE OR REPLACE VIEW too.wide( c1, c2, c3, c4)

AS SELECT CAST(NULL AS VARCHAR(30000)),

CAST(NULL AS INTEGER),

CAST('A' AS BLOB(1M)),

CAST(NULL AS VARGRAPHIC(2000))

FROM SYSIBM.SYSDUMMY1; VALUES getrowsize('TOO', 'WIDE');

1

-----------

34183

## Conclusion

Deriving the defined length of a row is not always easy. Having a routine that computes it can be helpful when defining a db schema.

You can enhance this routine, if you wish, by actually parsing a table signature.that way there would be no need to create a view.