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

4. Enter the ALTER TABLE DROP CONSTRAINT statement.
5. 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.
For more information, see the SQL/MX Reference Manual.
Dropping SQL/MX Indexes
To drop an index, use the DROP INDEX statement.
This statement purges the physical files that contain the index and eliminates the access path to
the underlying table. The RESTRICT option, by default, will not allow an index to be dropped if it
is being used to validate a constraint. The CASCADE option, if specified, will drop both the index
and any constraints using the index for validation.
Dropping an index invalidates programs that depend on index access path. When you drop an
index, you should explicitly SQL compile the dependent programs to return the application to a
valid state and to avoid expensive automatic recompilations.
For more information, see the SQL/MX Reference Manual.
Steps for Dropping an Index
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 index.
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 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.
4. Enter the DROP INDEX statement.
5. 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.
If you plan to use the TMF subsystem for recovering an index, see “Recovering Views and Indexes”
(page 251).
For more information, see the SQL/MX Reference Manual.
Dropping Partitions for SQL/MX Tables and Indexes
Use the MODIFY utility to drop a range partition or hash partition in a table or an index. To drop
only the data within a partition and leave partition itself intact, use the PURGEDATA utility. For
more information, see the “Using MODIFY to Manage Table and Index Partitions” (page 184) and
“Using PURGEDATA to Delete Data From Tables” (page 210).
Determining When to Drop a Partition
When all information in a partition becomes obsolete, or when a database design deficiency
leaves a partition continually empty, a reference to a table or index defined across this partition
request results in a correspondingly longer access time to the table or index. In such circumstances,
you might want to drop this partition while leaving the others defined for the object intact.
172 Adding, Altering, and Dropping SQL/MX Database Objects