SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—544536-007
10-45
Using PURGEDATA to Delete Data From Tables
Using PURGEDATA to Delete Data From Tables
PURGEDATA is a syntax-based utility you can execute from MXCI to delete data from
a table and its related index or from the specified partitions of a table that has no index.
For more information about the PURGEDATA utility, see the SQL/MX Reference
Manual.
Guidelines for Using PURGEDATA
•
The PURGEDATA requester must own the schema where the table resides, have
SELECT and DELETE privileges on the table being purged, or be the super ID
user. An error is returned if an access violation occurs.
•
The table name you specify with PURGEDATA can exist in a catalog defined on a
remote node and be referenced by the current PURGEDATA operation if the
remote node is visible to the local node.
•
If PURGEDATA fails because of a process, CPU, or system failure, use the
RECOVER command to resume the operation. RECOVER returns an error if it
cannot resume the operation.
•
PURGEDATA records operation progress steps in the DDL_LOCKS metadata
table. Users can query this table to determine the PURGEDATA operation’s
progress. For more information about PURGEDATA operation steps and their
progress states, see the SQL/MX Reference Manual. For more information about
querying for DDL_LOCK information, see Section 8, Querying SQL/MX Metadata.
•
The PURGEDATA operation temporarily invalidates, by marking as corrupt, tables
and indexes, which prevents concurrent access by other users until the data is
purged.
•
After purging the data, the PURGEDATA operation validates the table and indexes
by turning off the corrupt flag so that they are again accessible to other users.
•
The PURGEDATA operation does not automatically alter the table’s statistics. After
purging the data and after you (or any programs) have added data to the table, run
an UPDATE STATISTICS statement to record current statistics for the table in the
catalog. If the statistics are incorrect, the SQL compiler might not select the best
access path for performance.
•
You cannot use the PURGEDATA command on a view or in an SQL program.
PURGEDATA returns errors if:
•
You specify a list of partition for a hash-partitioned table. For hash-partitioned
objects, you must delete the entire table.
•
You attempt PURGEDATA on SQL/MX metadata tables, including histograms,
system defaults, and the MXCS metadata tables.