Topic
  • 3 replies
  • Latest Post - ‏2014-04-24T14:54:32Z by dattark
gdevelek
gdevelek
4 Posts

Pinned topic reclaiming space

‏2014-04-04T17:34:00Z |
Hi guys,

In v10 LUW, what's the best way to reclaim space after  removing BLOBs?

I use "update table_name set BLOB_COLUMN=null where... " in my update statement, and it runs to completion.

Then I run a REORG with LONGLOBDATA option.

Then I check the final size, and it's marginally smaller than the initial size, even though in my test I removed the BLOBs from half the rows of the table.

Is there a better way to remove BLOBs? Is there a better way to reclaim space?

Is the LONGLOBDATA applicable to BLOB columns?

Thanks a lot,

George

 

  • dattark
    dattark
    17 Posts

    Re: reclaiming space

    ‏2014-04-23T18:29:57Z  

    I assume you are looking to reclaim disk space used by the tablespace backed by a single container (device or file).

    AFAIK, in DB2 LUW , there is no way to reorg a tablespace such that the disk space used is reduced.  Try the db2dart command and look at it's recommendations.  

    The only option that I know of is export data from table, drop and recreate tablespace and tables, and import back in the data. Good luck, when you do this, have good backups, and watch for "nil" (not null) errors in db2diag.log when reimporting data. 

    If you have a DMS tablespace that has multiple containers, you can monkey with options like rebalancing, dropping a file etc, but have not tried that route.

     

  • gdevelek
    gdevelek
    4 Posts

    Re: reclaiming space

    ‏2014-04-24T08:52:43Z  
    • dattark
    • ‏2014-04-23T18:29:57Z

    I assume you are looking to reclaim disk space used by the tablespace backed by a single container (device or file).

    AFAIK, in DB2 LUW , there is no way to reorg a tablespace such that the disk space used is reduced.  Try the db2dart command and look at it's recommendations.  

    The only option that I know of is export data from table, drop and recreate tablespace and tables, and import back in the data. Good luck, when you do this, have good backups, and watch for "nil" (not null) errors in db2diag.log when reimporting data. 

    If you have a DMS tablespace that has multiple containers, you can monkey with options like rebalancing, dropping a file etc, but have not tried that route.

     

    Thanks for answering, I had lost hope.

    Let me clarify: I don't need to get diskspace back, it's not necessary. What I need is to be able to REUSE the space that was released when I set those BLOBs to null. When I say "reuse" I mean by inserting new rows.

    If this happens I'll be happy.

    Thanks,

    George

     

     

  • dattark
    dattark
    17 Posts

    Re: reclaiming space

    ‏2014-04-24T14:54:32Z  
    • gdevelek
    • ‏2014-04-24T08:52:43Z

    Thanks for answering, I had lost hope.

    Let me clarify: I don't need to get diskspace back, it's not necessary. What I need is to be able to REUSE the space that was released when I set those BLOBs to null. When I say "reuse" I mean by inserting new rows.

    If this happens I'll be happy.

    Thanks,

    George

     

     

    See the manuals on the command LIST TABLESPACES SHOW DETAIL

    It will show page usage information.