SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-31
Dropping Indexes
•
Dropping the view also drops the view definition from the data dictionary. To
re-create the environment, you must recover these definitions from backup tapes
or OBEY recovery files.
To drop a view, follow these steps:
1. Start an SQLCI session. Enter a LOG command to initiate a log file for the
statements and commands entered in this session. Keep the log for your records.
2. Determine all the objects and programs dependent on the view by using the
DISPLAY USE OF command.
3. Prevent all access to the view and its dependent objects.
4. Stop execution of any dependent programs.
5. Enter the DROP VIEW statement to the SQLCI session.
6. For an audited view, make a new TMF online dump so that file recovery does not
replace the view.
7. If you want to use the dependent programs again, revise program source files to
delete references to the dropped view and recompile the programs.
Dropping Indexes
To drop an index, use the DROP INDEX statement. This statement purges the physical
file that contains the index and eliminates the access path to the underlying table.
Dropping an index invalidates programs that depend on the underlying table. You
should include steps to explicitly SQL compile the dependent programs to avoid
automatic recompilation and to return the application to a valid state.
To drop an index, follow these steps:
1. Start an SQLCI session. Enter a LOG command to initiate a log file for the
statements and commands entered in this session. Keep the log for your records.
2. Determine the name of the table for which you want to drop the index.
3. Determine which programs depend on the table by using the DISPLAY USE OF
command. These programs will be invalidated.
4. Optionally, prevent the use of the table for the duration of the DROP INDEX
operation to eliminate conflicts in access to the table; this operation requires
exclusive use of the table.
5. Enter the DROP INDEX statement.
6. SQL compile the invalidated programs.
7. Restart use of the table if you stopped its use.
If you plan to use the TMF subsystem for recovering an audited SQL index, see
Recovering Purged SQL Tables on page 11-14 before proceeding.