PROUTIL IDXCOMPACT qualifier

Performs index compaction. 

proutil db-name -C idxcompact [ owner-name.]table-name.index-name 
   [ tenant tenant-name| group group-name | 
         partition partition-name | composite initial ]
[n] | [ unusedblocks ] [ -compactonly ]

Parameters

db-name
Specifies the source database name.
owner-name
Specifies the owner of the table containing the data you want to dump. You must specify an owner name unless the table's name is unique within the database, or the table is owned by PUB. By default, ABL tables are owned by PUB.
table-name
Specifies the source table containing the source index to be compacted.
index-name
Specifies the source index to be compacted.
tenant tenant-name
Specifies that the IDXCOMPACT operation is to be performed only on the index partitions of tenant-name. If tenant-name does not own any partition of the specified index, IDXCOMPACT exits with an error. Specifying tenant is only allowed on databases enabled for multi-tenancy.
group group-name
Specifies that the IDXCOMPACT operation is to be performed only on the index partitions of group-name. If group-name does not own any partition of the specified index, IDXCOMPACT exits with an error. Specifying group is only allowed on databases enabled for multi-tenancy.
partition partition-name
Specifies that the IDXCOMPACT operation is to be performed only on the indexes of the partition-name table partition. IDXCOMPACT exits with an error if partition-name is not a partition for table-name, if partition-name is not allocated or in a rebuilding state, or if index-name is not a local index for table-name. Specifying partition is only allowed on databases enabled for table partitioning.
composite initial
Specifies that the IDXCOMPACT operation is performed only on the indexes of the initial partition for a partitioned table. IDXCOMPACT exits with an error if the composite initial partition for table-name does not exist, if the composite initial partition is not allocated or in a rebuilding state, or if index-name is not a local index for table-name. Specifying composite initial is only allowed on databases enabled for table partitioning.
n
Optional parameter that specifies the degree of index compaction. You can specify an integer >=50 and <=100. The default value is 80. If you do not specify n, 80 is used. Do not specify n with unusedblocks.
unusedblocks
Optional parameter that specifies that the IDXCOMPACT operation is to scan only the delete chain and to clean up blocks if the index is a unique index. This option improves OLTP performance and speeds up certain PROUTIL IDXCOMPACT operations, because the unused blocks can take up a lot of space, for example, after a mass delete operation. Do not specify n with unusedblocks.
-compactonly
Optional parameter which, for a unique index, affects the behavior of the IDXCOMPACT operation, depending on whether unusedblocks is also specified:

  • Only -compactonly is specified—IDXCOMPACT only compacts the index tree and does not scan for delete holders or perform block clean-up.

  • Both unusedblocks and -compactonly are specified—IDXCOMPACT processes the index delete chain and compacts the index. However, it does not search for delete holders in the index blocks that are not part of the index delete chain.

  • Both unusedblocks and -compactonly are not specified—IDXCOMPACT removes the index delete holders from the index tree that are not on the index delete chain, regardless of the specified percentile or degree of compaction. It also performs block clean-up from the index delete chain.

  • Only unusedblocks is specified—IDXCOMPACT scans only the index delete chain and cleans up blocks.

Notes

  • Index compaction is recommended when the PROUTIL IDXANALYS utility indicates that space utilization of an index is reduced to 60 percent or less. Index compaction increases space utilization of the index block to the compacting percentage specified by n.
  • 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 block count is computed.
    • Phase 3 — The non-leaf levels of the B-tree are compacted starting at the root working toward the leaf level.
    • Phase 4 — The leaf level is compacted.
  • PROUTIL IDXCOMPACT can be run either online or offline.
  • In addition to compacting an index, this utility clears dead entries left after entries have been deleted from unique indexes.
  • If the index specified is a partitioned index, and the partition qualifier is not supplied, all partitions of the index are processed. Index partitions that are read-only, rebuilding, or not allocated are skipped.
  • Multiple instances of PROUTIL IDXCOMPACT can run simultaneously for different partitions of the same index.
  • 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, allowing full concurrency.
  • The IDXCOMPACT utility does not lock any record or table.
  • No other administrative operation on the index is allowed during the compacting process.
  • In rare cases where the required percentage of compaction is very high, the compacting percentage might not be reached. Repeating the compacting process a second time might obtain better results.
  • For more information on the description of how to monitor the progress of this utility using the _UserStatus virtual system table (VST), see User status (_UserStatus).
  • For more information on how to troubleshoot index compaction and IDXCOMPACT status messages, see Troubleshoot index compaction.