TRUNCATE statement

The TRUNCATE statement deletes all of the rows from a table.The TRUNCATE statement deletes all data files from an Hadoop table. The table cannot be a Db2® local table or an HBase table.

Restriction: This statement is not supported for HBase tables.

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
DELETEIN privilege on the schema containing the table
CONTROL privilege on the table to be truncated
DATAACCESS authority on the schema containing the table
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
ALTERIN privilege on the schema containing the table and all subtables of a table hierarchy
CONTROL privilege on the table
SCHEMAADM authority on the schema containing the table and all subtables of a table hierarchy
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

Authorization

The authorization ID of the statement must have write access to the table's location directory.

Syntax

Notes:

¹ IMMEDIATE is optional only for column-organized tables.

Syntax

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.

table-name

Identifies the table to be truncated. The name must identify an existing Hadoop table. The table cannot be a Db2 local table or an HBase table.

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.

The IMMEDIATE clause can be excluded only for column organized tables.

If IMMEDIATE is not specified for column organized tables, the TRUNCATE statement does not need to be the first statement in the work unit and you can use a ROLLBACK statement to undo the truncate operation.

Attention: The IMMEDIATE clause is optional only in Db2 Version 11.5 Mod Pack 2 and later versions.

PARTITION partition-spec

This option applies only to partitioned tables. Use this option to truncate one or more partitions. The partition-spec is a comma-separated list of name-value pairs, where the name identifies a column.

FORCE

Use this option to truncate external tables.

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 before executing the TRUNCATE statement. The table must not have any logically detached partitions (SQLSTATE 55057). The asynchronous partition detach task must complete before 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.
On column-organized tables, the following rules apply to the IMMEDIATE clause when it is optional:
- If you do not specify the IMMEDIATE option, the TRUNCATE statement is processed and can be undone.
- The TRUNCATE statement can be anywhere within the transaction scope.
- The TRUNCATE statement can be undone before the transaction completes.
- The truncated table can be used immediately in the same unit of work.
- A ROLLBACK statement can be executed after a TRUNCATE statement without the IMMEDIATE option is processed. The TRUNCATE operation is then undone.
- For example, if another data change operation is done on the table after the TRUNCATE statement without the IMMEDIATE option is processed, and if then the ROLLBACK statement is executed, the TRUNCATE operation is also undone.
- You can then reclaim the storage manually by running REORG RECLAIM, or you can use the health monitor to trigger the storage after the TRUNCATE statement without the IMMEDIATE option is completed.
- The storage is automatically asynchronously reclaimed after the transaction completes.
- The truncate operation will perform in a similar way to a mass delete operation. The number of rows that are deleted from the table is returned at SQLERRD(4).
- The SQLWARN(5) value is ‘W’ because the underlying DELETE statement does not include a WHERE clause.

The TRUNCATE statement does not remove partitions from a partitioned table. To remove the partitions, use the ALTER TABLE...DROP PARTITION statement.
When the TRUNCATE statement runs concurrently with a SELECT statement, the SELECT statement might return SQLCODE -5105, and you might see FileNotFoundException in the bigsql.log file on your worker hosts.
When the TRUNCATE statement runs concurrently with an INSERT statement, you might see FileNotFoundException in the bigsql-sched.log file on your scheduler host.

Examples

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
```
Example 3: If IMMEDIATE is not specified for column organized tables, the TRUNCATE statement does not have to be the first statement in the work unit, and you can use a ROLLBACK statement to undo the truncate operation.
```
SELECT COUNT(*) FROM TAB10;
TRUNCATE TABLE TAB10;
SELECT COUNT(*) FROM TAB10;
ROLLBACK;
```
Note that the truncate statement without IMMEDIATE is not the first statement in the work unit, and that the truncate statement without IMMEDIATE can be rolled back.

Example 1: Truncate all data in table TAB1.
```
TRUNCATE TABLE TAB1
```
Example 2: Truncate all data in external table TAB2.
```
TRUNCATE TABLE TAB2 FORCE
```
Example 3: Specify all partition columns to truncate one partition in table TAB3_PARTITIONED.
```
TRUNCATE TABLE TAB3_PARTITIONED PARTITION (p1=1,p2=1)
```
Example 4: Specify some partition columns, starting with the highest level to truncate many partitions in table TAB4_PARTITIONED.
```
TRUNCATE TABLE TAB4_PARTITIONED PARTITION (p1=1)
```
Example 5: Truncate all partitions in table TAB5_PARTITIONED.
```
TRUNCATE TABLE TAB5_PARTITIONED
```
Example 6: Truncate one partition in external table TAB6_PARTITIONED.
```
TRUNCATE TABLE TAB6_PARTITIONED PARTITION (p1=1,p2=1) FORCE
```