NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-21
Considerations—CLEANUP
After deleting each object, CLEANUP displays a message indicating the object and
catalog entry that was deleted.
•
Transactions, breaks, and failures
You cannot use CLEANUP within a user-defined TMF transaction.
SQL automatically starts a transaction for each catalog description and file label
purged with CLEANUP, so only the deletion of the last SQL object (or partition) is
undone if CLEANUP fails before the deletion is committed.
If CLEANUP is interrupted by a break request, all changes that have completed at
the time of the break remain in effect; any change in progress is rolled back. The
messages displayed by CLEANUP before the break is received indicate which
objects have been fully deleted. In the rare case when the break arrives immediately
after a deletion and just before a message is issued, CLEANUP actually might have
deleted one additional object.
•
CLEANUP operations
SQL objects are described in the SQL data dictionary, which is composed of SQL
catalogs and file labels. Misuse of various system management utilities can corrupt
the data dictionary. This damage can make it impossible to access the objects,
preventing you from removing them with the customary DROP command or
PURGE utility. In such cases, you can probably eliminate the objects by using
CLEANUP.
When purging an object, CLEANUP attempts to purge the file containing the object
and the description of the object in the catalog.
For a table, index, or view specified in the fileset list, CLEANUP attempts to purge
dependent objects and to mark dependent programs as invalid. This operation is
consistent with those of the DROP and PURGE commands; however, unlike DROP
and PURGE, CLEANUP deletes each object independently of the other objects.
For an SQL program in a Guardian file specified in the fileset list, CLEANUP
purges the program. CLEANUP cannot operate on an SQL program in an OSS file.
For partitioned objects, CLEANUP processes each partition as a separate object,
purging each one independently of the other partitions. You cannot request a
CLEANUP operation for a single partition.
CLEANUP also processes each dependent object independently. Under unusual
circumstances it is possible to still have pieces of the dependent objects, partitions,
or indexes remaining (after using CLEANUP) that refer to a deleted table.
For indexes, the CLEANUP utility does not update the object version of any
dependent object of the purged index. The recorded object versions of some objects,
therefore, might not be the same as the actual object versions.
For collations, CLEANUP does not purge a collation or its description if the
collation has dependent objects. If qualified-fileset-list includes both a
collation and all its dependent objects, SQL purges the dependent objects first, then
the collation and its description. If qualified-fileset-list includes an