When you truncate data storage areas, it:
  • Clears the space that was formerly allocated to tables and indexes in empty data storage areas, while preserving the schema structure.
  • Reduces the high-water mark to reflect object removal.
  • Supports disk locality. For example, the DBA can move a 100GB table that spans, with related objects, across 2TB in one area to the front 100GB of a recently truncated area.
  • Can help a Database Administrator address record fragmentation and speed up the process of loading data when moving tables and indexes to an area.

To truncate a data storage area, run PROUTIL TRUNCATE AREA.

You may use truncation when performing binary dump and load to reorganize data and improve performance, although performance may be slowed by buffer pool management when you run PROUTIL TRUNCATE AREA on a running database.

When you truncate a data storage area on a running database, the area to be truncated must contain no objects.

Truncation is an important step in the process of removing application data storage areas and extents from a database, described in Return data storage space to the operating system. However, because the PROSTRCT REMOVEONLINE utility performs both truncation and removal of data storage areas, and reduces the high-water mark, running PROUTIL TRUNCATE AREA is an optional step in the process.

Data storage area truncation works for Type I or Type II areas and on tables owned by PUB or non-PUB. You can truncate storage areas while a database is running or stopped:
  • Stop the database if you need to truncate areas with before-image or after-image files or transaction logs. For truncation of areas on a stopped database, you must first disable after-imaging and truncate the before-image file. Offline area truncation requires replication target rebase.
  • If you only need to truncate data storage areas, you may do so on a running database, and you do not need to disable after-imaging or to truncate the before-image file.