SQL/MP Installation and Management Guide

Moving a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
9-23
Moving Collations
The DUP utility enables you to either implicitly move all indexes with the underlying
table or to turn the implicit index duplication off. If you specify INDEXES OFF, no
indexes are moved with the underlying table. DUP implicitly moves indexes with the
underlying table by default or when you specify the INDEXES IMPLICIT option,
regardless of whether the indexes are named in the file set list.
The BACKUP and RESTORE utilities enable you to either implicitly move all indexes
with the underlying table or to move only the indexes explicitly named in the source
file-set list. If you explicitly name the index in the file set list and include the INDEXES
EXPLICIT option, the index is moved. If you do not include the index in the file set list
but include the INDEXES EXPLICIT option, the index is not moved. BACKUP and
RESTORE implicitly move indexes with the underlying table by default or when you
specify the INDEXES IMPLICIT option, regardless of whether they are named in the
file set list.
If you want to move an index to a new subvolume on the same volume or to rename
the index, you can use the ALTER INDEX statement with the RENAME option. This
strategy does not actually move the data, but alters the directory entry and all
associated catalog references to the index.
If you want to move an index from one location to another without moving the
underlying table, re-create the index in the new location, registering it in a catalog, and
then drop the old index.
This example moves an index by re-creating the index in a new location and then
dropping the old index:
>> CREATE INDEX new-index ...;
>> DROP INDEX old-index;
You can also move all or part of a partition of an index. For more information, see
Moving Partitions on page 9-23.
Moving Collations
If you move a collation, any dependent objects that refer to the original collation do not
refer to the moved collation. You can move a collation by copying it with the SQLCI
DUP utility or the Guardian BACKUP and RESTORE utilities. If you want dependent
objects to refer to the new collation, you must drop and re-create the dependent
objects, with references to the moved collation.
Moving Partitions
To move, split, merge, or redefine row boundaries of partitions of tables and indexes,
use the PARTONLY MOVE option of the ALTER TABLE or ALTER INDEX statement.
To minimize interruptions to data availability during the operation, use the WITH
SHARED ACCESS option.
For more information, see Splitting, Moving, and Merging Partitions on page 7-20 and
the descriptions of the ALTER TABLE and ALTER INDEX statements in the SQL/MP
Reference Manual.