SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—540440-003
5-61
Considerations for MODIFY
MODIFY and Table Reloading
Some of MODIFY’s options start a FUP RELOAD process that runs in the background.
Until this process completes, you cannot do DDL or utility operations on the file. You
can monitor the reload process’s progress with this command:
FUP STATUS physical-file-name
If FUP STATUS returns a RELOAD COMPLETED message and the physical file is not
being opened by another process, you can start the next MODIFY operation. Note that
the ORSERV process started by the reload operation might still open the physical file a
bit longer (about five minutes) even though FUP STATUS already returns the RELOAD
COMPLETED message.
You can find the Guardian (physical) file name by using the SHOWLABEL command.
For example:
SHOWLABEL CAT.SCH.T1, DETAIL;
For details on this command, see SHOWLABEL Command on page 4-78.
Correcting File Name Problems with MODIFY
When you create a table or index with precise file names, a file might already exist with
the same name as one of the partitions to be created. Typically, the solution is to move
the partition that already exists. Use MODIFY TABLE...MOVE PARTITION to specify a
new Guardian file name for the partition to be moved. This file can exist on the same
volume as the original or on a different volume.
For example, suppose that you need to recover this table:
CREATE TABLE T13B (....)
location $data2.ZSDXQGN5.P0000000
....
(add location $DATA2.ZSDXQGN5.P0010000,
add location $DATA2.ZSDXQGN5.P0020000,
add location $DATA2.ZSDXQGN5.P0030000);
Suppose that, however unlikely, a file with the name $DATA2.ZSDXQGN5.P0020000
already exists as a partition of another table, say T13x. Use MODIFY to move this
partition of T13x:
MODIFY TABLE CAT.SCH.T13x MOVE PARTITION WHERE LOCATION
$DATA2.ZSDXQGN5.P0020000 TO LOCATION $DATA.ZSDXQGN5.P002A000;
Following this operation, you can properly perform the original CREATE TABLE
statement with the indicated file names. You can also use this technique to correct
other individual file name problems, such as errors in naming individual partitions in
previous commands.