SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

ManualsBrandsHP ManualsServerHP Integrity NonStop H-Series

171

172

173

174

175

176

177

178

179

180

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

3. Query the system metadata to ensure the temporary 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 226) 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 of the new table, drop the old table and rename the new table to have the

name of 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, updates or deletes will not have to satisfy the constraint. The ALTER TABLE DROP

CONSTRAINT statement drops only the constraint definition from the table and does not affect the

data in the table.

Dropping a constraint on a table invalidates the programs that depend on the 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.

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

Steps for Dropping a Constraint

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 for which you want to drop the constraint.

3. Use the DISPLAY USE OF command to identify which user modules are associated with this