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

SQL/MX tables can be reorganized online. Online reorganization involves:
• “Reorganizing Table Files With FUP RELOAD” (page 177)
• “Determining the Status of a Reorganization With FUP STATUS” (page 178)
• “Suspending a Reorganization Operation” (page 178)
After you use FUP RELOAD to defragment a table, update the physical statistics using UPDATE
STATISTICS to update the metadata information. This allows the optimizer to generate better query
plans.
Reorganizing Table Files With FUP RELOAD
The FUP RELOAD command lets you reorganize a key-sequenced file while the file remains available
for use by the application. The FUP RELOAD operation physically restructures the file to improve
access performance and space usage.
Before performing a FUP RELOAD operation, consider these issues:
• During the operation, performance can be degraded if the data blocks have become physically
disorganized. Disorganization can occur as a result of INSERT, UPDATE, or DELETE operations
performed over a period of time. You can, however, control the amount of degradation by
using the command’s RATE option. The higher the rate, the faster the reload occurs, but the
more performance degrades. Conversely, the lower the rate, the slower the reload occurs,
but the less performance degrades. The default value for RATE is 100 percent.
• You can either specify an ANSI name or a Guardian file name when you perform a FUP
RELOAD on an SQL/MX table or index. FUP RELOAD only supports the use of ANSI names
for individual table or index partitions. If you provide an ANSI name for a table or index
partition, FUP converts it to its corresponding Guardian file name and performs RELOAD on
the file as if the Guardian name had been entered directly. If you provide a Guardian file
name, you must first identify the Guardian files (objects) that are associated with that table or
index. For instructions on obtaining this information, see “Displaying Users With Catalog and
Schema Creation Rights” (page 124) or use the SHOWDDL command. You then run FUP
RELOAD on each of these Guardian files (objects) for the SQL/MX table or index. FUP RELOAD
can be run in parallel or sequential order.
NOTE: If you do use ANSI names with FUP RELOAD, the FUP command line cannot exceed
132 characters. Do not use ANSI names that span more than one input line and require an
embedded “&” line continuation character. One alternative would be to accept an input file
and read the ANSI names out of that file, limiting it to names of 255 characters or less because
EDIT files have that limit.
• The FUP RELOAD command reorganizes either a table or an index independent of each other.
NOTE: Do not attempt to perform a FUP RELOAD operation on a partition while other partition
(DDL) operations are in progress on its table or index.
• You can FUP RELOAD a nonpartitioned table or index. You can also FUP RELOAD one, some,
or all partitions of a partitioned table or index.
• When the FUP RELOAD command is issued, FUP uses the ORSERV program to initiate a
background process to perform the operations requested by the command. After the process
is initiated, FUP displays the message RELOAD STARTED and either returns a prompt or
terminates (depending upon whether FUP was initiated interactively or noninteractively).
Using FUP RELOAD to Reorganize Tables 177