## Background

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.

## Algorithm

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.

## 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:

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.

Tags:
table
row
width
length
size
sql0670n
670
sqlcode
pagesize
getrowsize
1dantress commented PermalinkWhy isn't this procedure shipped by default with DB2 ?

2SergeRielau commented PermalinkPatrick,

Consider it an alpha release.

Serge

30UNX_현_지수 commented PermalinkSergeRielau,

4SergeRielau commented Permalink현 지수,

You can see in the "loblength" variable assignment how the pointer gets longer as the maximum size increases.

Serge

5Feng WanLi commented PermalinkI READ THE IBM DB2 INFORMATION CENTER, THAT SAY ,THE DECIMAL IS " p/2+1 bytes, where p is precision " .

6SergeRielau commented PermalinkFeng Piao,

I have updated the code in the article to

TRUNC(length / 2) + 1

Serge

7Kpfitzgerald commented PermalinkHey Serge and Rick,

8Kpfitzgerald commented PermalinkWell i got it working... not sure how or why... My best guess is running statistics after deploying the UDF. I had stats prior, not sure if its a MUST to run after seems to be the only real fix i can think of, i did mess with the deployment parameters also, but that was just removing the debug from the deploy and using db2admin instead of anther user account with admin rights.

9SergeRielau commented PermalinkKelly,

Reason being that it is written in SQL PL and thus datastudio won't be abel to do any harm to it.

All it does is to pass the text along to DB2.

The values the function looks at are also the defined lengths and not anything gleaned from statistics.

Perhaps it was function overloading that did you in? A previous attempt at the function with similar signature?

Glad you got it to work though.

Serge

1