After deleting a big set of data from a table, we expect it to get smaller than it was before. But in Db2 V9.5 you need to lower high water mark to shrink unused space in tablespace.

After Db2 V9.7 you can use ALTER TABLESPACE command to lower high water mark and reduce a table’s size. You can find more information about V9.7 ALTER TABLESPACE statement from here. You need to use db2dart tool, to reclaim tablespace. In this article, I am going to cover: using db2dart utility to shrink tablespace.

Db2 V9.5 – How to Shrink Tablespace

In most cases, modern database management systems, shrink high watermark automatically. Db2 v9.5 released in 2007, so it’s an old version of Db2, you can not expect cutting edge features from Db2 V9.5.

What is db2dart?

db2dart is db2 database analysis and reporting tool. As you can see, it analysis problems on a database and suggests a solution. You can click here to navigate db2dart’s infocenter page.

In most cases, db2dart will suggest a solution (a solution that works!) for your problem. I always analyze tablespaces using db2dart to see it’s suggestions.

How to Shrink Tablespace

First of all, you need to find information about your tablespaces.

db2 list tablespaces show detail

You should see an output like this:

Total pages = 15783424
Useable pages = 15783392
Used pages = 7776640
Free pages = 8006752
High water mark (pages) = 15783392

As you see, High water mark pages are really higher than used pages. That means, you will not be able to use pages between them. Now create a report about tablespace’s “defragmentation” status.

Step 1 db2dart /DHWM

db2dart <dbname> /DHWM /TS 2

This command will create a report for your database. You should see defragmentation in this report file. (Empty spaces between filled pages) If you see a fragmentation in the report, now you should run /LHWM option

Step 2 db2dart /LHWM

db2dart <db_name> /LHWM

db2dart utility will ask you tablespace id and pages count to lower. Your input should be like 2,0. You need to write 0 to lower high water mark as much as possible. Check the report that tool generated. There should be a suggestion at the end of the report.

Step 3 Reorganize Your Tables

If it says you to reorg your tables offline, here is your receipt:

db2 -x "select tabname, tabschema from syscat.tables where type='T' and tabschema NOT LIKE 'SYS%'" | grep '[^[:blank:]]' | awk '{print $2"."$1}' | grep -v "-" | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/ ALLOW NO ACCESS LONGLOBDATA;/g' > reorg.sql
db2 -x "select tabname, tabschema from syscat.tables where type='T' and tabschema NOT LIKE 'SYS%'" | grep '[^[:blank:]]' | awk '{print $2"."$1}' | grep -v "-" | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/ ALLOW NO ACCESS;/g' > reorg_noac.sql

These commands will generate two sql files. Execute them using db2 -tvf option.

Step 4 Check Your Results

You might need to repeat step two and three more than once. You need to list tablespaces with detail to ensure if this worked or not.

If you have a question don’t hesitate to ask, leave a comment below, i’ll answer you as soon as possible.