SQL/MP Installation and Management Guide

Moving a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
9-18
Moving Tables
Operational Steps
To move a table, follow these steps:
1. Determine the name of the table you want to move.
2. Determine the dependent objects with the DISPLAY USE OF command. Any
dependent programs are invalidated when you drop the old table.
3. Check that sufficient space exists on the targeted volumes to create the new table
and its dependent objects.
4. Create an OBEY command file for the command if the command is long or will be
reused. Consider whether the new table will refer to the same collations as the old
table; if not, update the CREATE TABLE statement to refer to the new collations.
5. Check that the table is not in use.
6. Perform the move command of your choice. Use the logging facility of SQLCI or
the LISTALL option of BACKUP and RESTORE. Keep the log for your records.
7. Drop the old table by using the DROP TABLE statement.
8. Alter the DEFINEs, if used, to refer to the new location of the table and any
dependent views.
9. SQL compile all invalidated programs.
10. Make a new TMF online dump if the table is audited.
11. Restart the application, if stopped, by using the new DEFINEs.
Examples of Using DUP to Move Tables
This example shows how to use the DUP command to move a single table that has no
dependent objects from one subvolume to another. The new table is to be registered in
the same catalog as the source table. The CATALOG option is required if the current
default catalog does not apply. This example sets the default catalog before the DUP
command is entered.
>> CATALOG $VOL1.SALES;
>> DUP $VOL1.SALES.ODETAIL, $VOL1.MKTG.ODETAIL;
In this example, the wild-card file name * identifies the target file-set list. By using the
wild-card character, you enable a table with dependencies to be moved where the
dependent objects are also duplicated. The new table and its dependencies are to be
registered in the catalog $VOL1.ADMIN. In this example, all the dependent objects of
EMPLOYEE also reside on $VOL1.PERSNL, so a target file-set list is used.
>> DUP $VOL1.PERSNL.EMPLOYEE, $VOL1.ADMIN.*,
+> CATALOG $VOL1.ADMIN, ALLOWERRORS ON;
This example demonstrates the MAP NAMES and CATALOG options. The table
EMPLOYEE and its dependencies are being moved from the PERSNL subvolume to