SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-44
Considerations—ALTER TABLE
You can partition tables of any file organization, but you cannot partition a
key-sequenced table that has a system-defined primary key (as opposed to a
user-defined primary key) unless it also has a clustering key.

ALTER TABLE operations that use WITH SHARED ACCESS generally take
longer to complete than those that do not. However, because WITH SHARED
ACCESS operations allow concurrent read and write access to the source
partition, they cause far less application downtime than equivalent operations
without WITH SHARED ACCESS.
The duration of a WITH SHARED ACCESS operation increases with the
number and length of transactions on the node that contains the source
partition, particularly with the number and length of transactions that involve
the source partition and the amount of activity on the audit trail used for the
source partition.

An operation that uses WITH SHARED ACCESS cannot complete successfully
unless the TMF audit trail generated during the operation is available for
reading later in the operation. If a required audit trail has been overwritten, a
WITH SHARED ACCESS operation cancels changes made to the database
and terminates.
When performed on a source object that has a valid TMF online dump, an
operation that uses WITH SHARED ACCESS generates audit information for
the target object.
Lengthy operations that use WITH SHARED ACCESS might require an
operator to mount tapes of TMF audit dumps. (Requests to mount TMF audit
dump tapes for WITH SHARED ACCESS operations are not distinguishable
from other requests to mount TMF audit dump tapes. Such requests are
generally sent to an operator's console. SQL does not return information about
such requests to the terminal or process that started the operation.)

When a split operation with the WITH SHARED ACCESS option finishes
successfully, check SQL FILEINFO for the source partition to see if the F flag is
present. For a merge operation with the WITH SHARED ACCESS OPTION,
check the target partition; for a one-way move operation with the WITH
SHARED ACCESS option, check the source and target partitions. If the F flag
is present, the file contains data blocks allocated to obsolete (moved) records.
Use the FUP RELOAD command to reclaim the disk space. For more
information, see the File Utility Program (FUP) Reference Manual.
If ALTER TABLE fails, the original table normally remains intact and accessible.
However, if ALTER TABLE fails because of a processor failure or system
crash, a newly added, moved, or split partition of the table might continue to
exist—along with the original table—although it is inaccessible. After the
system becomes available, use CLEANUP to drop the new partition (or ask the
local super ID to do so), reissue the ALTER TABLE statement. ALTER TABLE
returns an error if there is a problem with the table.