473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 9868
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: VA | last post by:
I got the following function to swap table columns from somewhere on the Internet function swapColumns (table, colIndex1, colIndex2) { if (table && table.rows && table.insertBefore && colIndex1...
4
by: VA | last post by:
There are a lot of excellent drag-n-drop Javascript libraries available out there http://script.aculo.us/ http://www.walterzorn.com/dragdrop/dragdrop_e.htm...
17
by: Romulo NF | last post by:
I´ve seen some people around the web wondering about a code to reorder table columns using the mouse (drag and drop). Since i had already made one script to reorder table lines (TR´s) i decided to...
2
by: Jacksm | last post by:
How can I align an asp:table columns with gridview columns (the widths)? I have tried setting table.column(0).width = gridview.column(0).width at page_load but it doesn't work. Thanks in advance
0
by: negmat | last post by:
Guys, Does SQL Server allow for creating conditional constraints on the table columns? My table is: CREATE TABLE ( IDENTITY (1, 1) NOT NULL ,
1
by: archrajan | last post by:
I would like to have a script that can drag/drop table columns. and show/hide table columns. any help is appreciated. thanks in advance.
1
by: Ankit | last post by:
Hi guys i need to make a table to store a certain data using Tkinter..I have searched on the group but i have not been able to find a solution that would work for me..The thing is that i want my...
5
by: Romulo NF | last post by:
Greetings, I´m back here to show the new version of the drag & drop table columns (original script ). I´ve found some issues with the old script, specially when trying to use 2 tables with...
3
by: Ganapathi sundaram | last post by:
Dear All, I want to get more than one table columns as a single column by alternative. For eg, Table name = employee columns = empid, empname, address1,address2
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.