asked on

DB2 Reorg BLOB tablespace

DB2 on windows server 2012 R2
I have a very large table (400 GB) that is large because of a BLOB field that holds a photo. We made a change to store the photo on a secure file share and then just have the table point to that location.

On my test system this reduced the table from 400 GB to 5 GB. When I tried that in prod, it blew out my data drives and crashed. System is up now but how can I have it do a REORG using a different drive to hold the working data? The main data drive is D: and we added a temp drive T: for this.

Or would it be faster/easier to just create a new BLOB tablespace and move the data from the existing to a new table space? Any help would be greatly appreciated. Thank you!!!

Jim

Tomas Helgi Johannsson

Hi,

You should use another disk as a temporary disk for BLOB reorgs as you are mentioning.
What you need to do is create a temporary or regular tablespace that is residing on the tempoary disk (in your case on T)
Then issue the reorg table statement with the additional option
LONGLOBDATA USE <your temporarytablespacename on T>
something like this

REORG MYSCHEMA.MYTABLE .... LONGLOBDATA USE MYTEMPTABLESPACE

Open in new window

Note that you need to add other options as well to make sure that you are doing either online or offline reorg depending on your needs.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001966.html

Moving the data between tablespaces will not solve your BLOB clutter only transfer it between tablespaces.

Other links to look at
https://www.ibm.com/support/pages/db2-reorganizes-lob-data-even-though-longlobdata-clause-not-specified-reorg-command
https://www.ibm.com/support/pages/steps-reclaim-all-available-space-reclaimable-storage-dms-automatic-storage-tablespace

Regards,
Tomas Helgi

Jim Youmans

ASKER

Thank you for the advice but now I have a different issue.
I created a tablespace on my X: drive like so.

CREATE TABLESPACE TempJim MANAGED BY SYSTEM USING ('X:\DBData');

Open in new window

and that worked fine. But when I issue the command

REORG TABLE SCHEMA.TABLEName USE TempJim;

Open in new window

I get this error.

SQL2213N  The specified table space is not a system temporary table space.

Open in new window

So I tried this.

CREATE TEMPORARY TABLESPACE TempJim2 MANAGED BY SYSTEM USING ('X:\DBData');

Open in new window

but then get this error.

ERROR [428B2] [IBM][DB2/NT64] SQL0298N  Bad container path.

Open in new window

What am I doing wrong? I just want to reclaim the space that is not being used any longer since we removed the photos from the table. The table is currently over 400 GB and in test we were able to knock that down to like 25 GB.
Thanks!

ASKER CERTIFIED SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Youmans

ASKER

Thank you!!