468,556 Members | 2,400 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,556 developers. It's quick & easy.

SQL0670N creating table with 250 columns

Jo
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.

Jul 24 '06 #1
13 9585
Jo wrote:
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.
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
Jul 24 '06 #2
Jo wrote:
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.
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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 24 '06 #3
Jo
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.

Jul 25 '06 #4
Jo wrote:
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 25 '06 #5
Jo
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.

Jul 25 '06 #6
Jo wrote:
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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jul 25 '06 #7
Jo wrote:
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 25 '06 #8
Knut Stolze wrote:
Jo wrote:
>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
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
Jul 25 '06 #9
Jo
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.

Jul 27 '06 #10
Jo wrote:
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.
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.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 27 '06 #11
Jo wrote:
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/infoce.../v9/index.jsp). Am I
missing something?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jul 27 '06 #12
Jo
>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

Jul 28 '06 #13
Jo
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..."

Jo wrote:
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
Jul 28 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by VA | last post: by
4 posts views Thread by VA | last post: by
2 posts views Thread by Jacksm | last post: by
1 post views Thread by archrajan | last post: by
1 post views Thread by Ankit | last post: by
3 posts views Thread by Ganapathi sundaram | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.