By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,417 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,417 IT Pros & Developers. It's quick & easy.

CLOB length

P: n/a
When creating a table with a CLOB column, I can (should?) specify the
maximum length of the CLOB. What purpose does this serve apart from
restring the length of data that can be inserted. Are there any
advantages in specifying a smaller max length than a larger one?

I presume that when a row is inserted, the space allocated to the CLOB
value is limited to only the space needed to store the actual value
inserted, regardless of what the maximum length is.

Thanks in advance ...
Steve
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Each CLOB takes up some space in the row of the table in addition to the
actual space for the CLOB data. If you give a CLOB a smaller maximum size,
it will take up less space in the row. (The total size of your row is
limited by the page size of the table space for the table.)

If you look at CREATE TABLE in the SQL Reference, you'll find a table that
shows the space used in the row for various sizes of CLOBs.

--
__________________________________________________ ___________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Lab

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2...s2unix/support
Nov 12 '05 #2

P: n/a
But how about this situation with two tables? A little more overhead,
but maybe better if using raw/managed by database? and efficient use
of indices. Assume more than 1000 rows?

First table:
CREATE TABLE BLOB_TABLE_ID (
BLOB_ID INT NOT NULL GENERATED ALWAYS...
BLOB_NAME VARCHAR(100),
BLOB_TYPE VARCHAR(10),
PRIMARY KEY (BLOB_ID))
IN CERTAINTABLESPACE;
Second table:
CREATE TABLE BLOB_TABLE (
BLOB_ID INT NOT NULL,
BLOB_IMAGE BLOB NOT NULL,
PRIMARY KEY (BLOB_ID),
CONSTRAINT BT FOREIGN KEY (BLOB_ID) REFERENCES BLOB_TABLE_ID(BLOB_ID)
ON DELETE CASCADE);
SS
Douglas Doole <do***@ca.ibm.com> wrote in message news:<c2**********@hanover.torolab.ibm.com>...
Each CLOB takes up some space in the row of the table in addition to the
actual space for the CLOB data. If you give a CLOB a smaller maximum size,
it will take up less space in the row. (The total size of your row is
limited by the page size of the table space for the table.)

If you look at CREATE TABLE in the SQL Reference, you'll find a table that
shows the space used in the row for various sizes of CLOBs.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.