TRUNCATE TABLE

The TRUNCATE TABLE command performs the same function as the DELETE command, except TRUNCATE TABLE removes all records from a table without recording the changes in the Forward and Backward Log files.

Invocation

TRUNCATE TABLE is an executable command that can be issued interactively or embedded in a host language. TRUNCATE TABLE can be dynamically prepared.

Authorization

To execute the TRUNCATE TABLE command, users must possess either DBADM authority for the database containing the table, the DELETE privilege on the table, overall SYSADM authority for the location, or be the owner of the table to be truncated.

Syntax

TRUNCATE TABLE table-name

Parameters:

table-name

Identifies the table to be truncated.

Description

The TRUNCATE TABLE command has the same functionality as DELETE FROM table-name, but is faster since deletes will not be logged and indexes are not incrementally maintained. The system will not be able to rollback the deleted records. This command is not allowed on a table containing a primary key, since it may be referenced by another table. Additionally, use of the TRUNCATE TABLE command alleviates the need to issue a COMPACT command on the table after records are deleted (to reclaim and reuse storage space).

Note:

The "records deleted" message produced after issuing the TRUNCATE TABLE command can indicate more records removed than originally present in the table. TRUNCATE TABLE does not stop to determine if a record was previously deleted from the table, it just truncates the table file -- counting all records equally, including those marked for deletion.