PURGE TABLE statement (Delete all rows)
Function
The PURGE TABLE statement deletes all rows in a specified base table.
Privileges
A user who has the DELETE privilege for a table can delete rows from that table.
Format
PURGE TABLE[[RD-node-name.]authorization-identifier.]table-identifier
[{WITH ROLLBACK|NO WAIT}]
Operands
- [[RD-node-name.]authorization-identifier.]table-identifier
- RD-node-name
- Specifies the RD node name of the RD node to be accessed.
- authorization-identifier
- Specifies the authorization identifier of the user who owns the table.
- MASTER cannot be specified as an authorization identifier. For an explanation of omitting the authorization identifier, see 1.1.8 Qualifying a name.
- table-identifier
- Specifies the name of the base table from which all rows are to be deleted.
- [{WITH ROLLBACK|NO WAIT}]
If neither the WITH ROLLBACK option nor the NO WAIT option is specified and the table from which all rows are to be deleted is being used by a transaction issued by another user, the system executes the PURGE TABLE statement after that transaction has terminated.
When USE is specified for the pd_check_pending operand in the system definition and a referencing table that is referencing a table from which all rows are to be deleted is being used by another user transaction, the referencing table is set to check pending status once the transaction terminates.
- WITH ROLLBACK
- Specifies that if the table from which all rows are to be deleted is being used by another user, the system is to cancel and invalidate the current transaction.
- When USE is specified for the pd_check_pending operand in the system definition and a referencing table that is referencing a table from which all rows are to be deleted is being used by another user transaction, the transaction is cancelled and invalidated.
- NO WAIT
- Specifies that if the table from which all rows are to be deleted is being used by another user, HiRDB is to invalidate the current SQL without canceling the transaction.
- When USE is specified for the pd_check_pending operand in the system definition and a referencing table that is referencing a table from which all rows are to be deleted is being used by another user transaction, the SQL statement is invalidated without canceling the transaction.
Common rules
- When executed normally, the PURGE TABLE statement is committed as soon as its processing has been completed.
- If the user LOB RDAREA that stores a LOB column or LOB attribute is in the frozen update status, the PURGE TABLE statement cannot be executed on a table containing the LOB column or the LOB attribute (an attempt to execute this statement causes an already frozen error).
- The PURGE TABLE statement cannot be executed on falsification prevented tables.
- If the PURGE TABLE statement is executed on a shared table, a locking equivalent to the LOCK statement with an EXCLUSIVE specification is applied to the shared table. For HiRDB/Parallel Server, the locking is applied on all back-end servers.
Rules related to the check pending status
- When USE is specified for the pd_check_pending operand in the system definition and the operation-target table is a referenced table, the referencing table that references the operation-target table is set to check pending status.
- If the operation-target table is in check pending status, the status is released. However, if either of the conditions listed below is satisfied, the check pending status in the dictionary tables is not released. In such a case, use the integrity check utility to release the check pending status in the dictionary tables.
- NOUSE is specified for the pd_check_pending operand in the system definition.
- The inner replica facility is being used.
Notes
- The PURGE TABLE statement cannot be specified for a view table.
- The PURGE TABLE statement cannot be specified for a foreign table.
- The PURGE TABLE statement cannot be specified from an X/Open-compliant UAP running under OLTP. When calling a procedure from a UAP running under OLTP, you cannot execute procedures using the PURGE TABLE statement.
- When executing a procedure defined on a distributed RD node for remote database access using the distributed database facility, you cannot execute procedures using the PURGE TABLE statement.
- The PURGE TABLE statement cannot be executed during trigger action.
Example
Delete all rows from stock table STOCK:
PURGE TABLE STOCK