SQL/MP Installation and Management Guide

Moving a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
9-16
Moving Tables
Moving Tables
You can move a table with the SQLCI DUP, LOAD, or COPY utility, or with the
Guardian utilities BACKUP and RESTORE. Each utility involves some special
considerations.
The DUP and BACKUP/RESTORE utilities enable you to specify the source tables by
name or by qualified file-set list. If the table and its dependent objects all reside on the
same subvolume and are moved to another subvolume, you can use a target file-set
list to specify the new location. If the table and its dependent objects are to reside on
two or more subvolumes, you must use the MAP NAMES option. You cannot use the
MAP NAMES option and the target file-set list in the same command. If the dependent
objects are described in a new catalog, use the CATALOG clause to define the new
catalog for the objects.
If you want to move a table to a new subvolume on the same volume or to rename the
table, you can use the ALTER TABLE statement with the RENAME option. This
approach does not actually transport the data, but alters the directory entry and all
associated catalog references to the table.
The move utilities do not automatically move dependent programs with the underlying
table. Programs are moved only if they are included in the file set list. After you have
moved a table, you should include steps to explicitly SQL compile the dependent
programs to avoid automatic recompilation. Programs are not invalidated by the move
operation but will be invalidated when the old table is dropped.
The move utilities also do not automatically move any collations used by a table or its
dependent objects.
You can move a nonpartitioned table to another volume by using the MOVE option of
the ALTER TABLE statement. For more information, see Splitting, Moving, and
Merging Partitions on page 7-20. You can also move all or part of a partition of a table.
For more information, see Moving Partitions on page 9-23.
Using DUP
When you DUP a table, the utility attempts to duplicate all partitions, indexes, and
protection and shorthand views. To duplicate all these objects along with your table,
specify either a target file-set list or the MAP NAMES option so that DUP can map the
dependent source objects to the target objects. In addition, DUP writes all constraints,
comments, and statistical information to the appropriate tables of the target catalog.
You can limit the automatic duplication of indexes and views with the INDEXES and
VIEWS parameters.
Using COPY and LOAD
You can move tables with the COPY and LOAD utilities. During the LOAD operation,
you can define the block structuring of a target key-sequenced table. By specifying
SLACK or DSLACK, you can load a source table into a target table with free space for
future insertions.