NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-42
Considerations—ALTER TABLE
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.
°
Versioning considerations
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 NonStop 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 NonStop SQL/MP software, error 1125 (Incompatible remote
system) occurs.
•
Dropping partitions of tables
°
All partitions of a table must be accessible when you drop any partition of the
table, but partitions other than the partition being dropped can be accessed by
other processes while the ALTER TABLE executes.
Dropping a partition requires SQL to update any catalog that describes a
shorthand view in terms of the dropped partition, as well as to update the
catalogs for the table itself. If any such catalog is unavailable, the ALTER
TABLE fails.
°
Dropping a table partition invalidates a program that uses the table or that uses a
view that depends on the table, unless the program was compiled with CHECK
INOPERABLE PLANS and the table or view has the SIMILARITY CHECK
option enabled.
Invalid programs that reference the table with DEFINEs or with references to
partitions other than the dropped partition can be recompiled (automatically or
explicitly). Programs that reference the table with a physically coded name for
the dropped partition must be modified before they can be recompiled.
°
If ALTER TABLE fails while attempting to drop a partition, the original table
remains intact and accessible.
°
ALTER TABLE cannot drop a primary partition, a partition that is not empty, or
a partition in a table with relative or entry-sequenced file organization that is not
the last partition.
•
Modifying the partition array