NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-39
Considerations—ALTER TABLE
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.
°
Access requirements
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. See DDL (Data Definition
Language) Statements on page D-19 for more information about the errors
returned.
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. (See the entry WITH SHARED
ACCESS OPTION on page W-4 for details.) 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 will either wait until the partition becomes available or time
out.
Other partitions of the table are available for INSERT, UPDATE, or DELETE
operations, so processes can make read and write requests for those partitions.
(See the OPEN ACCESSED PARTITIONS clause and the SKIP
UNAVAILABLE PARTITION clause under CONTROL TABLE Directive
on
page C-72 for information about specifying on-demand opens.)
Without WITH SHARED ACCESS, you might want to stop activity on a table
when you intend to move or split one of the partitions to the table.
To update label information for partitions, ALTER TABLE requires exclusive
label locks on all partitions of a table during the final phase of a move or split
operation. Transaction activity on the table can cause ALTER TABLE to time
out when it attempts to acquire the locks. Alternatively, ALTER TABLE can