Question & Answer
Question
The high-water mark of a tablespace affects how small the tablespace can be shrunk during a redirected restore (or via ALTER TABLESPACE ). This document offers some hints and tips for lowering that value.
Answer
The high-water mark is a DMS tablespace attribute. To determine what the value is for a particular tablespace, use the following command and look for the corresponding entry for that tablespace.
LIST TABLESPACES SHOW DETAIL
The high-water mark is expressed in pages and represents the highest allocated page in the tablespace. This is not the same as the "Used pages" value since unused extents/pages under the highest allocated page do not affect the high-water mark (for example, if the very last page in the tablespace is in use but the rest of the tablespace is empty, the high-water mark will still point to this last page).
The high-water mark plays a major role during a redirected restore (a restore in which in the tablespace containers are redefined). Because the containers are being redefined, the number of containers can change and their sizes can change. As a result, the total number of pages in the tablespace and the total number of useable pages in the tablespace can change. For the redirected restore to be successful, the resulting number of useable pages must be at least as large as the high-water mark. This is an important point to remember.
The same can be said when trying to remove space from a tablespace using the REDUCE, RESIZE, or DROP options of ALTER TABLESPACE. The resulting number of pages must be greater than or equal to the high-water mark or the ALTER TABLESPACE statement will fail.
This can sometimes be a problem. For example, a tablespace is full, most of the data is then removed from it, and then there is a need to shrink the size of that tablespace. It is possible that there are data pages scattered throughout the tablespace holding the high-water mark at some high-value.
Various operations can be performed on the objects within the tablespace that may move the objects' extents around. However, it is difficult to determine what operations to do, what objects to do them on, and what the exact result of doing them will be.
Two high-water mark options were added to the db2dart tool to assist with this.
The first option is /DHWM. This will display a map of the tablespace and high-water mark information. The tablespace ID must be provided when using this option (it can be specified using the /TSI option or it will be prompted for if this option is not used).
For example, to display this information for a tablespace with an ID of 4 within database TESTDB:
db2dart testdb /dhwm /tsi 4
The output will be found in the file <dbname>.RPT (TESTDB.RPT in this case).
This information can be used to determine what object is holding up the high-water mark.
The second option is /LHWM. This will make suggestions about ways to lower the high-water mark for a given tablespace. The tablespace ID must be provided along with a desired high-water mark. The /TSI and /NP options can be used to specify these values, otherwise they will be prompted for. To get suggestions on lowering the high-water as much as possible, use a value of 0 as the desired high-water mark. Note that even an empty tablespace has used pages so it will be impossible to actually lower it down to 0.
For example, to display suggestions on lowering the high-water mark as much as possible for tablespace 6 in database TESTDB:
db2dart testdb /lhwm /tsi 6 /np 0
The output will be found in the file <dbname>.RPT (TESTDB.RPT in this case).
Some of the operations that are suggested include REORG, EXPORT/LOAD, and DROP/EXPORT/CREATE/LOAD.
Some things to note about the suggestions:
- The suggestions are based on rules and assumptions about what will move by the operation, what the resulting size of the object will be after doing the operation, etc. This isn't always predictable and therefore there may be times when the set of steps recommended do not result in the same state as predicted by db2dart. Consider running db2dart with this option after every operation to ensure that the steps are suggested as accurately as possible.
- db2dart works on a per node/partition basis so the suggestions do not take into account all of the other nodes in a multi-node database. Some of the operations work on all nodes at once (REORG for example) so an operation on one node may affect the placement of data on others as well.
If the high-water mark is being held up by an SMP extent (space map extent) that maps no used extents then it is not possible to reduce the high-water mark through online DDL or database commands. However, the /rhwm option of db2dart can be used to remove these SMP extents while the database is offline. Note that recoverable databases will be placed into a backup-pending state after the operation has been performed. This is because the work that db2dart does is not logged and a backup is required to capture the new state of the database.
If the database was created in DB2 V9.7, then the alter tablespace reduce option can be used to lower high water mark and reclaim the free space.
Additionally, for CLOB/LOB data, you would need to use the LONGBLOBDATA clause first on the table that includes any LOB/CLOB columns and then use the LOWER HWM clause next after completing the reorg. You can use db2pd -tablespaces option to review container data changes too.
** NOTE: Never run db2dart while the database is up and running.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21006526