SQL/MP Installation and Management Guide

Moving a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
9-15
Dropping and Re-creating Catalogs
For information about moving an SQL program stored in a Guardian file, see Moving
Programs on page 10-39. To move an SQL program stored in an OSS file, use the
appropriate OSS utility.
Dropping and Re-creating Catalogs
To move a catalog, you must first drop all the objects described in it, drop the catalog,
and then re-create it in a new location. Finally, restore the SQL objects to disk,
referring to the new catalog name.
If you want to move both a catalog and all the dependent objects described in that
catalog to a new location, see Steps for Moving a Database on page 9-25.
If you want to move only the catalog tables to a new location and keep the dependent
objects in the same location, you must use the BACKUP/RESTORE method, because
the DUP utility does not allow for the redefinition of a source and target catalog without
also mapping target locations for the objects.
Moving Catalogs
To move a catalog, follow these steps:
1. Determine the dependent programs of the objects described in the catalog by
using the DISPLAY USE OF command. The move operations invalidate these
programs.
2. Back up all the dependent objects described in the catalog, including programs.
Use the FROM CATALOGS parameter of the qualified file-set list to identify the
objects from the catalog. This command, entered at the command interpreter
prompt, accomplishes the operation:
43> BACKUP $TAPE, *.*.* FROM CATALOG old-catalog-name,
AUDITED, OPEN, LISTALL
3. Create the new catalog in SQLCI, as shown in this statement:
>> CREATE CATALOG new-catalog-name
;
4. Drop the specified objects and the old catalog:
>> PURGE *.*.* FROM CATALOG old-catalog-name ALLOWERRORS ON;
>> DROP CATALOG old-catalog-name;
5. Restore the backup tape by using the CATALOG option to define the new catalog.
At the command interpreter prompt, enter:
45> RESTORE $TAPE, *.*.*, CATALOG new-catalog-name,
AUDITED, OPEN, TAPEDATE, LISTALL
6. SQL compile the programs described in the new catalog or any programs
referencing the objects described in the new catalog.
7. Make new TMF online dumps of the catalog and all restored audited objects.