Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

August 01, 2003

Table Size in DB2

We came across an interesting feature at work today. In DB2 UDB there is a limit to the size of rows in a table. The number of bytes of storage used by all of the columns in a row cannot exceed 32672 bytes.

If the combined storage requirements of each of the columns in your table definition exceeds this limit you will get an error when you try and create it. The error message will contain "SQLSTATE=42611" which is how you will know you've hit this limit.

Well, its not as simple as that, because the actual limit is determined by the available buffer pools you have and the tablespaces which use them. I suspect that 32672 bytes is the largest possible limit. But thats another story and best left to proper DB2 DBAs.

I discovered this limitation (which isn't obvious from the documentation) when trying to specify a VARCHAR column of 32k. I reduced the column size to 30k and the table was successfully created.

Posted by Andy Todd at August 01, 2003 02:46 PM

Comments

Ouch! Thanks for that info. I need to file it away in the back of my mind to avoid DB2 for text-heavy applications.

Posted by: Babu on August 1, 2003 06:13 PM

You should use TEXT for character data that large, not VARCHAR.

Posted by: squirrel on November 27, 2003 08:25 PM