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).
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.