SQL0670N creating table with 250 columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jo - 24 Jul 2006 12:54 GMT

Hi.

I'm getting the following error when creating a table with 250 columns
. I have tried creating it in a 32K tablespace , still the same issue.
Is this a limitation in DB2? I am using DB2 v8.2 FP 9a.

SQL0670N The row length of the table exceeded a limit of "32677"
bytes.

(Table space "".) SQLSTATE=54010

Any advice will be appreciated
Thanks a lot.

Reply to this Message

Philip Nelson - 24 Jul 2006 13:29 GMT

> Hi.
>
[quoted text clipped - 9 lines]
> Any advice will be appreciated
> Thanks a lot.

Jo,

It sounds like your issue is not the number of columns but the total length
of the columns in the columns. There is a limit of 32k (32677) on
"standard" columns (not counting LONG VARCHAR and LOB type columns).

I'd suggest that it is very unusual indeed to require that amount of data in
one table : it suggests that either you are using the wrong data types or
you have a design issue.

As a start, look at your VARCHAR columns and turn the longest ones into LONG
VARCHAR or CLOB. You maybe want, for performance reasons, to separate
your LONG data into a separate tablespace (create DMS tablespaces,
including a LARGE one and use "LONG IN tsname" in the CREATE TABLE
definition).

But above all, review your design to ensure that all the attributes you have
are directly related to the key : that you don't have repeating groups, for
example (e.g. ADDRESS1, ADDRESS2, ADDRESS3, ...).

HTH

Phil

Reply to this Message

Serge Rielau - 24 Jul 2006 13:40 GMT

> Hi.
>
[quoted text clipped - 4 lines]
> SQL0670N The row length of the table exceeded a limit of "32677"
> bytes.

The number of columns is not relevant. The width of the row is.
Note that DB2 will not allow runtime errors (unlike at least one other
vendor) of this kind. So what matters is not how much data you pack into
the row, it's how much you COULD pack into the row.
Do you have big VARCHAR() columns? Could they be turned into CLOBs or
shrunk?
Should this really be one table or should you normalize it better?

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Jo - 25 Jul 2006 01:21 GMT

Hi.
All the columns are declared using DBCLOB datatype. The SQL Server
columns were declared as NTEXT. I am in the process of migrating and I
dont think I can change the table definition. Any workarounds would be
appreciated .
Thanks a lot.

Reply to this Message

Serge Rielau - 25 Jul 2006 04:10 GMT

> Hi.
> All the columns are declared using DBCLOB datatype. The SQL Server
> columns were declared as NTEXT. I am in the process of migrating and I
> dont think I can change the table definition. Any workarounds would be
> appreciated .

You have 250 DBCLOB columns in a row? That's a first...
Anyway, how big are they each? The footprint of a LOB column in teh row
depends on the the maximum length of the LOB. If you make it, say 2MB it
will take a lot less space than 2GB.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Jo - 25 Jul 2006 08:29 GMT

Hi.
Yes 250 columns of DBCLOB(1G). I tried reducing to DBCLOB(1M) and put
it in a 32K tbsp. same issue. Is splitting the table the only way ?
Thanks a lot.

Reply to this Message

Knut Stolze - 25 Jul 2006 10:08 GMT

> Hi.
> Yes 250 columns of DBCLOB(1G). I tried reducing to DBCLOB(1M) and put
> it in a 32K tbsp. same issue. Is splitting the table the only way ?

A LOB locator for 1G LOBs requires 254 bytes at least
(http://tinyurl.com/csp4u, Table 6). So we have 63500 bytes required for a
row.

You have the following options:
(1) vertically partitioning of the table (splitting it)
(2) redesign your application if possible
(3) shorten the LOBs to 65K because that requires only 118 bytes for each
locator

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Philip Nelson - 25 Jul 2006 13:34 GMT

>> Hi.
>> Yes 250 columns of DBCLOB(1G). I tried reducing to DBCLOB(1M) and put
[quoted text clipped - 9 lines]
> (3) shorten the LOBs to 65K because that requires only 118 bytes for each
> locator

And I think that (as Serge, Knut and myself have all already said) redesign
would be the best option. Or if it is a package solution maybe look for
an alternative, as I can't imagine what problems you are going to run into
while trying to support this !!!

I'd be interested to know what type of application needs 250 1G DBCLOBs in a
table. Apart from anything elee your storage requirements could
potentially be immense : assuming you were actually using the complete
fields you are looking at 250 gig PER ROW !!!

Phil

Reply to this Message

Jo - 27 Jul 2006 15:54 GMT

Hi Everyone.
Thanks a lot for your inputs. We have reduced the size of all the
DBCLOB columns from 1G to 30K. The table creation is successful and
data also has been loaded successfully. However when viewing this data
from the control center we run into error about lob locator limit
exceeding. From command line, the select goes through without this
issue. We tried playing around with the lobcachesize parameter but
still no effect. Any suggestions would be appreciated !!
Thanks again.

Reply to this Message

Serge Rielau - 27 Jul 2006 16:37 GMT

> Hi Everyone.
> Thanks a lot for your inputs. We have reduced the size of all the
[quoted text clipped - 5 lines]
> still no effect. Any suggestions would be appreciated !!
> Thanks again.

How many rows are you selecting back in one shot?
In DB2 V8 for LUW I think 32000 locators are supported. In DB2 9 it's
some big number in the millions.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Knut Stolze - 27 Jul 2006 19:16 GMT

> Hi Everyone.
> Thanks a lot for your inputs. We have reduced the size of all the
> DBCLOB columns from 1G to 30K. The table creation is successful and
> data also has been loaded successfully. However when viewing this data
> from the control center we run into error about lob locator limit
> exceeding.

You could add the following to your [common] section in the db2cli.ini file:

PATCH2=50

That will cause LOB locators to be freed upon the next fetch.

> From command line, the select goes through without this
> issue. We tried playing around with the lobcachesize parameter but
> still no effect. Any suggestions would be appreciated !!

The CLP doesn't use LOB locators as does CLI (which is employed by the
control center).

Another alternative in your own application is to roll a commit/rollback
operation more frequently.

Serge, there is no change to the limit of 32100 - according to the V9 docs
(http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp). Am I
missing something?

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Jo - 28 Jul 2006 01:43 GMT

>From the control center we double-click on the table, the first 100

rows are fine, when we click to fetch the next 100 rows we run into
this error about lob locators. From CLP fetching all the rows is fine.
There are over 2200 rows. Is there any workaround to bypass this issue
when selecting from the control center? We are still using v8.2

Reply to this Message

Jo - 28 Jul 2006 02:33 GMT

If the PATCH2=50 is set then all the columns have an error message
inside them when viewed from the control center. The message says "
SQL0423N..."

> >From the control center we double-click on the table, the first 100
> rows are fine, when we click to fetch the next 100 rows we run into
> this error about lob locators. From CLP fetching all the rows is fine.
> There are over 2200 rows. Is there any workaround to bypass this issue
> when selecting from the control center? We are still using v8.2

Reply to this Message

Serge Rielau - 25 Jul 2006 12:55 GMT

> Hi.
> Yes 250 columns of DBCLOB(1G). I tried reducing to DBCLOB(1M) and put
> it in a 32K tbsp. same issue. Is splitting the table the only way ?
> Thanks a lot.

Check out "Table 14. Byte Counts of Columns by Data Type" in CREATE
TABLE of the SQL Reference.
32000/250 => 128 bytes per columns. That puts you to 64K/LOB columns.
Since it's a DBCLOCB you're down to 32K Characters per column.
Now here is an interesting question:
AFAIK SQL Server supports 8K pages only. Now I know that SQL Server
checks for rowsize limits only at runtime when inserting/temping a row.
Given that you don't have an issue on SQL Server it suggests that most
your 250 columns are either NULL for any given row or very small.
On first blush it appears that the table should be pivoted. I.e. you
should have one row per (not null) LOB.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message