SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Falling Back to SQL/MX Release 1.8
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
8-4
Drop SQL/MX Objects
Drop SQL/MX Objects
1. In the SQL/MX Release 2.x environment, identify and drop all SQL/MX objects,
including both user tables and system metadata tables.
a. To determine a complete list of SQL/MX schemas on the system, query the
system metadata. For example, this command returns all the schemas,
including system schemas, that are visible on the node named FIGARO:
>> set schema nonstop_sqlmx_figaro.system_schema;
>> select substring(schema_name,1,30) as schema_name,
substring(cat_name,1,20) as cat_name, node_name
from schemata s, catsys c, cat_references cr
where s.cat_uid=c.cat_uid
and c.cat_uid=cr.cat_uid
and cr.replication_rule='A';
SCHEMA_NAME CAT_NAME NODE_NAME
------------------------------ ------------------ ---------
DEFINITION_SCHEMA_VERSION_1200 NONSTOP_SQLMX_FIGARO \FIGARO
SYSTEM_SQLJ_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO
SYSTEM_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO
SYSTEM_DEFAULTS_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO
MXCS_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO
DEFINITION_SCHEMA_VERSION_1200 CAT \FIGARO
SCH CAT \FIGARO
DEFINITION_SCHEMA_VERSION_1200 SAMDBCAT \FIGARO
SALES SAMDBCAT \FIGARO
PERSNL SAMDBCAT \FIGARO
INVENT SAMDBCAT \FIGARO
b. Use the DROP SCHEMA schema CASCADE command to drop all SQL/MX
user schemas (highlighted in
bold in Step a) and their subordinate objects,
such as:
•
Tables
•
Views
•
Indexes
•
Constraints
•
Triggers
•
SPJs
•
SQL/MP aliases
Caution. Before falling back to SQL/MX Release 1.8, remove all SQL/MX objects, including
user tables and system metadata tables. SQL/MX objects are difficult to remove from a system
with an RVU earlier than the G06.23 RVU.
Note. If you encounter problems using MXCI to drop SQL/MX objects, use the mxtool
GOAWAY command instead. For information about this command, see the SQL/MX
Reference Manual.