SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-43
Considerations—ALTER TABLE
Other partitions of the table are available for INSERT, UPDATE, or DELETE
operations, so processes can make read and write requests for those
partitions. For information about specifying on-demand opens, see the OPEN
ACCESSED PARTITIONS clause and the SKIP UNAVAILABLE PARTITION
clause under CONTROL TABLE Directive on page C-77.
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
successfully acquire the locks while other transactions are active and cause
those transactions to time out.
If SQL statements refer to the source partition and the partition is moved with a
simple move operation, you might need to change your program or DEFINEs
to reference the new location.

If you add, move, or split a partition to a table that has a protection view
defined on it, SQL automatically creates a corresponding partition for the
protection view on the same subvolume as the table partition and writes the
view description in the same catalog as the table partition description.
Moving, merging, or splitting a table partition invalidates a program that uses
the table or a dependent view, unless the program was compiled with CHECK
INOPERABLE PLANS and the table or view has the SIMILARITY CHECK
option enabled.
A simple move or a split of a partition invalidates previous TMF online dumps
of the affected partition. If you want TMF file recovery protection, you must
make online dumps of the newly moved or split partitions. (If the operation
specifies the WITH SHARED ACCESS option, you can begin making new
online dumps without waiting for the operation to complete. For more
information, see the WITH SHARED ACCESS OPTION on page W-4.)

Merging a partition or moving all or part of a partition into another existing
partition does not change related items such as statistics, comments, and help
text. To update statistics, use the UPDATE STATISTICS command. Check your
comments and help text to make sure they still apply.

The first key value of a new partition cannot duplicate the first key value of
another partition of the table.
You cannot create a partition on a nonaudited volume, even if the table that
includes the partition is a nonaudited table.
New partitions must comply with the limits on the number and size of partitions.
For more information, see Limits on page L-6.