473,144 Members | 2,539 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,144 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 9855
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
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
2
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...
3
by: efe2023 | last post by:
Hello Everybody, There is SQL Server 2008 R2 database with MS Access front-end (linked ODBC tables). I can see all linked tables in MS Access and can add new rows. However, when trying to update...
3
by: jimatqsi | last post by:
So, I had this bright idea today that didn't quite work out. Or maybe it is working out but requires me to change a habit. I'm working on a form that will be a sub-form of another form. During...

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.