NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-24
Examples—ALTER INDEX
version 1 of NonStop SQL/MP software, error 1125 (Incompatible remote
system) occurs.
You cannot use the WITH SHARED ACCESS option with a split, merge, or
move request unless each source object and each target object resides on a node
running a version of NonStop SQL/MP software (315 or later) that supports the
specific type of split, merge, or move operation.
•
Dropping partitions of indexes
°
All partitions of an index must be accessible when you drop any partition of the
index, but partitions other than the partition being dropped can be accessed by
other processes while the ALTER INDEX executes.
°
Dropping an index partition invalidates a program that uses the index, unless the
program was compiled with CHECK INOPERABLE PLANS and the table
associated with the index has the SIMILARITY CHECK option enabled.
°
If ALTER INDEX fails while attempting to drop a partition, the original index
remains intact and accessible.
°
ALTER INDEX cannot drop a primary partition or a partition that is not empty.
If you attempt to drop a partition that contains data, SQL returns error 1411
(Operation cannot be performed against a nonempty partition).
Examples—ALTER INDEX
•
The following statement changes the value of MAXEXTENTS and deallocates the
disk space for the index XORDCUS:
ALTER INDEX \SYS1.$VOL1.SALES.XORDCUS
MAXEXTENTS 300 DEALLOCATE;
•
The following statement changes the maximum number of extents for index
XEMPNAME to 200. It also sets the SERIALWRITES attribute to specify serial
mirror writes for operations on the index:
ALTER INDEX \SYS.$VOL1.PERSNL.XEMPNAME
MAXEXTENTS 200 SERIALWRITES;
•
The following statement renames the index XEMP:
ALTER INDEX XEMP RENAME XEMPID;
•
The following example moves an index partition, specifying WITH SHARED
ACCESS to keep the partition accessible to other processes during most of the
move:
ALTER INDEX $DISK1.USERS.XDATA PARTONLY MOVE TO $DISK2
WITH SHARED ACCESS NAME MOVE2D2 COMMIT BY REQUEST;
...
CONTINUE MOVE2D2 ONCOMMITERROR COMMIT BY REQUEST;
The ALTER INDEX statement specifies COMMIT BY REQUEST so that the user
can control entry to the commit phase of the operation, which locks out other