SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-9
Creating Schemas
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 about schema naming, see the SQL/MX Reference Manual.
Rules for Creating and Dropping Schemas
•
A schema is the unit of object ownership. The person who creates a schema is the
owner of that schema and all the objects in that schema.
•
Only the super ID user can both create a schema and specify any valid
authorization ID as the schema owner.
•
Only the super ID user or the schema owner can create and drop objects in that
schema.
Examples for Creating Schemas
This example creates a user schema that is owned by the regular user, sql.gdavis, who
creates it.
CREATE SCHEMA mycat.myschema;
This example creates a schema that is owned by the user sql.gdavis and located on
the volume ZSDA142A:
CREATE SCHEMA mycat.myschema AUTHORIZATION "gdavis" LOCATION
ZSDA142A;
The authorization ID must be the current authorization ID unless it is a super ID user. A
super ID user can specify any currently valid authorization ID as the owner of the
schema. For more information, see Access Privileges for SQL/MX Database Objects
on page 7-10 and the SQL/MX Reference Manual.