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

Adding, Altering, and Dropping SQL/MX Database

Objects

HP NonStop SQL/MX Installation and Management Guide—523723-004

9-29

Dropping Catalogs

To drop a catalog, use the DROP CATALOG statement.

DROP CATALOG automatically removes the SQL/MX user metadata tables, system

metadata tables, and definition schema tables associated with the catalog and all

entries in the system catalog associated with the catalog. All metadata tables for the

catalog must be accessible at the time DROP CATALOG executes.

Before you can drop a catalog, it must be empty. Any user can drop any empty catalog

visible on the local node. No user can drop a nonempty catalog, even if the catalog

contains only empty schemas.

For more information, see the SQL/MX Reference Manual.

Steps for Dropping a Catalog

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 catalog you wish to drop.

3. If necessary, use the DROP SCHEMA statement to drop any schemas from the

catalog. (See Dropping Schemas on page 9-33.)

4. Enter the DROP CATALOG statement.

For more information and examples of dropping catalogs, see the SQL/MX Reference

Manual.

Dropping Columns

You cannot drop a column from a table, view or index directly. To drop a column

indirectly, you must create a new table, view or index identical to the old one, but

without the offending column, and then copy the contents of the remaining columns

from the old table to the new one.

For more information, see the SQL/MX Reference Manual.

Steps for Dropping a Column From a Table

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 a name for the new table.

3. Query the system metadata to ensure the new table name is unique among

existing table, view, SQL/MP alias and SPJ names in the schema.

Note. The CASCADE option causes all objects within the specified schema to be dropped

as well as the schema itself.