I'm developing against a DB2 database, and at some point I get an error code "-670" when trying to add a new column. The error code indicates an insufficiently sized tablespace page size, anyway, I just went and ran a DESCRIBE command and I estimate I don't have more than 17K for the table width (I just added the numeric value contained in the "Length" column), anyway I'm not sure of that estimate since I have many BLOB columns. There is a SQL command (or DB2 command line utility) I could use to retrieve the exact info regarding the table width?
+1
A:
The sum of the LENGTH values in the output of the DESCRIBE TABLE command is a fairly accurate gauge of row width if you don't count the BLOB, CLOB, or LONG VARCHAR columns, which are not stored inline with the rest of the columns. There is a small amount of overhead bytes that aren't shown in that report, but it's usually not a significant portion of the table. DB2 has historically stored large objects separately to improve manageability and performance of the rest of the data in the table. DB2 has recently supported storing large objects inline in order to make use of compression and buffering, but I haven't seen it used widely and I doubt it will become a popular approach.
It sounds like it's time for you to relocate your table to a tablespace with a larger page size. Unless you're maxed out at a 32K page already, you have the option of doubling your page size by migrating your table to a larger bufferpool and tablespace, which will give you more room for additional columns. If you need to keep the data from the old table, loading from a cursor is a quick way to copy a large amount of data from one table to another within the same database. Your other option is to export the table's contents to a flatfile so you can drop and recreate the table in the wider tablespace and load the data back in.
Fred Sobotka
2009-08-25 18:21:17
In fact, we already reached the 32K limit (that's what we are guessing by now). By what I could see in the DB2 documentation, the max size for a BLOB descriptor is around 300 bytes, and considering this size we should be below 20K, but we are not sure if this is the case since the column is being created by a third party application upon a specific API call, so maybe the content is being added "inline" and that's what could be killing our table width limitation, do you know if there is a way to know if the content is added inline? Your previous answer was pretty good, thanks!
Abel Morelos
2009-08-27 06:47:56
A:
Answering my own question, this script can be very useful in giving you a very good estimation about the used table width size (hence, you can have an idea about the remaining free space):
select SUM(300) from sysibm.syscolumns where tbname = 'MY_TABLE' and (typename = 'BLOB' or typename = 'DBCLOB')
select 2 * SUM(length) from sysibm.syscolumns where tbname = 'MY_TABLE' and typename = 'VARGRAPHIC'
select SUM(length) from sysibm.syscolumns where tbname = 'MY_TABLE' and typename != 'BLOB' and typename != 'DBCLOB' and typename != 'VARGRAPHIC'
Abel Morelos
2009-11-06 06:41:10