SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-23
Considerations—ALTER INDEX

ALTER INDEX 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 accessible 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 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 processor or system failure, a
newly added, moved, or split partition of the index might continue to exist—
along with the original index—although 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), and 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 Limits on page L-6.