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

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.
6. Enter the CREATE INDEX statement.
NOTE: If the base table is populated, specify the NO POPULATE option for this statement,
and then run POPULATE INDEX utility. For more information, see the SQL/MX Reference
Manual.
7. Enter an UPDATE STATISTICS statement to update the statistical information stored in the
catalog and get statistics on the new index.
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.
9. Make a new TMF online dump containing the new index.
For more information, see “Precreating Indexes or Managing Constraint-Created Indexes” (page 36)
and “Creating Indexes for SQL/MX Tables” (page 95).
Adding Partitions to SQL/MX Tables and Indexes
If an index or a table is stored by a user-specified primary key or a key column list, you can add
partitions to the index or table by using the MODIFY utility. MODIFY is a syntax-based utility that
can be executed through MXCI, and enables database administrators to perform partition operations
on range and hash partitions of SQL/MX tables and indexes. Depending on the type of operation
you are performing, MODIFY can be run as an online or offline operation.
CAUTION: Except for the operation to drop a range partition, partition operations are done in
multiple transactions. If the partition operation fails, you might need to use the RECOVER utility to
cancel or resume the failed partition operation.
For more information about creating and using table and index partitions, see:
• “Planning Table and Index Partitioning” (page 33)
• “Range Partitioning and Hash Partitioning” (page 33)
• “Creating and Managing Partitions for SQL/MX Tables” (page 82)
• “Restrictions on Creating and Placing Partitions” (page 83)
• SQL/MX Reference Manual
Evaluating the Benefit of a New Partition
Partitioning can provide significant benefits to a wide variety of applications by improving
manageability, performance, and availability. It is not unusual for partitioning to improve the
performance of certain queries or maintenance operations by an order of magnitude.
Partitioning might increase performance if:
• Disk accesses are queued in the disk process, partitioning the table across multiple volumes
might increase performance.
• A partition of a table or index is full, the partition can be split into two partitions.
156 Adding, Altering, and Dropping SQL/MX Database Objects