HIGH WATER MARK REDUCTION

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).

• If the REORG is using a temporary tablespace then the REORGed object will be copied over the original object, and will use the same extents (the object size may decrease, stay the same, or increase, depending on the data in the table).
• If it is an in-place REORG then another object will need to be created in the tablespace. This will be populated by the REORG, the other object will be removed, and we can now point to this new object. If there was not enough free space below the highwater mark to hold this new object then a REORG might actually cause the highwater mark to rise.

• Utilize the /LHWM and /DHWM options in the DB2DART tool.

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:

• The table's page0 (the starting page of a table object) is at the highwater mark position.
• Internal space management pages (such as SMP or Extent Map Pages (EMP)) are at the highwater mark position.
• An in-place table reorganization is executed and a temporary table space is not specified. This reorganized table might result in a higher high water mark and the table will not be copied back to overwrite the original one.
• Variable size records might result in worse padding within pages after a table reorganization, which must following index clustering. Padding can result in the table size INCREASING rather than decreasing after the REORG, which means the hwm will go up, not down

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:

• V5: FixPak 12
• V6: FixPak 3
• V7: GA (original release)
• or later

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:

• 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 DDL or database commands.

Note: Never run db2dart while the database is up and running.

The db2dart Utility

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.
      (/TSC and /TS require a tablespace id. See notes 1, 2)

  /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.
       (See notes 1, 6, 9. Make sure db is offline.)

  /IP  Initialize data page of a table as empty.
       (See note 9. Make sure db is offline.)

  /ETS  Extends the table limit in a 4K DMS tablespace, if possible.
      (/ETS requires a tablespace id. See notes 1, 2)

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
             (1) Check control info of varying types in rows.
             (see note 10)

  /PS number      Page number to start with.
             (suffix page number with 'p' for pool relative)

  /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.
             (M) minimize output to screen.
             (N) no output to screen.

  /RPTF Y/E/N     (Y) normal output produced to report file.
             (E) only error information to report file.
             (N) no report file output.

  /ERR Y/N/E      (Y) normal information log DART.INF file.
             (N) minimize output to information log
               DART.INF file.
             (E) minimize DART.INF file output, minimize screen
             output, only error information to report file.
             Default (Y).

  /WHAT DBBP OFF/ON  Database backup pending state.
             (OFF) off state.
             (ON) on state.
 

Notes:

1- For actions that require additional input values for identifying the data
  to act on, the input values can be specified as arguments along with the
  action, or if not specified then you will be prompted for input values.
  This does not apply for actions /DDEL and /IP, the input values required
  will be prompted for.

2- Actions /TSC, /TS, /ETS, and /DHWM require 1 input value - the
  tablespace ID.

3- Actions /T and /DEMP require two input values consisting of
  tablespace ID, and either of table object ID or table name.

4- Actions /DD, /DM and /DI require five input values consisting of either
  table object ID or table name, tablespace ID, page number to start with,
  number of pages, and verbose choice.

5- Action /MI requires two input values consisting of tablespace ID and
  index object ID.

6- Action /MT requires three input values consisting of either table object ID
  or table name, tablespace ID, and password.

7- Action /DP requires three input values consisting of tablespace ID (DMS
  tablespace only), page number to start with, and number of pages.

8- Action /LHWM requires a tablespace ID and the number of pages for the
  desired highwater mark after lowering it.

9- For password required by some actions, please contact DB2 service.

10- For value options where unique values identify different choices for
  the option, sum up the values to get the combination of choices.

11- Default location for report output file is the current directory in a
  non-MPP environment, and in the diagnostic directory in a MPP environment.

12- The scope of db2dart is single node.

13- In a MPP environment, you can use db2_all to invoke db2dart at all DB2
  logical nodes in a single invocation.

Suggestions to reduce tablespace High Water Mark

1. enter an DB2 environment
2. connect to a database
3. execute command ...
   db2 list tablespace
   note the id of the tablespace of which you want to reduce the high water mark, then disconnect
4. stop all applications using the database
5. deactivate the database using ...
   db2 deactivate database <db>
6. backup the database you want to practice the HWM reduction
7. start the db2dart utility
   db2dart <db> /LHWM /TSI <ts id> /np 0
8. analyze output file DART0000/<db>.RPT stored in the DB2 diagnose directory ...
• follow the instructions given, for example ...
  Step #60: Object ID = 3856
   => Offline REORG of this table using the LONGLOBDATA option (do not
    specify a temporary tablespace).
• always execute the suggested reorg utilitiy in „in-place“ mode, do not specify a temporary tablespace
• sometimes the suggestion is to export the table, drop, create und import the table data.
• use LONGLOBDATA option to include long LOB columns which are excluded by default. If the table contains LOB columns that do not use the COMPACT option, the LOB DATA storage object can be significantly larger following table reorganization. This can be a result of the order in which the rows were reorganized, and the types of table spaces used (SMS or DMS).
9. backup the database again
10. start your applications

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.

• For indexes db2dart suggests: Drop all indexes for parent table of this object, disconnect, reconnect, and then recreate them.
• If this is not possible REORG the indexes stored in the tablespaces. Try following!
• determine indexes stored in a tablespace using SQL:
  SELECT DISTINCT TBCREATOR, TBNAME
  FROM  SYSIBM.SYSINDEXES
  WHERE TBSPACEID = n ;
• Reorg the indexes:
  REORG INDEXES ALL FOR TABLE tbcreator.tbname [CLEANUP ONLY ALL]
  You may alternatively omit the CLEANUP option to improve object performance. But then you should into account take an increasing tablespace size.
  The CLEANUP ONLY ALL option will free committed pseudo empty pages, as well as remove committed pseudo deleted keys from pages that are not pseudo empty. This option will also try to merge adjacent leaf pages if doing so will result in a merged leaf page that has at least PCTFREE free space on the merged leaf page, where PCTFREE is the percent free space defined for the index at index creation time. The default PCTFREE is ten percent. If two pages can be merged, one of the pages will be freed. The number of pseudo deleted keys in an index, excluding those on pseudo empty pages, can be determined by running runstats and then selecting the NUMRIDS DELETED from SYSCAT.INDEXES. The ALL option will clean the NUMRIDS DELETED and the NUM EMPTY LEAFS if they are determined to be committed.
• The CLEANUP option only merges and frees empty index pages but does not move index pages to lower the high water mark. This can only be achived with a DROP or REORG command.
  Next appy following command to reduce the high water mark:
  REORG INDEXES ALL FOR TABLE tbcreator.tbname

Ressources

• IBM DB2 V8.2 Command Reference, db2dart utility: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/c0010412.htm
• IBM Support Note „Lowering the High-Water Mark of a Tablespace“ (2003): http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=high+water+mark&uid=swg21006526&loc=en_US&cs=utf-8&lang=en
• IBM Support Note „Highwater mark in the list tablespaces show detail not being RESET after a RUNSTATS or REORGS“ (2003): http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=high+water+mark&uid=swg21007334&loc=en_US&cs=utf-8&lang=en
• IBM Support Note „SQL20170N when decreasing the size of a table space“ (2006): http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=high+water+mark&uid=swg21234267&loc=en_US&cs=utf-8&lang=en
• SAP Support Portal: Note number 543897, 152531 and 486559

 

Notes On Backup and Recovery :

Notes On Backup and Recovery :

 

We all Need to be keen about the process model of back up with udb as this study makes us very clear and does not make us to be as mistaken while making the query and start up process for backup ,,,

 

 

It's worth spending some time discussing the DB2 backup process model. It helps you to understand the performance of your system

if you know what the DB2-spawned processes are doing.

 

                                     The backup process model.
 

 

 

 

 

On the left you can see the DB2 table spaces and their associated containers.

When you invoke the BACKUP utility, the db2agent process is spawned to control the flow between the buffer manipulators (the db2bm processes are used to read data off disk and into shared memory) and the db2med process (which reads from shared memory and writes the pages out to the target devices).

 

There are no restrictions with respect to how fast these processes will run; however, you can control them in relation to your environment's workload by using the DB2 throttling feature. To architect this utility to run as a fast as possible, the buffer manipulator has been coded such that it doesn't have to give data to a specific device controller. Think of it as a "race" -- DB2 doesn't care in what order the pages are placed on the backup media, just how fast it can get it there.

There is however one level of association: each table space will be assigned to one process that is solely responsible for processing all the data in that table space. The number of buffer manipulators is controlled by the parallelism option used when invoking the BACKUP utility. For example, if you set this option to 2, you will have two db2bm processes that can each read two separate table spaces in parallel.

The db2med processes spawned are equal to the number of targets you give it. For example, with Tivoli Storage Manager, if wanted to open three sessions, DB2 sets up three streams to the Tivoli server. This will help DB2 drive parallelism to the archive media.

 

If you are backing your data to a file system, and that file system is a virtualization of multiple disks, you should specify the mount point multiple times. For example, in a DB2 for Windows environment, you would enter the command as follows:

 

Incremental Backup:

Incremental backup first made its way into the product in the V7.2 release. We've been seeing the popularity of this type of backup increasing since it was first introduced -- especially for data warehouses where a small percentage of the data changes.

 

Incremental backup allows you to back up only index and data pages that have changed since a previous backup.

The exception is for long field and large object data in "dirty" table spaces, which are always backed up.

There is no incremental support for this type of data because these data types have a different physical format than our index and data pages, and currently DB2 cannot determine at backup time if that data has changed. This exception will be removed in a future release of DB2.

 

Incremental and delta backups.

From this figure you can see that an incremental backup is really based on the last full backup. This means that an incremental backup taken on Tuesday would include all of Monday's and Tuesday's changes since the full backup (in our example) was performed on Sunday.

 

Delta backups are based on the last incremental or delta backup. For a delta backup, you need to maintain all the backups taken since the last full backup in order to be able to reconstruct the data. For example, to recover data to the end of business day Wednesday, you would need the delta backup images for Monday, Tuesday, and Wednesday (or Wednesday's log files). If you took an incremental backup on Tuesday, then you would only need the incremental image from Tuesday, and Wednesday's delta backup image (or log files).

In addition to the changed pages, an incremental backup will include the database's metadata (database configuration, history file, table space definitions, and so on) to assist in recovery. This metadata is not incremental; it is a full copy each and every time.

 

By default, a DB2 database is not configured for incremental backups because there is a very minimal impact on runtime performance that's incurred to enable DB2 to perform this kind of backup. To enable this kind of backup, set the TRACKMOD database configuration parameter to ON (changing this parameter won't take affect until the next database activation).

When TRACKMOD is enabled, the first write operation will mark the data's hosting table space as dirty. If the table space isn't dirty, DB2 won't even look at it when a backup is started. If DB2 sees a dirty bit for a table space, it will go and inspect the extents within the marked table space (they are also marked with dirty bits) and ultimately only pull changed data pages into the backup image. The tracking feature used to support incremental backup is entirely internal and doesn't require any storage considerations.

 

An incremental backup is not permitted until a non-incremental backup is taken to set a base from which it can recover -- this is to support incremental restore which always requires a non-incremental base image.

 

Online Backup Considerations:

 

DB2 can perform an online or offline backup.

Online backups can run during normal SELECT, INSERT, DELETE, and UPDATE activity against the database. The only restriction when running an online backup in DB2 is that you cannot drop a table space while that table space is actively being backed up.

With an offline backup, DB2 knows that it's the only application reading from the database, and therefore it doesn't need to worry about locks. With an online backup, things are a little different.

DB2 has to implement a locking strategy for an online backup. For large objects and long field data, DB2 escalates Intent None (IN) locks to Share (S) locks and therefore is approximately 10% slower.

An online backup will likely require some more memory from the UTIL_HEAP memory allocation to allocate some internal structures that help support this operation.

 

The database history file

 

The database history file is becoming a more and more crucial part of the database engine. The database history file is a record of administrative operations. It is individually restored from any type of backup image (it is part of the metadata we detailed earlier). Events recorded in this file include operations such as backup, restore, rolling forward of the logs, loads, queiscing of the database or table space, table space alters, and dropped tables (when dropped table recovery is enabled). The information about these operations that are recorded include the objects affected (database, table space, or table), the location and device type (backup image or load copy), range of relevant log files, start and completion time of the operation, and the resultant SQLCA code. Previously this was an informational file that you could query. DB2 now uses this file to support recoverability like automatic restore. The new log manager uses this file as well.

This information is kept in a file and not in a DB2 table because the information is needed for recovery. If the database was ever not available, we could not leverage it for database recoverability. Therefore, the database history is stored in an ASCII file and included in the backup image where we can retrieve and process it.

Third party backup Support :

 

The media processes that db2 uses to write data out during a backup are built on a published set of interfaces that have been availablto the open market  since 1993.

This has lead to broad DB2 support for today's most popular backup vendors, including IBM Tivoli Storage Manager (TSM), Veritas NetBackup, Legato NetWorker, Compuer Associates, and more.

 

The DB2 backup interfaces.

 

 

 

 

When "plugged" into a vendor's archive software, instead of writing the backup information to a file, DB2 will write the backup data to these interfaces, sent as a stream of bits directly to the target archive server.

 

For example, if you're using TSM, then DB2 will load the TSM API, and so on. These libraries are loaded directly into the DB2 kernel and run in our address space. You don't have to worry about the quality of the vendor's plug-in code (as of DB2 V7+FP11) as DB2 will protect the instance address from a failure of a partner's code. In fact, before every operation, DB2 will get the state of the signal handlers before and reset them afterwards. This means that even if the vendor's code traps, the database engine will not go down (obviously the backup operation itself will fail).

 

DB2 has a long history of integration with Tivoli Storage Manager. In fact, DB2 was the second application to ever add support for the TSM API. Because of its long standing history with Tivoli (and the fact it's an IBM product), we ship direct support for TSM free of charge.