SQL/MP Installation and Management Guide

Moving a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
9-34
Backing Up and Purging SQL Objects
Then, use the SELECT statement to list the catalog names:
>> LOG log-file CLEAR;
>> SELECT CATALOGNAME FROM $volume.SQL.CATALOGS;
In the example, log-file is a device, process, or disk file, and $volume is the
volume on which the system catalog resides.
To find all local and remote objects that need to be backed up and the catalog names
of dependent objects, query the USAGES table in each catalog:
>> SELECT * FROM catalog-name.USAGES;
All user-defined objects listed in the USAGES tables of the catalogs on the affected
node need to be backed up and purged before the renaming or renumbering operation.
For example, if you have an index on a node you want to renumber, you must back up
the underlying table even if the table does not have a single partition on the node.
Do not attempt to back up and restore catalog tables and indexes; those objects are
re-created when the catalogs are re-created.
Backing Up SQL Programs (Step 2)
To back up SQL programs stored in Guardian files on your node, use the Guardian
BACKUP utility. Back up all the programs registered in each catalog by using the
FROM CATALOG option to back up all programs in a catalog onto tape.
To back up an SQL program stored in an OSS file, use the appropriate OSS utility.
Backing up SQL programs separately from SQL objects is recommended. You can
issue BACKUP commands at a TACL prompt, but it is probably more efficient to do the
BACKUP operation by using an OBEY command file.
This command backs up all SQL programs registered in the specified catalog:
53> BACKUP $TAPE1, *.*.*
FROM CATALOG old-catalog-name WHERE SQLPROGRAM,
ARCHIVEFORMAT, AUDITED, OPEN, LISTALL
Use the AUDITED option for all objects, including nonaudited ones, because the file
labels for the objects are audited.
Backing Up Other SQL Objects (Step 3)
This command backs up all SQL objects other than programs registered in the
specified catalog:
54> BACKUP $TAPE2, *.*.*
FROM CATALOG old-catalog-name WHERE NOT SQLPROGRAM,
ARCHIVEFORMAT, AUDITED, OPEN, LISTALL
Note. Do not back up user catalogs, because RESTORE cannot restore them as user
catalogs. Use an OBEY command file instead to re-create the catalogs and the catalog
security and ownership.