SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

• A nonempty catalog cannot be dropped.
• In a distributed database environment, only the super ID user and the person who creates a
catalog can register and unregister it. For more information, see the “Managing an SQL/MX
Distributed Database” (page 266).
Examples for Creating a Catalog
To create a user catalog, enter:
CREATE CATALOG mycat;
To create a user catalog on the local node and use the LOCATION clause to specify the location
of the metadata tables for the catalog, enter:
CREATE CATALOG mycat LOCATION $DATA08;
For more information, see the SQL/MX Reference Manual.
Creating Catalogs for Single-Node and Distributed Systems
From NonStop SQL/MX, you must create a catalog on the node where it will reside. You cannot
create catalogs for remote nodes from the local node.
In a distributed database environment, use the REGISTER CATALOG command to register a catalog
from the local node for remote nodes. Remote nodes on which a catalog has been registered
contain information about that catalog in their system metadata. For more information, see the
“Managing an SQL/MX Distributed Database” (page 266).
Reserved Catalogs
Catalog names beginning with NonStop_SQLMX_ are reserved for the first part of the schema
names and metadata tables that reside in the system catalog. You are not allowed to create or
drop catalogs with these reserved names.
Creating Schemas
After you have created a catalog, you can begin to create its schemas.
A schema is a named collection of SQL/MX database objects, including tables, views, indexes,
and so forth. Each object is described in exactly one schema, although an object can refer to
objects that are described in other schemas. A schema cannot contain other schemas.
Schema Naming
The schema name is an SQL identifier that represents the second part of the three-part ANSI name
of the form catalog.schema.name. The schema’s SQL identifier must be unique within the
catalog. NonStop SQL/MX automatically qualifies a schema name with the current default catalog
name unless you explicitly specify a catalog name with the schema name. For example:
cat2.schema2
For more information, see the SQL/MX Reference Manual.
Rules for Creating and Dropping Schemas
A schema is the unit of object ownership. A user who creates a schema is the owner of that schema.
NOTE: Starting with SQL/MX Release 3.1, new features related to the ownership of schemas
and its objects, and the transfer of ownerships are introduced. For more information on these new
features, see the SQL/MX Reference Manual.
Examples for Creating Schemas
This example creates a user schema that is owned by the regular user, sql.gdavis, who creates it.
78 Creating an SQL/MX Database