|
Reduction of the DB2 Tablespace High Water Mark |
|
Documentation bases on DB2 Version 8.2 FixPak 12. For DMS, the highwater mark is the current "end" of the tablespace address space. In other words, the page number of the first free extent following the last allocated extent of a table space. Note that this is not really a "high water mark", but rather a "current water mark", since the value can decrease. For SMS, this is not applicable. In determining whether a REORG or RUNSTAT would allow this parameter to be reset, consider whether the REORG is in place or not (for example, whether or not a temporary tablespace is being used).
This document provides troubleshooting information for the situation where you have deleted many records in a table and then try to resize the table space to take advantage of the freed space. After performing a REORG against the table, the ALTER TABLESPACE command fails to resize the table space to a smaller size. It returns an SQL20170N (There is not enough space in the table space ... for the specified action) error. The ALTER TABLESPACE command returns SQL20170N when the size of the table space is being reduced such that the amount of space being removed is greater than the amount of space above the high water mark (hwm). You can lower the high water mark of a table space that is being held by a data extent by performing the REORG TABLE command against the table space. This operation may not be successful in lowering the high water mark, however, if the high water mark is being held by a Space Map Pages (SMP) extent that cannot be moved by REORG commands. The REORG TABLE command will not lower the high water mark of a table space if any of the following conditions are true:
Solution: The high-water mark of a tablespace affects how small the tablespace can be shrunk during a redirected restore (or via ALTER TABLESPACE in Version 8). This document offers some hints and tips for lowering that value. 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. In Version 8, 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. These options became available in the following releases:
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 /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 /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:
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 DDL or database commands. Note: Never run db2dart while the database is up and running. Syntax: db2dart <DB Alias> <option> ... Help: /H Help. This help text. Inspect Action: /DB (default) Inspects entire database. /T Inspects a single table. (See notes 1, 3) /TSF Inspects only the tablespace files and containers. /TSC Inspects a tablespace's constructs (but not its tables). /TS Inspects a single tablespace and its tables. /ATSC Inspect constructs of all tablespaces (but not their tables). Data Format Action: /DD Dumps formatted table data. (See notes 1, 4) /DM Dumps formatted block map data (See notes 1, 4) /DI Dumps formatted index data. (See notes 1, 4) /DP Dumps pages in hex format. (See notes 1, 7) /DTSF Dumps formatted tablespace file information. /DEMP Dumps formatted EMP information for a DMS table. (See notes 1, 3) /DDEL Dumps formatted table data in delimited ASCII format. /DHWM Dumps highwater mark information. (See notes 1, 2) /LHWM Suggests ways of lowering highwater mark. (See notes 1, 8) /RHWM Hidden Function: Reduce highwater mark by deleting empty SMP pages Repair Action: (MAKE SURE DATABASE IS OFFLINE) /MI Mark index as invalid (make sure db is offline) (See notes 1, 5) /MT Mark table with drop-pending (unavailable) state. /IP Initialize data page of a table as empty. /ETS Extends the table limit in a 4K DMS tablespace, if possible. Change State Action: (MAKE SURE DATABASE IS OFFLINE) /CHST Change a state of the database. Input values options: /OI object-id Object ID. /TN table-name Table name. /TSI tablespace-id Tablespace ID. /ROW sum (2) Check LF/LOB descriptors /PS number Page number to start with. /NP number Number of pages. /V Y/N Y or N for verbose option. /PW password Contact DB2 service for valid password. /RPT path Optional path to place report output file. /RPTN file-name Optional name for report file. /SCR Y/M/N (Y) normal output produced to screen. /RPTF Y/E/N (Y) normal output produced to report file. /ERR Y/N/E (Y) normal information log DART.INF file. /WHAT DBBP OFF/ON Database backup pending state. Notes: 1- For actions that require additional input values for identifying the data 2- Actions /TSC, /TS, /ETS, and /DHWM require 1 input value - the 3- Actions /T and /DEMP require two input values consisting of 4- Actions /DD, /DM and /DI require five input values consisting of either 5- Action /MI requires two input values consisting of tablespace ID and 6- Action /MT requires three input values consisting of either table object ID 7- Action /DP requires three input values consisting of tablespace ID (DMS 8- Action /LHWM requires a tablespace ID and the number of pages for the 9- For password required by some actions, please contact DB2 service. 10- For value options where unique values identify different choices for 11- Default location for report output file is the current directory in a 12- The scope of db2dart is single node. 13- In a MPP environment, you can use db2_all to invoke db2dart at all DB2 Suggestions to reduce tablespace High Water Mark
Suggestions to reduce „index“-space High Water Mark To reduce th high water mark in tablespace containing indexes, called „index“-spaces in this document, use db2dart or an alternative method.
|
© Gernot Ruban