SQL/MP to SQL/MX Database and Application Migration Guide

Converting SQL/MP Tables to SQL/MX Tables
HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001
5-5
System Catalogs and Metadata
System Catalogs and Metadata
In NonStop SQL/MP, the metadata is a combination of system catalog metadata and
user catalog metadata. NonStop SQL/MP creates the system catalog when you issue
the CREATE SYSTEM CATALOG command, in whatever subvolume you specify. The
default is $SYSTEM.SQL. NonStop SQL/MP creates user catalogs when you issue the
CREATE CATALOG command in the specified subvolume.
Each node on which NonStop SQL/MP is installed has one system catalog and one or
more user catalogs. Each table, view, index, partition, collation, or catalog table located
on a node must be described in a catalog on the same node. Normally, SQL programs
are also registered in a catalog.
SQL/MP metadata, also called the Data Dictionary, is organized by disk. It consists of
all the catalogs on a network and labels for each file on the volume. The label includes
the name of the object, the name of the catalog that describes the object, and other
information about the file. For more information, see the
SQL/MP Reference Manual.
In NonStop SQL/MX, the system catalog is NONSTOP_SQLMX_nodename. SQL/MX
metadata is organized within these system schemas:
A definition schema resides in the location that you specify when creating the catalog.
There is one definition schema per catalog. The remaining system schemas reside in
the location specified when NonStop SQL/MX was installed. There is only one of each
of these types of system schemas per node.
User Catalogs and Schemas
A SQL/MP catalog is a set of tables and indexes that describe SQL/MP database
objects. Tables in the set are called catalog tables, and NonStop SQL/MP creates them
and their indexes when you execute a CREATE CATALOG statement. Each SQL/MP
catalog (the set of catalog tables and their indexes) resides on its own Guardian
subvolume, and the name of that subvolume is also the name of the catalog. NonStop
SQL/MP allows you to create and drop catalogs. For more information, see the
SQL/MP Reference Manual.
SYSTEM_SCHEMA System schema tables that list information such as
catalogs and schemas available on the node.
DEFINITION_SCHEMA_
VERSION_
versnum
Definition schema tables that list information, such as
columns in tables, constraints, partitions, views,
procedures, and so on in a catalog.
SYSTEM_DEFAULTS_
SCHEMA
User-provided system defaults for the node.
MXCS_SCHEMA MXCS subsystem data source and governing
information for the node.
SYSTEM_SQLJ_SCHEMA System schema that contains only the stored
procedure VALIDATEROUTINE, which is for internal
use.