Have you ever seen:
"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:
- 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 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.
In the end the algorithm isn't all that complex:
- 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
Beyond these rules DB2 also supports 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 worst case length. The goal of value compression is to reduce the average, actual row length.
CREATE OR REPLACE FUNCTION GetRowSize(tabschema VARCHAR(128), tabname VARCHAR(128))
SPECIFIC GETROWSIZE READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION
DECLARE rowsize INTEGER DEFAULT 0;
DECLARE loblength INTEGER;
DECLARE compression_mod INTEGER;
SELECT CASE WHEN compression in ('B', 'V') THEN 2 ELSE 0 END
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
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')
WHEN typename = 'XML' THEN 80
ELSE 0 END;
SET rowsize = rowsize +
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
CASE WHEN compression_mod = 0 AND NULLS = 'Y' THEN 1 ELSE 0 END;
IF compression_mod <> 0 THEN
SET rowsize = rowsize + 2;
SELECT varchar(tabschema, 10), tabname, getrowsize(tabschema, tabname) as rowsize
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:
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))
VALUES getrowsize('TOO', 'WIDE');
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.