PROUTIL IDXMOVE qualifier

Moves an index from one application data area to another while the database remains online.

Syntax

proutil db-name -C idxmove
[ owner-name.]table-name.index-name area-name
[ tenant tenant-name| group group-name|
partition partition-name | composite initial]

Parameters

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.

Specifies the area name of the target application data area into which the index is to be moved. Area names that contain spaces must be quoted. For example, "Area Name."

For databases enabled for multi-tenancy, specifies that the partition of the index owned by tenant-name is to be moved.

If tenant is specified, and tenant-name is not valid, or the index is not a multi-tenant index, IDXMOVE exits with an error.

For databases enabled for multi-tenancy, specifies that the partition of the index owned by group-name is to be moved.

If group is specified, and group-name is not valid, or the index is not a multi-tenant index, IDXMOVE exits with an error.

For databases enabled for table partitioning, specifies the named partition of the index to be moved.

If partition is specified, and partition-name is not valid, or index-name is not a local index, IDXMOVE exits with an error. Specifying partition is only allowed on databases enabled for table partitioning.

For databases enabled for table partitioning, specifies the composite initial partition of the index is to be moved.

If the composite initial partition for table-name does not exist, or index-name is not a local index, IDXMOVE exits with an error. Specifying composite initial is only allowed on databases enabled for table partitioning.

Phase 1 — The new index is constructed in the new area. The old index remains in the old area, and all users can continue to use the index for read operations.

Phase 2 — The old index is killed, and all the blocks of the old index are removed to the free block chain. For a large index, this phase might take a significant amount of time. During this phase all operations on the index are blocked until the new index is available; users accessing the index might experience a freeze in their applications.

Notes

While you can move indexes online, no writes to the table or its indexes are allowed during the move. The IDXMOVE utility acquires a SHARE lock on the table, which blocks all attempts to modify records in the table. Run the utility during a period when the system is relatively idle or when users are doing work that does not access the table.

No other administrative operation on the moved index is allowed during the move of the index; it will be blocked. For example, you cannot run an index move utility and at the same time run the index fix or the index compacting utilities on the same index.

Because the index move utility needs to acquire a share lock on the table, there is a possibility that it will have to wait before it can acquire the lock and start operating.

You might be able to improve performance by moving indexes that are heavily used to an application data area on a faster disk.

For multi-tenant indexes, you can not specify both group and tenant, but you must specify one. The area-name parameter must specify a Type II area.

Shared indexes of a multi-tenant database are moved without specifying tenant or group on the command line.

For an index of a partitioned table, you must specify partition, and the area-name parameter must specify a Type II area.

For an index of a partitioned table, you cannot move the index of a partition that is in a rebuilding state.

The _UserStatus virtual system table displays the utility's progress. See MaintainingDatabase Structure for more information.