DB2 storing CLOB strings in chunks of 1k

[Ian Boyd]

Customer wanted us to figure out why their database was growing at an
astronomical rate.

We broke down the various fields making up a table, and we found that the
..LB file was using about 1k per row, where the average length of the two
combined CLOB fields in the table was 12 bytes (4 bytes standard deviation).

So i hex edited the .lb file and found the problem, each clob value is
taking 0x400 bytes i.e. 1024 bytes.

So it will usually have a word or two the the user entered, followed by 1000
bytes of null.

Since the customer (censored insult here) insists that they be able to enter
comments without limit (meaning we must use a LOB field of some kind), what
field should we choose instead of CLOB so that the contents are not padded
up to be 1024 bytes long? The .LB file is only being about 1% used, and
this is just nasty. As it stands, the database is growing about 60% faster
than the data it holds.

Perhaps using NTFS file compression to shut them up?

This is only a problem because the customer is demanding to know where all
the extra space is going - and right now the answer is: DB2 is padding 6
bytes to take up 1024.

Keywords: db2 storing clobs multiple 1024 1k 0x400 bytes chunk lb file slack
space database size growing lb

[Mark A]

On Feb 23, 9:34 am, "Ian Boyd" <[e-mail]:

Customer wanted us to figure out why their database was growing at an
astronomical rate.

We broke down the various fields making up a table, and we found that the
.LB file was using about 1k per row, where the average length of the two
combined CLOB fields in the table was 12 bytes (4 bytes standard deviation).

So i hex edited the .lb file and found the problem, each clob value is
taking 0x400 bytes i.e. 1024 bytes.

So it will usually have a word or two the the user entered, followed by 1000
bytes of null.

Since the customer (censored insult here) insists that they be able to enter
comments without limit (meaning we must use a LOB field of some kind), what
field should we choose instead of CLOB so that the contents are not padded
up to be 1024 bytes long? The .LB file is only being about 1% used, and
this is just nasty. As it stands, the database is growing about 60% faster
than the data it holds.

Perhaps using NTFS file compression to shut them up?

This is only a problem because the customer is demanding to know where all
the extra space is going - and right now the answer is: DB2 is padding 6
bytes to take up 1024.

Keywords: db2 storing clobs multiple 1024 1k 0x400 bytes chunk lb file slack
space database size growing lb

Did you try the "COMPACT" option on the LOB column? I don't know how
much space it will save, but you could try it.

[Brian Tkatch]

On Fri, 23 Feb 2007 11:34:00 -0500, "Ian Boyd"
<ia***********@avatopia.comwrote:

>Customer wanted us to figure out why their database was growing at an
astronomical rate.

We broke down the various fields making up a table, and we found that the
.LB file was using about 1k per row, where the average length of the two
combined CLOB fields in the table was 12 bytes (4 bytes standard deviation).

So i hex edited the .lb file and found the problem, each clob value is
taking 0x400 bytes i.e. 1024 bytes.

So it will usually have a word or two the the user entered, followed by 1000
bytes of null.

Since the customer (censored insult here) insists that they be able to enter
comments without limit (meaning we must use a LOB field of some kind),

Actually, that is untrue. LOBs do have limits. Unlikely to ever hit
them, but... :)

Instead, CREATE a separate TABLE for comments.

CREATE TABLE Item_Comments
{
Id AUTONUMBER
Item INT REFERENCES ...
Comment VARCHAR(32000)
}

And just chop comments on their way in.

Or...

Use a VARCHAR in the main TABLE to hold comments up to 255 characters,
or the like. And, CREATE a comment TABLE to hold CLOBs.

CREATE TABLE Item_Comments
{
Item INT REFERENCES ...
Comment CLOB
}

Have a TRIGGER check ON INSERT if the value for the comment is more
than 255 characters, and if so, INSERT a NULL instead, and INSERT the
comment INTO the Comments TABLE instead.

Oh, and put LOB in their own TABLESPACE. Just easier for management.

B.

what
field should we choose instead of CLOB so that the contents are not padded
up to be 1024 bytes long? The .LB file is only being about 1% used, and
this is just nasty. As it stands, the database is growing about 60% faster
than the data it holds.

Perhaps using NTFS file compression to shut them up?

This is only a problem because the customer is demanding to know where all
the extra space is going - and right now the answer is: DB2 is padding 6
bytes to take up 1024.

Keywords: db2 storing clobs multiple 1024 1k 0x400 bytes chunk lb file slack
space database size growing lb

[Ian Boyd]

Actually, that is untrue. LOBs do have limits. Unlikely to ever hit

them, but... :)

As far as i'm concerned character(8000) would be a limit they'll never hit
either; but you can't explain that to the hoopleheads.

Instead, CREATE a separate TABLE for comments.

CREATE TABLE Item_Comments
{
Id AUTONUMBER
Item INT REFERENCES ...
Comment VARCHAR(32000)
}

And just chop comments on their way in.

Or...

Use a VARCHAR in the main TABLE to hold comments up to 255 characters,
or the like. And, CREATE a comment TABLE to hold CLOBs.

CREATE TABLE Item_Comments
{
Item INT REFERENCES ...
Comment CLOB
}

Have a TRIGGER check ON INSERT if the value for the comment is more
than 255 characters, and if so, INSERT a NULL instead, and INSERT the
comment INTO the Comments TABLE instead.

Oh, and put LOB in their own TABLESPACE. Just easier for management.

That would be an okay idea, except that it would require a rewrite of a
large amount of things. And since we also had our hands tied into using
Hibernate; it makes it even more of a mess.

If DB2 is unable to store CLOB text efficiently, then i'd rather tell them:
"It's a DB2 thing, because you wanted to be able to enter text without
limit. So either be happy with 4000 characters, or buy a bigger hard drive."

[Ian Boyd]

>So i hex edited the .lb file and found the problem, each clob value is

>taking 0x400 bytes i.e. 1024 bytes.

Did you try the "COMPACT" option on the LOB column? I don't know how
much space it will save, but you could try it.

Tried it, it doesn't save any space. DB2 still pads clob values to take up
0x400 bytes.

Reading the docs on COMPACT, after you mentioned it, i would have thought it
is exactly what we were after.

[Brian Tkatch]

On Mon, 26 Feb 2007 10:30:02 -0500, "Ian Boyd"
<ia***********@avatopia.comwrote:

>Actually, that is untrue. LOBs do have limits. Unlikely to ever hit
them, but... :)

As far as i'm concerned character(8000) would be a limit they'll never hit
either; but you can't explain that to the hoopleheads.

>Instead, CREATE a separate TABLE for comments.

CREATE TABLE Item_Comments
{
Id AUTONUMBER
Item INT REFERENCES ...
Comment VARCHAR(32000)
}

And just chop comments on their way in.

Or...

Use a VARCHAR in the main TABLE to hold comments up to 255 characters,
or the like. And, CREATE a comment TABLE to hold CLOBs.

CREATE TABLE Item_Comments
{
Item INT REFERENCES ...
Comment CLOB
}

Have a TRIGGER check ON INSERT if the value for the comment is more
than 255 characters, and if so, INSERT a NULL instead, and INSERT the
comment INTO the Comments TABLE instead.

Oh, and put LOB in their own TABLESPACE. Just easier for management.

That would be an okay idea, except that it would require a rewrite of a
large amount of things. And since we also had our hands tied into using
Hibernate; it makes it even more of a mess.

Is it possible you could use a VIEW instead? Because the first option
i mentioned should work very well with a VIEW.

>If DB2 is unable to store CLOB text efficiently, then i'd rather tell them:
"It's a DB2 thing, because you wanted to be able to enter text without
limit. So either be happy with 4000 characters, or buy a bigger hard drive."

I wonder if we could make idrathertellthem.com

Oh my, http://whatireallywanttosay.com/ is an actual link. /me cries.

B.

[Ian Boyd]

Is it possible you could use a VIEW instead? Because the first option

i mentioned should work very well with a VIEW.

Trying such things as having data in a view and a table is a nightmare to
deal with using Hibernate.
The customer's original inquiry was, "What's taking up all the database
space?"

My own (internal) response was, "i dunno, ask DB2. We're not going to start
debugging DB2 internal data structures."

Now i can say to the customer "It's how DB2 stores notes."

[Brian Tkatch]

On Mon, 26 Feb 2007 16:58:32 -0500, "Ian Boyd"
<ia***********@avatopia.comwrote:

>Is it possible you could use a VIEW instead? Because the first option
i mentioned should work very well with a VIEW.

Trying such things as having data in a view and a table is a nightmare to
deal with using Hibernate.

I have no idea what Hibernate is, so i'll just have to trust you on
this one.

B.

>The customer's original inquiry was, "What's taking up all the database
space?"

My own (internal) response was, "i dunno, ask DB2. We're not going to start
debugging DB2 internal data structures."

Now i can say to the customer "It's how DB2 stores notes."