SQL/MP Installation and Management Guide

Reorganizing Tables and Maintaining Data
HP NonStop SQL/MP Installation and Management Guide—523353-004
8-18
Purging Data From SQL Tables
The SORTED option indicates that the input data has already been sorted; the append
operation does not need to perform further sorting. The ALLOWERRORS option
ensures that append operation will proceed even if an input record contains data that
could not be converted into a target column.
> SQLCI / CPU 0, OUT $S.#LOG33, NOWAIT / &
>& APPEND $VOL1.WEEK25.PART33, $VOL33.TARGET.HIST, &
>& RECOVERYFILE $VOL2.RECOVER.PART33 &
>& PARTONLY, SORTED, ALLOWERRORS ON ; &
>& EXIT ;
> |
> SQLCI / CPU 1, OUT $S.#LOG34, NOWAIT / &
>& APPEND $VOL1.WEEK25.PART34, $VOL34.TARGET.HIST, &
>& RECOVERYFILE $VOL2.RECOVER.PART34 &
>& PARTONLY, SORTED, ALLOWERRORS ON ; &
>& EXIT ;
> |
> |
> |
> SQLCI / CPU 15, OUT $S.#LOG48, NOWAIT / &
>& APPEND $VOL4.WEEK25.PART48, $VOL48.TARGET.HIST, &
>& RECOVERYFILE $VOL2.RECOVER.PART48 &
>& PARTONLY, SORTED, ALLOWERRORS ON ; &
>& EXIT ;
The example issues 16 similar APPEND commands, one for each target partition. The
append operation executes in parallel against the 16 partitions.
The RECOVERYFILE parameter specifies a file that stores information needed to
restore the target partition to its initial state if a process failure or processor failure
interrupts the APPEND operation. For more information about this parameter, and
about the syntax and use of APPEND options, see the SQL/MP Reference Manual.
Purging Data From SQL Tables
If you want to purge only the data from a nonaudited or audited SQL table, use the
PURGEDATA command. This command clears only the data, leaving the catalog
description of the table valid. These guidelines apply:
•
The PURGEDATA operation temporarily invalidates the table and indexes to
prevent concurrent access by other users until the data is purged.
If an error occurs after the table is marked invalid but before the PURGEDATA
operation begins, the table is revalidated, and the data remains unchanged. If an
error occurs during the PURGEDATA operation and the operation fails to complete,
PURGEDATA leaves the table marked as corrupt. To recover, resolve the problem
that caused the first attempt to fail, then reissue the PURGEDATA command.
•
After purging the data, the PURGEDATA operation validates the table and indexes
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