SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-45
Considerations—ALTER TABLE
When you add a partition to a table, the PARTNS catalog table and associated
IXPART01 index might become full. To correct the situation, distribute object
and partition definitions across multiple catalogs. For more information about
partition limits and the PARTNS table, see Limits on page L-6.
If the SLACK space in the source file is less than the value chosen for the
target file, a MOVE operation can fail with a file full error. To prevent this
situation, check the actual slack amount in the source file (using the FILEINFO
STATISTICS command) and specify EXTENTS and MAXEXTENTS values for
the target file that are sufficient to hold the data.
If ALTER TABLE fails during a merge or move operation with the WITH
SHARED ACCESS option, use the SQL FILEINFO utility to see if the D or F
flag is present for the target partition:

The D flag, INCOMPLETE SQLDDL OPERATION, indicates that you need
to issue an ALTER TABLE name PARTONLY RECOVER INCOMPLETE
SQLDDL OPERATION request, followed by FUP RELOAD.

The F flag, UNRECLAIMED FREE SPACE, indicates that you need to
request a FUP RELOAD operation to reclaim space on disk.

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 the SQL/MP software (315 or later) that supports the
specific type of split, merge, or move operation.
If any partition of a table specified in a move or split operation (even a partition
of the table other than the one being moved or split) resides on a node running
version 1 of the SQL/MP software, error 1125 (Incompatible remote system)
occurs.

Considerations: Format 2-enabled tables

The partition array can be modified between any of the partition array values.

Modifying the partition array of a table implicitly converts all indexes on the
table to the same partition array value. There is no command to explicitly alter
the partition array of an index because an index inherits the value from the
table. Modifying a table to allow it to have a FORMAT2ENABLED partition
array results in the partition arrays of all the indexes of that table being
converted to FORMAT2ENABLED.

A table can only be converted to a Format 1 from a Format 2 table if all
partitions of the table and all partitions of all indexes of the table are Format 1.
If you attempt to convert a Format 2 to Format 1 when any Format 2 partitions
exist you will receive an error.

Modifying the partition array does not change the format of any of the existing
partitions. It enables new partitions to be created in formats compatible to that
partition array value.