SQL/MP Installation and Management Guide

Moving a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
9-21
Moving Views
>> CREATE TABLE $VOL1.PERSNL.EMPLOYEE LIKE $OLD.PERSNL.EMPLOYEE
+> WITH CONSTRAINTS;
>> CREATE INDEX $VOL2.PERSNL.XEMPNAME
+> ON $VOL1.PERSNL.EMPLOYEE (LAST_NAME, FIRST_NAME)
+> CATALOG $VOL2.PERSNL;
>> ALTER TABLE $VOL1.PERSNL.EMPLOYEE NO AUDIT;
>> LOAD $OLD.PERSNL.EMPLOYEE, $VOL1.PERSNL.EMPLOYEE,
+> SORTED, SLACK 20;
>> ALTER TABLE $VOL1.PERSNL.EMPLOYEE AUDIT;
>> CREATE VIEW $VOL1.PERSNL.EMPLIST
+> AS SELECT
+> EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, JOBCODE
+> FROM $VOL1.PERSNL.EMPLOYEE
+> FOR PROTECTION
+> CATALOG $VOL1.PERSNL;
The LOAD utility automatically loads any dependent indexes created before loading
the underlying table. The CREATE TABLE LIKE statement, however, does not apply
associated partitions, views, or indexes to the target table. You must create these after
creating the table.
The next example shows a sequence of statements and commands to copy the
EMPLOYEE table and its dependent index and view. If the target table is empty, this
COPY example and the preceding LOAD example create the same files except for the
slack space created with the load operation. The COPY operation occurs within a user-
defined TMF transaction.
>> CREATE TABLE $VOL1.PERSNL.EMPLOYEE LIKE $OLD.PERSNL.EMPLOYEE
+> WITH CONSTRAINTS;
>> CREATE INDEX $VOL2.PERSNL.XEMPNAME
+> ON $VOL1.PERSNL.EMPLOYEE (LAST_NAME, FIRST_NAME)
+> CATALOG $VOL2.PERSNL;
>> BEGIN WORK;
>> COPY $OLD.PERSNL.EMPLOYEE, $VOL1.PERSNL.EMPLOYEE;
>> COMMIT WORK;
>> CREATE VIEW $VOL1.PERSNL.EMPLIST
+> AS SELECT
+> EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, JOBCODE
+> FROM $VOL1.PERSNL.EMPLOYEE
+> FOR PROTECTION
+> CATALOG $VOL1.PERSNL;
Moving Views
Views do not maintain any physical data to be moved, but they do maintain physical file
labels. Typically, protection views are implicitly moved when the underlying table is
moved. Dependencies between a protection view and the underlying table are
enforced by SQL/MP. Shorthand views can be moved with normal BACKUP and
RESTORE procedures.
The DUP utility enables you to implicitly move both protection and shorthand views
when you move the underlying table. You can restrict whether a view is duplicated by
using the VIEWS EXPLICIT option. If you explicitly name a view in the file set list and