SQL/MP Installation and Management Guide

Moving a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
9-35
Backing Up and Purging SQL Objects
Creating an OBEY Command File to Re-Create the Catalogs
(Step 4)
Create an OBEY command file containing SQL statements that will re-create your
catalogs. The statements must specify the same security and the same owners for
each catalog and for each catalog table that can be individually secured.
The catalog security is the security of the catalog tables, except for the USAGES,
TRANSIDS, and PROGRAMS tables, which can be secured individually. In the system
catalog, the CATALOGS table can also be secured individually.
To find out the security and owner of your catalog tables, query the catalog TABLES
table:
>> LOG log-file CLEAR;
>> SELECT TABLENAME, SECURITYVECTOR, GROUPID, USERID
+> FROM catalog-name.TABLES
+> WHERE TABLENAME = "\system.$volume.catalog-name.TABLES";
To find out the security and owner of the USAGES, TRANSIDS, and PROGRAMS
tables, specify those tables in a query:
>> SELECT TABLENAME, SECURITYVECTOR, GROUPID, USERID
+> FROM catalog-name.TABLES
+> WHERE TABLENAME = "\system.$volume.catalog-name.USAGES"
+> OR TABLENAME = "\system.$volume.catalog-name.TRANSIDS"
+> OR TABLENAME = "\system.$volume.catalog-name.PROGRAMS";
The OBEY command file needs to contain these statements:
•
A CREATE CATALOG statement for each catalog on the node. To make sure the
catalog is re-created with the same security, use the SECURE option to specify the
catalog security:
>> CREATE CATALOG $volume.subvolume SECURE "security-string";
•
An ALTER TABLE statement for any table whose security or owner is different from
the catalog security or owner:
>> ALTER TABLE $volume.subvolume.PROGRAMS
+> SECURE "security-string"
+> OWNER "group-num, user-num" ;
Purging SQL Objects and Programs (Step 5)
To purge all SQL objects and programs, do these:
1. Log on as the super ID to avoid security restrictions.
2. Remove all SQL objects from the node except the system catalog, the CATALOGS
table, and the SQLCI2 program you are using. From SQLCI, enter this command
for each catalog except the system catalog:
>> PURGE *.*.* FROM CATALOG catalog-name !,
+> ALLOWERRORS ON;