NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-23
Considerations—ALTER INDEX
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.)
°
Completing ALTER INDEX operations
When a split command 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 partition 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 the request fails, the original index normally remains intact and accessible.
However, if ALTER INDEX fails because of a CPU or system failure, a newly
added, moved, or split partition of the index might continue to exist—along with
the original index—even though it is inaccessible. After the system becomes
available, use CLEANUP to drop the new partition (or ask a user with local
super ID authority to do so), then reissue the ALTER INDEX statement. ALTER
INDEX returns an error if there is a problem with the index.
When you add a partition to an index, 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, see the entry for Limits
on page L-5.
If ALTER INDEX fails during a merge or one-way 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 request an ALTER INDEX name PARTONLY RECOVER
INCOMPLETE SQLDDL OPERATION, followed by a FUP RELOAD
command for the target partition.
°
The F flag, UNRECLAIMED FREE SPACE, indicates that you need to
request a FUP RELOAD operation to reclaim space from the source
partition.
If the slack space in the source table is less than the value chosen for the target
table, a MOVE operation can fail with a file full error. To prevent this error,
check the actual slack amount in the source file (using FILEINFO STATISTICS)
and specify EXTENTS and MAXEXTENTS values for the target table
sufficient to hold the data.
°
Versioning requirements
If any partition of an index specified in a move or split operation (even a
partition other than the one being moved or split) resides on a node running