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

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 248).
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 182) and
“Using PURGEDATA to Delete Data From Tables” (page 207).
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.
Guidelines for Dropping Partitions
You can drop table and index partitions within these guidelines:
• The partition for a range-partitioned object must be empty.
• All partitions must be available when you use the MODIFY utility on an SQL/MX table or
index.
• If you plan to use the TMF subsystem for recovering a table or index, see “Recovering Database
Objects” (page 236) before proceeding.
For more information, see the SQL/MX Reference Manual.
Steps for Dropping a Partition
See “Using MODIFY to Manage Table and Index Partitions” (page 182).
Example of Dropping a Partition
The following example drops an empty partition of a range-partitioned table:
>>MODIFY TABLE CAT.SALES.CUSTOMER
+> DROP PARTITION
+> WHERE LOCATION $VOL5;
--- SQL operation complete.
Deleting Data from Partitions
To drop only the data from a table and associated indexes, use the PURGEDATA utility.
NOTE: You cannot use PURGEDATA to drop the data from an index partition. You can only use
PURGEDATA to drop data from partitions of tables that have no indexes, and you cannot drop
indexes separately from tables.
To drop the data from a table partition:
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 from which you want to drop the partition data.
3. Use the PURGEDATA utility to drop the data from the partition.
170 Adding, Altering, and Dropping SQL/MX Database Objects