TRUNCATE

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 privileges held by the authorization ID of the statement must include at least one of the following:

• For the table identified in the statement:
  • The DELETE privilege on the table, and
  • The system authority *EXECUTE on the library containing the table.
• Database administrator authority

If the IGNORE DELETE TRIGGERS option is specified, the privileges held by the authorization ID of the statement must include the following:

• The ALTER privilege on the table and the system authority of *OBJOPR on the table.

If row access control or column access control is activated for the table, the privileges held by the authorization ID of the statement must include the following:

• The system authorities of *OBJOPR and *OBJEXIST on the table.

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Description

table-name

Identifies the table from which rows are to be deleted. The name must identify a table that exists at the current server. It must not identify a catalog table, a view, or a system-period temporal table.

DROP STORAGE or REUSE STORAGE

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

DROP STORAGE

All storage allocated for the table is released and made available. This is the default.

REUSE STORAGE

All storage allocated for the table will continue to be allocated for the table, but the storage will be considered empty.

IGNORE DELETE TRIGGERS or RESTRICT WHEN DELETE TRIGGERS

Specifies what to do when 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 truncation operation. This is the default.

RESTRICT WHEN DELETE TRIGGERS

Specifies that an error is returned if delete triggers are defined on the table

CONTINUE IDENTITY or RESTART IDENTITY

Specifies how to handle generation of identity column values.

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. This is the default.

RESTART IDENTITY

If an identity column exists for the table, the next identity column value generated is the initial value that was specified when the identity column was defined.

IMMEDIATE

Specifies that the truncate operation is processed immediately and cannot be undone.

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 truncate operation cannot be performed if any session has a cursor open on the table or holds a lock on the table.

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

Notes

Referential Integrity: The identified table cannot be a parent table in a referential constraint.

Number of rows deleted: The ROW_COUNT condition area item in the SQL Diagnostics Area (or SQLERRD(3) in the SQLCA) is set to -1 for the truncate operation. The total number of rows that were deleted from the table is not returned.

If no rows exist in the table, a SQLSTATE value of '02000' is returned.

For a description of the SQLCA, see SQLCA (SQL communication area).

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