4.5.1 Specification format and rules for the TRUNCATE TABLE statement
The TRUNCATE TABLE statement deletes all the rows in a base table.
- Organization of this subsection
(1) Specification format
TRUNCATE TABLE-statement ::= TRUNCATE TABLE table-name
(2) Explanation of specification format
- table-name
-
Specifies the name of the base table whose rows are to be deleted (the row deletion target table ). For the rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.
Note that the following tables cannot be specified:
-
Viewed tables
-
Dictionary tables
-
System tables
-
- Note
-
If an archivable multi-chunk table is specified, the data stored in chunks that are archived and not archived is deleted.
(3) Privileges required at execution
To execute the TRUNCATE TABLE statement, both of the following privileges are required:
-
The CONNECT privilege
-
The TRUNCATE privilege on the table
(4) Rules
-
During the execution of the TRUNCATE TABLE statement, the DB area is locked in exclusive mode. Therefore, you cannot execute the TRUNCATE TABLE statement while performing operations on another table or index stored in the DB area that contains the table to be processed, or the index of the table to be processed.
-
If the TRUNCATE TABLE statement terminates successfully, a COMMIT statement is automatically executed before the transaction terminates. Therefore, there is no need to execute a COMMIT statement after the execution of the TRUNCATE TABLE statement.
-
If both of the following conditions are met, the TRUNCATE TABLE statement will result in an error.
-
There is a cursor performing retrieval from the table targeted by the TRUNCATE TABLE statement in the connection where the TRUNCATE TABLE statement is executing
-
The cursor is open
-
-
If both of the following conditions are met, the TRUNCATE TABLE statement will result in an error.
-
The TRUNCATE TABLE statement is executed using the JDBC driver
-
There is a Statement object or a PreparedStatement object performing retrieval from the table targeted by the TRUNCATE TABLE statement within the same connection
-
(5) Examples
- Example
-
Delete all rows in the sales history table (SALESLIST).
TRUNCATE TABLE "SALESLIST"