SQL/MP Installation and Management Guide

Reorganizing Tables and Maintaining Data
HP NonStop SQL/MP Installation and Management Guide—523353-004
8-19
Purging Data From SQL Tables
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 include the PURGEDATA command within a user-defined TMF
transaction.
•
You cannot use the PURGEDATA command on an SQL program, view, catalog
table, or index.
•
With the PARTONLY option of the PURGEDATA command, you can purge data
from a single partition of a partitioned table without indexes. (If you omit the
PARTONLY option but specify a primary or secondary partition in the command,
the data is removed from all partitions and indexes.)
•
When using the PARTONLY option of the PURGEDATA command for a relative or
an entry-sequenced table, you can only purge data from the last partition. For a
key-sequenced table, however, you can purge data from any partition. The
PARTONLY option applies only to tables with no dependent indexes.
To use PURGEDATA, follow these steps:
1. Start an SQLCI session.
2. Enter a LOG command to initiate a log file for the statements and commands
entered in this session. Keep the log for your records.
3. Prevent the use of the table.
4. Enter the PURGEDATA command. To purge data from an individual partition of a
table, use the PARTONLY option of this command.
When you perform a PURGEDATA operation on an SQL audited table, the end-of-file
marker is moved backward in the table, and the TMF audit record generated contains
the before-images and after-images of the altered file label; however, before-images of
the data in the table are not generated.
Under these circumstances, no data exists to enable you to roll back the PURGEDATA
operation to the previous state. However, if you make periodic online dumps of the
table and note the times at which you issue PURGEDATA commands, the purged data
is retained in audit images that can be recovered. For this recovery, use one of the
TMF interfaces (such as TMFCOM) to issue the RECOVER FILES command with the
TIME attribute set for file recovery to the time before the data was purged.