SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
D-65
Considerations—DROP
table (including all partitions) and the catalogs of all SQL object program files that
use the table are accessible.

You cannot drop a table or view unless all related indexes and views (including all
partitions) are accessible, and unless the catalogs of all SQL object program files
that use the table or view are accessible. You cannot drop a protection view unless
the underlying table (including all partitions and associated indexes) is accessible.
To make sure the object is accessible, perform a LOCK TABLE operation before
issuing the DROP TABLE request.
Dropping a table purges the physical file for the table and drops all dependent
indexes, constraints, partitions, and views. It also invalidates all SQL object
program files that refer to the table. If the dropping process does not have authority
to purge a shorthand view, SQL invalidates the shorthand view.
Dropping a view automatically drops all dependent views, and invalidates all SQL
object program files that refer to the views. If the dropping process does not have
authority to purge a shorthand view, SQL invalidates the shorthand view.
Invalidated shorthand views are unusable and should be dropped by a user with
appropriate security.
Invalidated program files might be usable, but you should ensure that they are
explicitly SQL-compiled to avoid automatic recompilation each time the program
runs.

You cannot drop a nonaudited object within a user-defined TMF transaction.

Dropping a program purges the physical program file.
To drop a program, you must have authority to read and write to the USAGES table
of each catalog that contains objects referenced by the program.
You cannot use DROP to delete an SQL-program OSS file. Use the OSS rm
command or the OSS unlink() function instead. (These commands remove the use
of one pathname for a file. OSS removes the physical SQL object program file
when the last pathname is removed; SQL deletes any references to the program in
the SQL catalog at that time.)
Caution. Using the DROP statement to delete a table is permitted if you have PURGE
authority, there are no locks outstanding on the table, and if you are beyond the
NOPURGEUNTIL date.
To adequately protect important tables, use the NOPURGEUNTIL attribute, specifying a date
well into the future, or change PURGE authority to “-” (SUPER only). Either method requires an
ALTER TABLE before the DROP TABLE.
Caution. Using the DROP statement to delete a table is permitted if you have PURGE
authority, there are no locks outstanding on the table, and if you are beyond the
NOPURGEUNTIL date.
To adequately protect important tables, use the NOPURGEUNTIL attribute, specifying a date
well into the future, or change PURGE authority to “-” (SUPER only). Either method requires an
ALTER TABLE before the DROP TABLE.