Raghu On Tech

Pro tips on managing data at scale

DB2 How to Lower High Water Mark For DMS Tablespaces ?

by | Jan 19, 2018 | 0 comments

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.

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.

 

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”

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shares
Share This