TRUNCATE
The TRUNCATE statement deletes all of the rows from a table.
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.
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
Example 2: Empty an unused inventory table regardless of any existing 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 triggers and preserve its allocated space for reuse.
TRUNCATE TABLE INVENTORY
REUSE STORAGE
IGNORE DELETE TRIGGERS
IMMEDIATE