For large history tables, or for cost savings in cloud deployments, you may need to rapidly and efficiently truncate all the data associated with a particular table while the database is running.

To truncate the table when you move it, use the TRUNCATE qualifier on the PROUTIL TABLEMOVE command. The TRUNCATE qualifier truncates the table and associated indexes and LOBs, then either optionally moves them to a new storage area or leaves them 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 original area where the table resided.

For examples of moving tables and their associated indexes and LOBs, see Specify target areas when moving table data.

Note: For the complete syntax description, see PROUTIL TABLEMOVE qualifier. The _UserStatus virtual system table (VST) displays the progress of the PROUTIL utility. For more information on VSTs, see Virtual System Tables.
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 performed in a single transaction. Canceling the transaction does not affect the data in the table.

You can truncate multi-tenant tables, the _Cdc-Change-Tracking table of Change Data Capture, and the _KeyEvt table of Keyevents.

Note: You cannot truncate auditing tables, schema tables, or partitioned tables. To truncate partitioned 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 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.

Table truncation is the first step in returning space used by tables to the operating system. After you truncate tables and associated objects, see Remove data storage areas for the next step in the process.