SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—544536-007
9-35
Dropping SQL/MX Tables
Guidelines for Dropping an SQL/MX Table and its Data
•
If you plan to use the TMF subsystem to recover an SQL/MX table, see
Recovering Tables on page 12-8.
•
To have the authority to drop a table, you must have all the security and authority
required to drop or invalidate all dependent objects, including access to all the
catalogs describing all the dependent objects.
•
You can use the DROP TABLE statement to drop a table with partitions, but not to
drop individual partitions within a table. For information about using the MODIFY
utility to drop the individual partitions of a table, see Dropping Partitions for
SQL/MX Tables and Indexes on page 9-31
•
When you drop a table, the operation invalidates the programs that depend on that
table. To avoid expensive automatic recompilations of these programs, you should
change them as necessary and recompile them explicitly, as in Steps for Dropping
an SQL/MX Table and its Data on page 9-35.
•
A table that has an active DDL lock (one for which the process that created it still
exists) cannot be dropped even if you specify CASCADE. An active DDL lock will
be released when the utility locking the file completes.
•
If you specify RESTRICT and table is referenced by a view, a trigger, or a
referential constraint of another table, or if the table has an active DDL lock, the
specified table cannot be dropped.
•
If you specify CASCADE, the table and all of its views, triggers, referential
constraints, and inactive DDL locks are dropped.
For more information about the DROP TABLE statement, see the SQL/MX Reference
Manual.
Steps for Dropping an SQL/MX Table and its Data
1. Start an MXCI session. Enter a LOG command to initiate a log file for statements
and commands entered in this session. Keep the log for your records.
2. Determine the name of the table you wish to drop.
3. Use the DISPLAY USE OF command to identify which user modules are
associated with this object. See the similarity check criteria in the SQL/MX
Programming Manual for C and COBOL to determine if your changes are likely to
cause similarly check to fail and force automatic recompilation. If they will, you
should SQL compile these modules after making the changes to avoid expensive
automatic recompilations at run time. SQL applications that are running while you
make these changes will still undergo automatic recompilation.
For information about explicit and automatic recompilation, see the SQL/MX
Programming Manual for C and COBOL. For information about using DISPLAY
USE OF, see Checking Module Dependencies with DISPLAY USE OF
on
page 11-19 and the SQL/MX Reference Manual.