Problem Statement:

Prior to DB2 V9.7, its a struggle to reduce the high water mark of a DMS tablespace. So what is tablespace high water mark anyway? High water mark of a tablespace can be defined as the highest allocated page number in a tablespace. Consider the following fabricated image of a tablespace with 3 tables in it using a total of 6300 pages.

Now Imagine you drop the Table 2 and as a result now your tablespace looks something like below only using 1300 pages.

However prior to DB2 V9.7 your DMS tablespaces are not reclaimable. That means the space that was cleared by dropping TABLE 2 is not available for other tablespaces or databases or file system to reuse it. This free space can only be used by the same tablespace.

Pre V9.7, you could try running db2dart with /LHWM option. It provides you with a report on how to lower the high water mark by performing reorgs on the tables in a specific order. However this is not guaranteed to work and can cause lengthy system outages.

Before looking at the solution, I would like to make a point on why this process was complicated prior to V9.7. As per my understanding (IBM developers might correct me) pre V9.7 bufferpool services layer had the ability to directly access pages of data on disk and cache the physical location of the object for future reference. As a consequence pages belonging to the object can no longer be moved from one location to another since the location has been cached.

Where as for tablespaces that were created at V9.7 and later, direct access to disk is no longer allowed and access is only through the tablespace meta-data pages. Therefore the high water mark can be lowered by moving the extents to a new location with the meta data updated accordingly.

Solution:

First you need to determine which tablespaces in your database are enabled for reclaimable storage. Following SQL will give you that information.

db2 "select varchar(tbsp_name, 30) as tbsp_name, tbsp_type, reclaimable_space_enabled, tbsp_free_pages from table (mon_get_tablespace(NULL,-2)) as t where reclaimable_space_enabled=1"

TBSP_NAME                      TBSP_TYPE  RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES
------------------------------ ---------- ------------------------- --------------------
SYSCATSPACE                    DMS                                1                  648
USERSPACE1                     DMS                                1                 7680
SYSTOOLSPACE                   DMS                                1                 8036
MYTBSP                         DMS                                1                 7424
INDEX_TS1                      DMS                                1                 7936
TS_16K                         DMS                                1                  256

Next you need to find out how many free pages are there below the high water mark. You can get this information from the following SQL. Also note that you need to know if the DMS tablespace is managed by Automatic storage or not.

db2 "select varchar(tbsp_name, 15) as tbsp_name, tbsp_free_pages, tbsp_page_top, tbsp_total_pages, tbsp_using_auto_storage from table (mon_get_tablespace(NULL,-2)) as t"

TBSP_NAME       TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_TOTAL_PAGES     TBSP_USING_AUTO_STORAGE
--------------- -------------------- -------------------- -------------------- -----------------------
SYSCATSPACE                      648                40308                40960                       1
TEMPSPACE1                         0                    0                    1                       1
USERSPACE1                      7680                  480                 8192                       1
SYSTOOLSPACE                    8036                  152                 8192                       1
MYTBSP                          7424                  736                 8192                       1
INDEX_TS1                       7936                  224                 8192                       1
TS_16K                           256                 1760                 2048                       1

1. Now, if your tablespace is Automatic Storage tablespace, All you need to do is run the following command. This one command should take care of lowering the high water mark and reducing the tablespace size.

db2 “ALTER TABLESPACE <TSNAME> REDUCE MAX”

2. If your tablespace type in Non-Automatic Storage DMS tablespace, you need to run the following command.

db2 “ALTER TABLESPACE <TSNAME> LOWER HIGH WATER MARK”

For Non-Automatic storage DMS tablespaces, you need to run an additional ALTER TABLESPACE command to reclaim the space above the high water mark, once the high water mark is lowered. You can choose appropriate option of ALTER TABLESPACE to shrink the containers.

Monitoring:

Finally, the extent movement process is asynchronous and you need to monitor the progress to really know if the high water mark is reduced. You can use the following two SQL to monitor the extent movement progress. In 1st SQL TOTAL_MOVE_TIME is measured in milliseconds.

1. db2 “select varchar(tbsp_name,30) as tbsp_name, last_extent, num_extents_moved, num_extents_left, total_move_time from table (mon_get_extent_movement_status(‘<TSNAME>’,-1)) AS T”

2. db2 “select varchar(tbsp_name, 30) as tbsp_name, tbsp_state from table (mon_get_tablespace(‘<TSNAME>’,-2)) as t”