NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-63
Examples—DROP
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.
•
Dropping nonaudited objects
You cannot drop a nonaudited object within a user-defined TMF transaction.
•
Dropping a program
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.)
You cannot drop a SQL-program Guardian file within a user-defined transaction.
(You can use the OSS unlink command to delete an SQL-program OSS file within a
user-defined transaction, but the operation is not performed as part of the
transaction.)
•
Dropping objects managed by ServerWare SMF
If ServerWare SMF is installed on your node, the object to drop must have either a
virtual or direct name.
This restriction does not apply to DROP CONSTRAINT.
Examples—DROP
•
The following example drops an index on the PARTS table and then drops the table
and a related program:
DROP INDEX $VOL1.SALES.XPARTDES;
DROP TABLE $VOL1.SALES.PARTS;
DROP PROGRAM $VOL3.SUBVOL3.PROGA;
WARNING. It is legal to DROP an open table, 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 prior to the DROP TABLE.