For databases enabled for table partitioning, the TPIDXBUILD utility allows database administrators to rebuild an index or indexes for a partition while the database remains online, in addition to offline. TPIDXBUILD only rebuilds indexes for a table partition.
Prior to rebuilding indexes for a table partition, you must enable your database for the rebuild. The general process is as follows:
1. Enable database for table partition index rebuild with PROUTIL ENABLETPIDXBLD (online or offline). See PROUTILENABLETPIDXBLD qualifier.
3. Disable the database for table-partitioned index rebuild with PROUTIL DISABLETPIDXBLD (offline only). See PROUTIL DISABLETPIDXBLD qualifier for details.
Table-partitioned index rebuild acquires an exclusive table partition lock when performing the rebuild, blocking other processes from creating or deleting index objects. Once the rebuild starts, clients cannot use the index in a query, locked or no-lock.
To prevent users from accessing a rebuilt index with a stale cursor, ABL clients with a timestamp older than a current timestamp from TPIDXBUILD, must log out.
Table-partitioned index rebuild has significant differences from the full index rebuild (PROUTIL IDXBUILD). Those differences include:
Table-partitioned index rebuild can operate while the database is online.
You can still access the database if the index rebuild fails. If the index rebuild fails, the index is left offline and must be brought online with an index utility (index rebuild or activation) because not all of the index rebuild activity is logged. Users can access the database after a failed table-partitioned index rebuild, provided the new process goes through crash recovery.
You cannot use table-partitioned index rebuild to clear a tainted index flag. Only the full index rebuild can do that.
Table-partitioned index rebuild is not multi-threaded.
To improve performance, table-partitioned index rebuild deletes and rebuilds the index.
There is no menu-driven option to TPIDXBUILD, you must completely specify the rebuild on the command line.
You can track the progress of TPIDXBUILD using the User status (_UserStatus) Virtual System Table.
Table-partitioned index rebuild can operate while the database is online.