How to REALLY clean up a table containing LOB data

[afdsvicosa]

Hi everybody.
I want to know how I can really clean up a table that contains LOB data. I have a table that has various columns and one of them is a BLOB of size 134,217,728 Bytes.
When I try a "load from /dev/null of del replace into <my_table> nonrecoverable" command it logically cleans up the table. Like, if I do a select count(*) from <my_table> it returns 0 (zero). BUT, the internal db2 LOB files *.LB and *.LBA for that table remain unchanged! The LOB data is still in the server taking up disk space.
The same thing happens if I use "load from empty.ixf of ixf replace into <my_table> nonrecoverable" where empty.ixf is an empty file. It logically cleans up the data but the db2 internal files containing the LOB data are still on the server taking up disk space.
The workaround I use is to drop the table. In that case, db2 frees up the *.LB and *.LBA files for the LOB data for the table I dropped.
Does anybody know a better way of really cleaning up the table that contains the LOB data in a way that db2 frees up the disk space used to store the LOB data?

Thanks in advance,

Aroldo

 

[Truusvlugindewind]

Funny: you call dropping the table a "workaround" while that is the normal way to go...
Have you tried an offline "reorg" after the load? That might clean up things.

 

[afdsvicosa]

I also find it funny that you call dropping a table TO DO A DATA CLEAN UP work "the normal way to go". So, do you think it is normal to drop the whole table along with its indexes, constraints, and whatever else just to do a data clean up? And then have the work of recreating everything else? I DON'T!!
Anyways, I will definitely try the reorg and post the result here.
Thank you!

Aroldo

 

[blom0344]

Normal or not normal, it is a matter of simply running 1 DDL script for the table isn't it?

Ties Blom

 

[Truusvlugindewind]

Okay, okay: normal would be: "delete from table" but there must be a reason not to. Probably extensive log usage.
Dis you try the reorg?

 

[MarcLodge]

Aroldo,
Are you on version 9 of Db2 or earlier? I believe that this was a known problem with all versions less than v9 and that the new REORG in v9 solves the problem.

See the end of this Craig Mullins article

Marc

 

[Truusvlugindewind]

When you read the start-post you'll come to the conclusion that the topic-starter uses SMS table spaces and *.ixf files. This kind-of rules out system/i & system/z versions of DB2.

But still: I am convinced that an offline-reorg did the job (like to get confirmation form the topic-starter).

 

[afdsvicosa]

Hi Truusvlugindewind,

thank you for your posts. Unfortunately we had to do some upgrades on our servers and I did not have the opportunity to try the reorg yet! But I'll definitely try it.

First thing, yeah, you're right. We do not want to use "delete from" because of log usage. Our table has about 500GB of data. It's massive! Imagine, we had that much data sitting there on the server and we didn't understand how the server could be so full after we deleted all the db2 data. That's when we found out about the consequences of using "load from" to do the clean up work.

What I can tell for now is that a "IMPORT FROM dev/null OF DEL REPLACE INTO <table_name>" does the job. It frees up the space used by lob data.

I'll post the result of using reorg later.

Marc, thank you for the article! We are currently using db2 8.2 but we plan on upgrating to v9 soon. Good to know about the reorg improvements on the newer version.
Thank you