When the DBANALYS utility indicates that space utilization of an index is reduced to 60 percent or less, use the PROUTIL IDXCOMPACT utility to perform index compaction online. Performing index compaction increases space utilization of the index block to the compacting percentage specified. For example:

 proutil db-name -C idxcompact [owner-name.]table-name.index-name[n ]
Note: For the complete syntax description, see PROUTIL IDXCOMPACT qualifier

Performing index compaction reduces the number of blocks in the B-tree and possibly the number of B-tree levels, which improves query performance.

The index compacting utility operates in phases:

  • Phase 1 — If the index is a unique index, the delete chain is scanned and the index blocks are cleaned up by removing deleted entries.
  • Phase 2 — The nonleaf levels of the B-tree are compacted starting at the root working toward the leaf level.
  • Phase 3 — The leaf level is compacted.

The _UserStatus virtual system table displays the utility's progress.

After a mass delete operation, you can scan only the delete chain of a unique index by specifying PROUTIL IDXCOMPACT UNUSEDBLOCKS. Cleaning up the unused index blocks on the index delete chain improves OLTP performance.

Note: Because index compacting is performed online, other users can use the index simultaneously for read or write operation with no restrictions. Index compacting only locks one to three index blocks at a time, for a short time. This allows full concurrency.