DB2 10.5 for Linux, UNIX, and Windows

TRUNCATE statement

The TRUNCATE statement deletes all of the rows from a table.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities for the table, and all subtables of a table hierarchy:
  • DELETE privilege on the table to be truncated
  • CONTROL privilege on the table to be truncated
  • DATAACCESS authority
To ignore any DELETE triggers that are defined on the table, the privileges held by the authorization ID of the statement must include at least one of the following authorities for the table, and all subtables of a table hierarchy:
  • ALTER privilege on the table
  • CONTROL privilege on the table
  • DBADM authority
To truncate a table that is protected by a security policy, the privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • CONTROL privilege on the table
  • DBADM authority
To truncate a table that has row access control activated, the authorization ID of the statement must include at least one of the following authorities:
  • CONTROL privilege on the table
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagram
             .-TABLE-.              .-DROP STORAGE--.   
>>-TRUNCATE--+-------+--table-name--+---------------+----------->
                                    '-REUSE STORAGE-'   

   .-IGNORE DELETE TRIGGERS--------.   
>--+-------------------------------+---------------------------->
   '-RESTRICT WHEN DELETE TRIGGERS-'   

   .-CONTINUE IDENTITY-.              
>--+-------------------+--IMMEDIATE----------------------------><

Description

table-name
Identifies the table to be truncated. The name must identify a table that exists at the current server (SQLSTATE 42704), but it cannot be a catalog table (SQLSTATE 42832), a nickname (SQLSTATE 42809), a view, a subtable, a staging table, a system-maintained materialized query table, a system-period temporal table (SQLSTATE 428HZ), or a range-clustered table (SQLSTATE 42807).

If table-name is the root table of a table hierarchy, all tables in the table hierarchy will be truncated.

DROP STORAGE or REUSE STORAGE
Specifies whether to drop or reuse the existing storage that is allocated for the table. The default is DROP STORAGE.
DROP STORAGE
All storage allocated for the table is released and made available. If this option is specified (implicitly or explicitly), an online backup would be blocked.
REUSE STORAGE
All storage allocated for the table will continue to be allocated for the table, but the storage will be considered empty. This option is only applicable to tables in DMS table spaces and is ignored otherwise.
IGNORE DELETE TRIGGERS or RESTRICT WHEN DELETE TRIGGERS
Specifies what to do when delete triggers are defined on the table. The default is IGNORE DELETE TRIGGERS.
IGNORE DELETE TRIGGERS
Any delete triggers that are defined for the table are not activated by the truncation operation.
RESTRICT WHEN DELETE TRIGGERS
An error is returned if delete triggers are defined on the table (SQLSTATE 428GJ).
CONTINUE IDENTITY
If an identity column exists for the table, the next identity column value generated continues with the next value that would have been generated if the TRUNCATE statement had not been executed.
IMMEDIATE
Specifies that the truncate operation is processed immediately and cannot be undone. The statement must be the first statement in a transaction (SQLSTATE 25001).

The truncated table is immediately available for use in the same unit of work. Although a ROLLBACK statement is allowed to execute after a TRUNCATE statement, the truncate operation is not undone, and the table remains in a truncated state. For example, if another data change operation is done on the table after the TRUNCATE IMMEDIATE statement and then the ROLLBACK statement is executed, the truncate operation will not be undone, but all other data change operations are undone.

Rules

Notes

Examples