TRUNCATE

The DB2® TRUNCATE statement deletes all rows for either base tables or declared global temporary tables. The base table can be in a simple table space, a segmented table space, a partitioned table space, or a universal table space. If the table contains LOB or XML columns, the corresponding table spaces and indexes are also truncated.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privilege set that is defined below must include at least one of the following privileges:

The DELETE privilege for the table
Ownership of the table
DBADM authority for the database
DATAACCESS authority
SYSADM authority

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

Start of change If the IGNORE DELETE TRIGGERS option is in effect, the privilege set must include at least one of the following privileges: End of change

The ALTER privilege for the table
Ownership of the table
DBADM authority for the database
System DBADM authority
SYSADM authority

Start of change If row access control is activated for the table, the privilege set must include at least one of the following privileges or authorities: End of change

Ownership of the table
DBADM authority
SYSADM authority
SYSCTRL authority
System DBADM authority

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in Dynamic preparation and execution. (For more details on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.)

Syntax


             .-TABLE-.              .-DROP STORAGE------.   
>>-TRUNCATE--+-------+--table name--+-+---------------+-+------->
                                      '-REUSE STORAGE-'     

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

Description

table-name

Identifies the table that is to be truncated. The name must identify a table that exists at the current server. The name must not identify the following objects:

a view
an auxiliary table
an XML table
a catalog table
a system-period temporal table
an accelerator-only table

If table-name is a base table of a table space, all tables that are defined under the table will also be truncated (for example: auxiliary LOB table spaces and XML table spaces), and all of its associated indexes will also be truncated.

DROP STORAGE or REUSE STORAGE

Specifies whether to drop or reuse the existing storage that is allocated for the table.

DROP STORAGE: Specifies that all storage that is allocated for the table is released and made available for use for the same table or any other table that resides in the table space. The scope of DROP STORAGE is always at the table space level and the deallocated space is always available for reuse by all tables in the table space.
DROP STORAGE is the default.
REUSE STORAGE: Specifies that all storage that is allocated for the table will be emptied, but will continue to be allocated for the table. REUSE STORAGE is ignored for a table in a simple table space and the statement is processed as if DROP STORAGE is specified.

RESTRICT WHEN DELETE TRIGGERS or IGNORE DELETE TRIGGERS

Specifies what to do when delete triggers are defined on the table.

RESTRICT WHEN DELETE TRIGGERS: Specifies that an error is returned if delete triggers are defined on the table.
IGNORE DELETE TRIGGERS: Specifies that any delete triggers that are defined for the table are not activated by the truncate operation. Additional authorization is required for this option; see Authorization.
IGNORE DELETE TRIGGERS is the default.

IMMEDIATE

Specifies that the truncate operation is processed immediately and cannot be undone. If the IMMEDIATE option is specified, the table must not contain any uncommitted updates. In the case of a table in a multi-table table space, if there are any uncommitted updates to any table in the table space, the truncate operation will fail. Also, if there are any uncommitted CREATE, ALTER or DROP statements for any table in the table space, the truncate operation will fail.

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.

If IMMEDIATE is not specified, a ROLLBACK statement can undo the truncate operation.

The IMMEDIATE option can be specified for a table in a segmented table space or a universal table space which allows deallocated spaces to be reclaimed immediately for subsequent insert operations in the same unit of work without committing the truncate operation.

Notes

Rules and restrictions:

The truncate operation cannot be executed if the table is a parent table in an enforced referential constraint. End of change

The DB2 subsystem issues an error when it detects the existence of rule violations. Therefore, if the referential integrity constraint exists, the TRUNCATE statement will be restricted regardless of whether the child table contains rows.

Start of change The TRUNCATE statement cannot be used if the table is a system-maintained temporal table. End of change

If the TRUNCATE statement is used on a tables where any of the following conditions is true, the truncate operation will perform in a similar way to a mass delete operation:

Tables with Change Data Capture (CDC) attribute
The DB2 subsystem allows a table with the CDC-enabled attribute to be truncated without imposing any new restrictions.
Tables with multi-level security
If the table contains a column that is defined as a security label, the truncate operation needs to examine each row to determine if the security label of the authorization ID or role has the authority to delete that row. However, if the primary authorization ID or role has write-down privilege, verification of each row in the table is not necessary.
Tables with a VALIDPROC attribute
If a VALIDPROC is defined for the table, the truncate operation needs to verify the validity of each row in the table.

TRUNCATE and table spaces that are not logged:

The TRUNCATE TABLE statement can be used to remove a table space from the logical page list and to reset recover-pending status. When the table space is segmented or universal, the table is the only table in the table space, and the table does not have a VALIDPROC, referential constraints, delete triggers, or a SECURITY LABEL column, use the TRUNCATE TABLE statement to empty the table and the table space will be removed from the LPL and recover-pending status will be reset.

Truncating rows from a table with activated row permissions or column access control:

Row permissions and column access control is not enforced for the TRUNCATE statement. End of change

Examples

Example 1: Empty an unused inventory table regardless of any existing triggers and return its allocated space.

   TRUNCATE TABLE INVENTORY
			DROP STORAGE
       IGNORE DELETE TRIGGERS;

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;

Example 3: Empty an unused inventory table permanently (a ROLLBACK statement cannot undo the truncate operation when the IMMEDIATE option is specified) regardless of any existing delete triggers and preserve its allocated space for immediate use.

   TRUNCATE TABLE INVENTORY
       REUSE STORAGE
       IGNORE DELETE TRIGGERS
       IMMEDIATE;