SQL/MX 2.x Installation and Management Guide (H06.04+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—540436-001
10-38
Examples of Using PURGEDATA to Delete Table
Data
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.
•
Another table references the named table through a trigger or referential integrity
constraint.
•
You attempt to perform PURGEDATA within a user-defined transaction. There is
no notion of a transaction existing for a table.
Examples of Using PURGEDATA to Delete Table Data
This example purges the data in the specified table. If the table has indexes, the
indexes are also purged.
PURGEDATA mycat.myschema.mytable;
This example purges the data in the specified partition, which has a Guardian name:
PURGEDATA mycat.myschema.mytable
WHERE LOCATION $DATA1.ZSDA09TO.QZ780000;
This example purges data from all partitions of the table:
PURGEDATA mycat.myschema.mytable
WHERE KEY = FIRST PARTITION THRU LAST PARTITION;