SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-37
Considerations—ALTER TABLE
add-spec
specifies options for a partition added with the ADD PARTITION clause.
The FIRST KEY clause specifies the primary or clustering key value for the first
key allowed in a new partition of a table with key-sequenced file organization. It is
required for key-sequenced files. val is a literal compatible with the data type of
the key column that specifies the key value. For clustering keys, specify multiple
vals, in order.
The FIRST KEY clause does not apply to tables with relative or entry-sequenced
file organization; ADD PARTITION adds an empty partition to the end of a relative
or entry-sequenced file.
The WITH DATA MOVEMENT clause directs SQL to transfer appropriate rows
from name to dest-part. If you do not specify WITH DATA MOVEMENT, ADD
PARTITION creates an empty partition and returns an error if records exist within
the FIRST KEY declaration of the new partition.
add-spec also includes options that allow you to specify a catalog for the new
partition, a physical volume if DSM/Storage Manager is installed, and to set the
EXTENT, MAXEXTENTS and FORMAT file attributes for the partition. These
options are the same as options described under move-spec earlier in this entry.
REUSE PARTITION reused-part reuse-spec
reuses a partition, specified in reused-part, using the options specified in
reuse-spec. When specifying reused-part, include the volume, subvolume,
and file name of the partition.
reuse-spec
specifies FIRST KEY for a partition reused with the ADD PARTITION clause.
The FIRST KEY clause specifies the primary or clustering key values for the first
key allowed in a reused partition of a table with key-sequenced file organization. It
is required for key-sequenced files. val is a list of comma-separated literals (one
for each column in key) that specifies the beginning key value for the partition to be
reused.
Considerations—ALTER TABLE

To alter a table or partition, you must be a generalized owner of the table or
partition. In addition, you must have authority to read and write to the affected
catalogs. For a partitioned table, affected catalogs include all catalogs that describe
a partition of the table or that will describe a new partition of the table as a result of
the ALTER TABLE operation.
ALTER TABLE executes only if the specified table or partition is available. Unless
you are altering file attributes for a partition, all partitions of the table must be
available.