SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-26
CLEARONPURGE File Attribute
If $VOL1 is removed (instead of $VOL2, as in the previous example), the catalog
descriptions cannot be removed using DROP or PURGE because the tables are
not accessible. You can delete the catalog descriptions by entering:
>> CLEANUP ($VOL1.PERSNL.DEPT, $VOL1.PERSNL.JOB,
$VOL1.PERSNL.EMPLOYEE) FROM CATALOG $VOL2.CAT;
If the DEPT, JOB, and EMPLOYEE tables are the only SQL objects on $VOL1, you
can accomplish the same operation:
>> CLEANUP $VOL1.PERSNL.* FROM CATALOG $VOL2.CAT;
CLEARONPURGE File Attribute
CLEARONPURGE is a Guardian file attribute that controls erasure of data from the
disk when a table, index, catalog, or program is purged or dropped. CLEARONPURGE
applies to key-sequenced, relative, and entry-sequenced tables and to indexes.
NO CLEARONPURGE is the default for tables, for catalogs, and for programs that are
explicitly SQL-compiled and stored in Guardian files.
The index default is the table value at index creation.
Considerations—CLEARONPURGE

When you drop or purge an object with NO CLEARONPURGE, the system
deallocates disk space but does not physically destroy the data in that disk space.
This implementation improves performance by reducing writes to the disk, but
when the disk space is allocated to a new file, other users might be able to read
data left by the object that used the space previously.
CLEARONPURGE increases security for sensitive data or programs by causing
the system to overwrite deallocated disk space.

If you drop or purge a file with the CLEARONPURGE attribute from within a TMF
transaction, the data is not physically erased from the disk until after the
transaction commits.
CLOSE Statement
CLOSE is a DML and a dynamic SQL statement that closes a cursor in a host
program. After the CLOSE executes, the result table for the cursor (the output that
results from the execution of the SELECT for the cursor) no longer exists.
{ CLEARONPURGE | NO CLEARONPURGE }
CLOSE { cursor }
{ :cursor-variable }