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

• A certain subset of a remote table’s data is accessed more frequently at the local node than
from a remote node, partitioning the table so that the frequently accessed portion of the data
resides on the local node can increase local performance.
• Queries are processed in parallel, partitioning a table or index is often required. Partitioning
is necessary, for example, for parallel execution of a SELECT statement on a single table. Even
for join queries, which do not require partitioning of the objects involved, parallel processing
operates best when a table or index is partitioned.
Use the Measure product to obtain statistics concerning disk message levels, queuing, and other
measurements on various volumes or file partitions to identify the levels of use.
For more information, see “Performance Benefits of Partitioning” (page 83).
Steps for Adding a 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 or index to which you want to add the partition.
3. For range partitioned tables or indexes, determine the location and starting key of the new
partition. For hash partitioned tables or indexes, determine the location of the new partition.
4. Ensure ample disk space is available for the new partition. For more information, see the
SQL/MX Reference Manual.
5. 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.
6. Use the MODIFY utility to add the partition. (See “Using MODIFY to Manage Table and Index
Partitions” (page 182).)
7. 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.
8. Make new TMF online dumps of all partitions of the table or index.
For more information about redistributing rows across partitions, see “Reorganizing SQL/MX
Tables and Maintaining Data” (page 176).
Example of Adding a Hash Partition to an SQL/MX Table
For this example, suppose that the table ORDERSH has been created in this way:
>> CREATE TABLE cat3.sch3.ordersh
+> (location char(16) not null not droppable,
+> ordernumber integer unsigned not null not droppable,
+> ordertime timestamp,
+> primary key (location, ordernumber) not droppable)
+> location $DATA01
+> hash partition(
+> add location $DATA02,
+> add location $DATA03);
--- SQL operation complete.
The table ORDERSH is partitioned three ways. When business orders increase, you decide to
rebalance the orders data over four disks. Use this MODIFY TABLE command to add the fourth
partition:
Adding Objects to an SQL/MX Database 157