Moves a table, or a partition of a table (partitioned or multi-tenant), and optionally moves its associated indexes, LOBs, or both, from one storage area to another while the database remains online. Optionally truncates the table, its associated indexes, and LOBs.

Syntax

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

Parameters

db-name

Specifies the name of the database containing the table.

owner-name

Specifies the owner of the table containing the data you want to move. 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 name of the table to be moved.

table-area

Specifies the name of the target application data area into which the table is to be moved. If an area name includes a space, enclose the name in quotation marks, for example, "Area Name".

index-area

Optionally, specifies the name of the target index area. If the target index area is supplied, the indexes are moved to that area. Otherwise, they are left in their existing location. You can move indexes to an area other than the area to which the table is being moved. If an area name includes a space, enclose the name in quotation marks, for example, "Area Name".

For a table-partitioned table, only local indexes of a specified partition are moved.

lob-area

Optionally, specifies the name of the target LOB area. If the target LOB area is supplied, the LOBs are moved to that area. Otherwise, they remain in their existing location. If you specify lob-area without truncate, the LOBs remain on the same area. You can move LOBs to an area other than the area to which the table is being moved. If an area name includes a space, enclose the name in quotation marks, for example, "Area Name".

For a table-partitioned table, only local LOBs of a specified partition are moved.

truncate

Specifies that the table and associated indexes and LOBs are to be truncated before being optionally moved to a new storage area or retained in the current storage area. The table and all associated LOBs must reside in a type II area. After the operation completes, the space that the table previously occupied is available for reuse by other objects in the table's original area.

tenant tenant-name

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

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

group group-name

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

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

partition partition-name

For databases enabled for table partitioning, specifies that the partition named partition-name is to be moved.

If partition is specified, and partition-name is not a valid partition, or the table is not a partitioned table, TABLEMOVE exits with an error.

composite initial

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

Notes

• If you omit the index-area parameter, the indexes associated with the table are not 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.

  The PROUTIL TABLEMOVE utility operates in four 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 have not specified the index-area parameter, then the indexes are rebuilt in their original area. If you have specified 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 killed and the _StorageObject records of the indexes and the table are updated.
• 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 recovery 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.
• 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 moving. An application that reads the table with an explicit NO-LOCK might be able to read records, but in some cases might 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.
• As part of truncation, you can move the table to a different storage area or leave it in the current area. When you specify PROUTIL TABLEMOVE TRUNCATE, you must specify a storage area name, whether or not you are moving the table.

  • If you specify a different target area, TRUNCATE truncates the table and moves it to the new area.
  • If you specify the same area, TRUNCATE truncates the table in place and retains it in the original storage area.

  You are prompted to acknowledge the operation before PROUTIL TABLEMOVE TRUNCATE truncates data. Responding NO cancels the operation and no changes occur. PROUTIL TABLEMOVE TRUNCATE is a recoverable operation that is performed in a single transaction. Canceling the transaction does not affect the table's original data.

  You can truncate multi-tenant tables, the _Cdc-Change-Tracking table of Change Data Capture, and the _KeyEvt table of Keyevents. You cannot truncate auditing tables or schema tables. To truncate partioned tables, use PROUTIL PARTITIONMANAGE TRUNCATE.

  Replication executes the same table truncate operation on each target.

  In general, PROUTIL TABLEMOVE TRUNCATE does not affect running applications. But because it acquires an exclusive lock on the table, and admin locks all the indexes for protection, it introduces contention for the table's data being truncated. Requests for data from ABL after truncation return a stale cursor error or return no data. No application triggers fire as the data is removed at the object level and not the record level. This may introduce referential integrity issues, so ensure that you understand the relationship on the table being truncated.

  PROUTIL TABLEMOVE TRUNCATE is logged in the.lg file and by the KeyEvents facility.

• No other administrative operation on any index of the moved table is allowed during the table move.
• The utility may have to wait for all the necessary locks to be available before it can start, which may take some time.

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

• For multi-tenant tables, you cannot specify both group and tenant, but you must specify one. The table-area parameter must specify a Type II area.
• Shared tables of a multi-tenant database are moved without specifying tenant or group on the command line.
• For table-partitioned tables, you cannot specify both partition and composite initial. The table-area and index-area parameters must specify Type II areas.
• Shared tables of a table-partitioned database are moved without specifying partition or composite initial on the command line.

• Moving and truncating tables is the first step in the process of removing application data storage areas and extents from a database, as described in Return data storage space to the operating system.