SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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” (page 171).)
NOTE: The CASCADE option causes all objects within the specified schema to be dropped
as well as the schema itself.
4. Enter the DROP CATALOG statement.
For more information, see the SQL/MX Reference Manual.
Dropping Columns from an SQL/MX Table
You cannot drop a column from an SQL/MX 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 an SQL/MX 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.
4. 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 more information about explicit and automatic recompilation, see the SQL/MX Programming
Manual for C and COBOL. For more information about using DISPLAY USE OF, see “Checking
Module Dependencies with DISPLAY USE OF” (page 223) and the SQL/MX Reference Manual.
5. Enter the CREATE TABLE statement to create a new table definition that does not contain the
column you want to drop.
6. After creating the new table, load the old table’s data into the new table with INSERT/SELECT
statements.
7. After the loading the new table, drop the old table.
8. Revise the application source code as needed to reflect your changes to the database. Process
and compile the updated source file. For more information, see the SQL/MX Programming
Manual for C and COBOL.
Dropping Constraints
To drop a constraint, use the ALTER TABLE DROP CONSTRAINT statement.
Dropping constraints on the database is similar to making a program change. Any future data
inserts or updates will not have to satisfy the constraint. The ALTER TABLE DROP CONSTRAINT
168 Adding, Altering, and Dropping SQL/MX Database Objects