Use the PROUTIL TABLEMOVE utility to move a table and its associated indexes, LOBs, or both, from one storage area to another while the database remains online. For example:

proutil db-name -C tablemove [owner-name.]table-nametable-area[index-area]
Note: For the complete syntax description, see PROUTIL TABLEMOVE qualifier. The _UserStatus virtual system table (VST) displays the utility's progress. For more information on VSTs, see Virtual System Tables.

If you omit the index-area parameter, the indexes associated with the table will not be moved.

Moving the records of a table from one area to another invalidates all the ROWIDs and indexes of the table. Therefore, the indexes are rebuilt automatically by the utility whether you move them or not. You can move the indexes to an application data area other than the one to which you are moving the table. If you want to move only the indexes of a table to a separate application data area, use the PROUTIL IDXMOVE utility.

Moving a table's indexes with the TABLEMOVE qualifier is more efficient than moving a table separately and then moving the indexes with the IDXMOVE utility. Moving a table separately from its indexes wastes more disk space and causes the indexes to be rebuilt twice, which also takes longer.

Note: While you can move tables online, no access to the table or its indexes is recommended during the move. The utility acquires an EXCLUSIVE lock on the table while it is in the process of moving. An application that reads the table with an explicit NO-LOCK might be able to read records, but in some cases can get the wrong results, since the table move operation makes many changes to the indexes. Run the utility during a period when the system is relatively idle, or when users are doing work that does not access the table. You can also prevent user logins by running PROUTIL in database maintenance mode. For more information, see Restrict database access.

The PROUTIL TABLEMOVE utility operates in phases:

  • Phase 1 — The records are moved to the new area and a new primary key is built.
  • Phase 2 — All the secondary indexes are built:
    • If you did not specify the index-area parameter, then the indexes are rebuilt in their original area.
    • If you did specify the index-area parameter, then all the indexes are moved to the new area where they are rebuilt.
  • Phase 3 — All the records in the old area are removed.
  • Phase 4 — All the old indexes are removed and the _StorageObject records of the indexes and the table are updated.
Note: Although PROUTIL TABLEMOVE operates in phases, it moves a table and its indexes in a single transaction. To allow a full recovery to occur when a transaction is interrupted, every move and delete of each individual record is logged. As a result, moving a table requires the BI Area to be several times larger than the combined size of the table and its indexes. Therefore, before moving your table, determine if your available disk capacity is sufficient to support a variable BI extent that might grow to more than three times the size of the table and its indexes.

If you want to reuse the space used by a particular table, you can also use PROUTIL TABLEMOVE to truncate a table. See the TRUNCATE parameter for the PROUTIL TABLEMOVE qualifier.