We help IT Professionals succeed at work.

DB2 BLOB fields

Jim Youmans
Jim Youmans asked
on
DB2 10.5 on Windows Server 2012 R2

I have a BLOB field in a table that has a photo stored in it.  There are 7 million rows and the table size is 490 GB.

We recently made changes to the system to store the photos on a file share instead and have the table hold a URL to that photo instead of the photo itself.

The developers re-purposed the BLOB field to hold the URL instead of creating a new VARCHAR field to do that.

I had assumed that the table size would shrink as the photos were replaced but so far we have replace 2.5 million photos with URL and I don't see any changes in the table size or the tablespace.

Is there a minimum  size that a BLOB will not go below?  Or do I have to REORG the table in order to see a change?

Thanks!!!!
Comment
Watch Question

Data Warehouse / Database Architect
Commented:
Hi Jim,

Not sure I'd have repurposed the BLOB for this purpose, but it should work fine.

DB2 doesn't release the space just because the object is deleted.  I don't believe that any of the major DBMS does.

There's a lot of overhead associated with reclaiming the space, and the only space that's truly recoverable on-the-fly are complete blocks that have emptied.

REORG the table and your space should come back.

And while you're at it, create a VARCHAR column with the link/path/URL/etc. to the file and populate it.  Then drop the BLOB column before the REORG.  

Good Luck!
Kent
Jim YoumansSr Database Administrator

Author

Commented:
Thank you!!
Jim YoumansSr Database Administrator

Author

Commented:
I am having an issue with reclaiming the space from the table with the photo after I delete all the photos from it.  

Before the delete, here are the table stats.

Table Size        Number of rows
64622 MB        1950532

After I run reorg on the table here are the stats.

Table Size        Number of rows
64622 MB        1950532

So I created a copy of the table called Table_Jim and then inserted the data from the original table into it and here are the stats.

Table Size        Number of rows
90 MB               1950532

How can I reclaim the space from the original table without having to create a new table and transfer the data to it?
Jim YoumansSr Database Administrator

Author

Commented:
I was able to find the answer.  You have to do a reorg with LONGLOBDATA keyword.

REORG TABLE ABC.TABLE1 LONGLOBDATA

Thanks all.
Kent OlsenData Warehouse / Database Architect

Commented:
Hi Jim,

Glad you found that.  I was tied up this morning....

Kent
Jim YoumansSr Database Administrator

Author

Commented:
thank you!