Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2792
  • Last Modified:

How to claim unused diskspace from DB2 files?

Is there an equivalent to Postgres's "VACUUM" command in DB2 to shrink the database when it grows too much, in other words to release unused space from files? I need to call it on "per database" basis, rather than per table or per index.
0
mersis
Asked:
mersis
  • 3
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:

Hi mersis,

I think that you want to start with the REORG function.  It's available through the Command Line Interface.

Once the tablespace has been reorganized, it can be shrunk with other tools.


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Depending on platform, you might run db2dart /DHWM to determine how much space can be returned and what object is the delimiting factor (high water mark).


Kent
0
 
mersisAuthor Commented:
so REORG does not reclaim the actual space back, it only reorganizes tables? How can I shrinnk the tablespace then?
I would like to put all commands that have to be called into a stored proc if possible and run that from time to time.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mersisAuthor Commented:
isn't there anything as simple as:
DBCC SHRINKDATABASE (myDatabase);
?
Or otherwise how can I achieve the same behaviour, say by constructing a stored proc?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi mersis,

REORG works at the table and index level, not tablespace.  

If a tablespace contains 100 tables, the only way to properly shrink it is to reorg every table and index to ensure that the pages are repositioned away from the upper end of the tablespace.  But this isn't 100% as these tools are ineffective against clustered indexes or multi-dimensional clustering (MDC).

A couple more things to keep in mind.  DB2/LUW using SMS will grow and shrink the tablespace dynamically.  There's no need to jump through a lot of extra hoops to shrink the tablespace.  This feature has been in DB2 for at least 3 years now, possibly longer so if you've got UDB V8 or V9 this is at your disposal.  Also note that DB2 won't dynamically shrink the tablespace by 1 page.  I don't know the exact algorithm that it utilizes, but it's based on the high water mark, extent size, number of contiguous unused extents in the upper end of the file, and other factors.

How big is the tablespace and how much are you trying to shrink it?

Kent
0
 
momi_sabagCommented:
if you want to shrink a tablespace you have 2 options :
1) if it's dms, you  need to find a way to make db2 free up unsued space, i think that one way to do it will be export the data from the table, and then import it back in replace mode. this should free up extra space and defrag the data
2) if you tablespace is a dms tablespace you can run reorg on the tables and then use the alter tablespace command and reduce the size of the container
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now