Raghu On Tech
Pro tips on managing data at scaleDB2 How to Lower High Water Mark For DMS Tablespaces ?

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”
0 Comments
Trackbacks/Pingbacks