DB2 BLOB fields
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!!!!
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!!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
I was able to find the answer. You have to do a reorg with LONGLOBDATA keyword.
REORG TABLE ABC.TABLE1 LONGLOBDATA
Thanks all.
REORG TABLE ABC.TABLE1 LONGLOBDATA
Thanks all.
Hi Jim,
Glad you found that. I was tied up this morning....
Kent
Glad you found that. I was tied up this morning....
Kent
ASKER
thank you!
ASKER