SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-25
Example—CLEANUP

Purge a catalog's name entry in the system catalog's CATALOGS table when a file-
system error occurs during access to the system CATALOGS table. When
CLEANUP is used to purge a catalog, CLEANUP attempts to remove the name
entry for the catalog from the system CATALOGS table. Any failure in this
operation (caused by an event such as a system catalog file being flagged as
CRASHOPEN) does not affect the outcome of the current CLEANUP operation.
CLEANUP reports a warning to the user if the catalog's name entry cannot be
removed because of a file-system error. In this case, you must use a licensed
SQLCI2 process to remove the catalog name entry from the system CATALOGS
table.

Purge the catalog information and the file label of any protection view whose
underlying table or table partition is missing or inconsistent.
Normally, a protection view is purged when the underlying table is removed by
CLEANUP; however, misuse of the GOAWAY utility on a base table or another
software problem could cause an orphan protection view. In this situation, if the
entire catalog is not being removed, you must use a licensed SQLCI2 process to
remove the catalog description associated with the protection view and use
GOAWAY to remove the file label of the view.

Purge a dependent partitioned protection view when the catalog tables describing
the view are missing. This situation occurs when a table with a protection view is
partitioned across multiple catalogs, and one of the catalogs in which a secondary
partition is registered is missing when CLEANUP is requested to purge the
partitioned table. In this case, CLEANUP cannot determine the dependents of the
table partition whose catalog is missing because the USAGES table is absent; this
situation can cause an orphaned protection view. To remove the orphaned
protection view, you must use the GOAWAY utility.

Purge catalog entries for SQL objects or determine dependent objects when the
VERSIONS table is missing or corrupted. The catalog tables themselves, however,
can still be purged in this case.

Purge catalog information, in certain unusual circumstances, for a partition of a
partitioned index when the file label of the partition is missing. CLEANUP, however,
successfully removes the file labels and catalog descriptions of all other partitions
of the index.
Example—CLEANUP
Suppose that the subvolume $VOL1.PERSNL contains three tables: DEPT, JOB, and
EMPLOYEE. The tables are described in the catalog $VOL2.CAT. If $VOL2 is
removed, the tables cannot be deleted using DROP or PURGE because the catalog in
which they are described is not accessible. You can, however, remove the tables by
entering:
>> CLEANUP ($VOL1.PERSNL.DEPT, $VOL1.PERSNL.JOB,
$VOL1.PERSNL.EMPLOYEE);