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 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 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:
- CONTROL privilege on the table
- DBADM authority
Syntax
.-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, 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 on-line
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
- Referential Integrity: The table, and all tables
in a table hierarchy, must not be a parent table in an enforced referential
constraint (SQLSTATE 428GJ). A self-referencing RI constraint is permitted.
- Partitioned tables: The
table must not be in set integrity pending state due to being altered
to attach a data partition (SQLSTATE 55019). The table needs to be
checked for integrity prior to executing the TRUNCATE statement. With DB2® Version 9.7 Fix Pack 1 and later
releases, the table must not have any logically detached partitions
(SQLSTATE 55057). The asynchronous partition detach task must complete
prior to executing the TRUNCATE statement.
- Exclusive Access: No other session can have a cursor
open on the table, or a lock held on the table (SQLSTATE 25001).
- WITH HOLD cursors: The current session cannot have
a WITH HOLD cursor open on the table (SQLSTATE 25001).
Notes
- Table statistics: The statistics for the table are
not changed by the TRUNCATE statement.
- Number of rows deleted: SQLERRD(3) in the SQLCA
is set to -1 for the truncate operation. The number of rows that
were deleted from the table is not returned.
Example
Example 1: Empty an unused
inventory table regardless of any existing triggers and return its
allocated space.
TRUNCATE TABLE INVENTORY
IGNORE DELETE TRIGGERS
DROP STORAGE
IMMEDIATE
Example 2: Empty an
unused inventory table regardless of any existing delete triggers
but preserve its allocated space for later reuse.
TRUNCATE TABLE INVENTORY
REUSE STORAGE
IGNORE DELETE TRIGGERS
IMMEDIATE