SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—540440-003
5-78
Considerations for PURGEDATA
Considerations for PURGEDATA
•
You must have ALL privileges on the table.
•
An error is returned if you specify a list-of-partitions for a hash-partitioned
table. For hash-partitioned objects, data must be purged from the entire table.
•
table-name can exist on a remote node and be referenced by the current
PURGEDATA operation if the remote node is visible to the local node.
•
Errors are returned if table-name cannot be accessed or if a resource or file
system problem causes the delete to fail.
•
PURGEDATA returns a syntax error if the partition-map syntax does not
conform.
•
If PURGEDATA fails in response to a process, CPU, or system error, you must run
the RECOVER utility to recover the operation. If the PURGEDATA operation
cannot be canceled, RECOVER returns an error. See Checking File Locks on
page 5-3 for details.
•
PURGEDATA records operation progress steps in the DDL_LOCKS metadata
table. Users can query this table to determine the PURGEDATA operation’s
progress:
•
PURGEDATA returns an error if a user transaction exists.
•
PURGEDATA returns an error if you attempt a PURGEDATA operation on an
SQL/MX metadata table (histogram, system defaults, or MXCS metadata tables).
•
PURGEDATA returns an error if another table references table-name through a
trigger or referential integrity constraint.
•
PURGEDATA sets the corrupt bit while processing. If PURGEDATA fails before it
completes, the table and its dependent indexes will still be corrupt and you must
run RECOVER must be run with the RESUME option to complete the operation
and remove the data. Files system error 59 is returned when it tries to open a table
whose corrupt bit is set.
PURGEDATA
Operation
Step Step Progress Status
Step 1 DDL lock has been created.
Step 2 PURGEDATA has passed verification tests.
Step 3 Affected partitions have been marked corrupt.
Step 4 Partition $volume.zsdnnnnn.nnnnnn00 has been purged.*
Step 5 PURGEDATA operation has deleted data from all requested partitions.
Step 6 Affected partitions are now available.
Step 7 DDL lock has been removed.
*
After this step, when any partition has been purged, a rollback using the RECOVER utility is not possible.