SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-42
Considerations—ALTER TABLE
simple file name for every other partition of the same object. ALTER TABLE
uses those names if you specify only a node name and volume name or
specify only a volume name (which causes the node to default to the local
node).
The two-way split does not support the WITH SHARED ACCESS option.

A one-way move operation moves the first or last part of a partition to its
logically adjacent (existing) partition, leaving the other part in the existing
partition:
MOVE UP TO KEY val TO dest-part WITH SHARED ACCESS
MOVE FROM KEY val TO dest-part WITH SHARED ACCESS
A one-way move is similar to a one-way split, but moves data to an existing
partition instead of a new partition.
In a one-way move, name specifies a valid partition of the table. NonStop
SQL/MP determines the actual source partition during execution.
The WITH SHARED ACCESS option is required.
After a successful one-way move operation, run FUP RELOAD to reclaim
unused disk space. For more information about FUP RELOAD, see the File
Utility Program (FUP) Reference Manual.

All partitions of the table must be accessible when you add a new partition to a
table. ALTER TABLE returns an error if you attempt to add a partition while
another process has a partition locked or while another process is attempting
to execute a DDL operation on the same partition. For more information about
the errors returned, see DDL (Data Definition Language) Statements on
page D-20.
When you move the primary partition of a table, you must have read and write
authority for its associated catalogs, indexes, views, and programs.
If you specify WITH SHARED ACCESS, SQL allows concurrent INSERT,
UPDATE, DELETE, and read-only utility operations on a partition being moved
throughout most of the move operation. For more information, see WITH
SHARED ACCESS OPTION on page W-4. You can use the COMMIT option to
control when the commit phase occurs and to specify the time-out period for
lock requests and the handling of retryable errors (such as errors in lock
requests) during the commit phase of the ALTER TABLE operation.
Without WITH SHARED ACCESS, a partition being moved or split is not
accessible for INSERT, UPDATE, or DELETE operations until ALTER TABLE
finishes, but is available for SELECT operations during most of the DDL
operation. If the partition is being accessed for a SELECT or read operation,
the move or split operation cannot complete until it can obtain an exclusive lock
on all partitions, and so it either waits until the partition becomes available or
times out.