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 specifies that the table and associated indexes and LOBs are to be truncated before being optionally moved to a new storage area or to remain 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.

For examples of moving tables and their associated indexes and LOBs, see Specify target areas when moving table data. For the complete syntax description, see PROUTIL TABLEMOVE qualifier.

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.

Before PROUTIL TABLEMOVE TRUNCATE truncates data, it prompts you to acknowledge the operation. 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 the CDC, 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 it introduces contention for the table data being truncated because it acquires an exclusive lock on the table and admin locks all the indexes for data protection. 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 at 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 Truncate data storage areas for the next step in the process.