SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—544517-008
5-86
Examples of MODIFY
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-84.
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.
Examples of MODIFY
•
Move all records of an existing range partition to a new location:
MODIFY TABLE tab1 MOVE PARTITION
WHERE LOCATION $data02
TO LOCATION $data03;
•
Move records of an existing range partition, whose key is equal to 10000 to the last
key, to a new location:
MODIFY TABLE tab1 MOVE PARTITION
WHERE KEY = VALUE (10000) THRU KEY = LAST KEY