CREATE SCHEMA

The CREATE SCHEMA data description statement defines a schema in the dictionary.
idmscu19
The CREATE SCHEMA data description statement defines a schema in the dictionary.
This article describes the following information:
2
2
Authorization
To issue a CREATE SCHEMA statement, you must have the CREATE privilege on the schema named in the statement.
If you specify FOR NETWORK SCHEMA, you must have the USE privilege on the network schema.
If you specify DBNAME, you must have USE privilege on the database; if you do not specify DBNAME or specify a value of NULL, you must have DBADMIN privilege on DBNAME SYSTEM.
Syntax
  ►►─── CREATE SCHEMA 
schema-name
 ──────────────────────────────────────────────►  ►─┬───────────────────────────────────────────────────────────┬────────────────────────►◄    ├─ DEFAULT AREA 
segment-name
.
area-name
 ─────────────────────┤    ├─ FOR ─┬─ NETWORK ─┬─ SCHEMA 
network-schema-specification
 ─┤    |       └─ NONSQL  ─┘                                       |    └─ FOR SQL SCHEMA 
sql-schema-specification
 ─────────────────┘  
Expansion of network-schema-specification
  ►►─┬────────────────────┬─ 
network-schema-name
 ─┬──────────────────────────┬───►    └─ 
dictionary-name
. ─┘                       └─ VERSION 
version-number
 ─┘   ┌────────────────────────────────────┐ ►─▼─┬───────────────────────────────┬──┴────────────────────────►◄     ├──WITH VIRTUAL KEYS────────────┤     ├──WITHOUT VIRTUAL KEYS─────────┤     └─ DBNAME ─── 
database-name
 ────┘                    
Expansion of sql-schema-specification
  ►►──────────────────────── 
sql-schema-name
 ───────────────────────────────────►  ►─┬────────────────────────────┬─────────────────────────────────────────────►◄    └─ DBNAME 
sql-database-name
 ─┘  
Parameters
  • schema-name
    Specifies the name of the schema being created.
    Schema-name
    must be a 1- through 18-character name that follows the conventions for SQL identifiers.
    Schema-name
    must be unique within the dictionary.
  • DEFAULT AREA
    Specifies the default area for storing rows of tables associated with the named schema. This area is used for any such table that is not explicitly assigned an area in the CREATE TABLE statement.
  • segment-name
    .
    area-name
    Identifies the segment and area.
    You do not need to define the named segment or area in the dictionary before issuing the CREATE SCHEMA statement.
  • network-schema specification
    Identifies the network-defined schema to associate with the SQL schema.
    Expanded syntax for
    network-schema-specification
    appears immediately following the statement syntax. Descriptions for these parameters are located at the end of this section.
    The keyword NONSQL is supported as an alternative.
  • sql-schema-specification
    Identifies an existing SQL-defined schema to which the new SQL schema refers. Expanded syntax for sql-schema-specification appears immediately following the statement syntax.
Parameters for Expansion of network-schema-specification
  • network-schema-name
    Names the network-defined schema.
  • dictionary-name
    Names the dictionary that contains the network-defined schema.
    If you do not specify
    dictionary-name
    , it defaults to the dictionary to which the SQL session is connected.
  • VERSION
    version-number
    Identifies the version number of the network-defined schema. If VERSION
    version-number
    is not specified,
    version-number
    defaults to 1.
  • DBNAME
    network-database-name
    Identifies the database containing the data described by the network-defined schema.
    network-database-name
    must be a segment name or a database name that is defined in the database name table.
    If you do not specify DBNAME, no database name is included in the definition of
    schema-name
    . At runtime the database to which the SQL session is connected must include segments containing the areas described by the network-defined schema.
    For considerations about whether to specify the database when you create a schema for a network-defined schema, see "Usage," later in this section.
  • WITH VIRTUAL KEYS
    The list of visible columns of the tables representing the network records is extended with the ROWID pseudo-column and all virtual foreign key columns associated with the sets of which the record is a member. All virtual foreign key columns become referenceable in statement syntax. A SELECT * from a table, and the CA IDMS Server metadata functions returning the columns of a table, contain all the visible columns with the columns corresponding to the record elements listed first, in the order of the record definition, followed by the ROWID and the virtual foreign key pseudo-columns in alphabetical order.
    Virtual key schemas may be created for network schemas which contain sets that have primary/foreign keys defined on the SET statements. These primary/foreign keys will be ignored when accessing data with a virtual key schema due to the existence of the ROWID/virtual foreign key relationship.
  • WITHOUT VIRTUAL KEYS
    The list of visible columns of the tables representing the network records does not include the ROWID pseudo-column, nor any virtual foreign key columns. Virtual foreign key columns are not referenceable, but ROWID is referenceable according to the ROWID syntax rules in the ROWID Pseudo-column section. WITHOUT VIRTUAL KEYS is the default.
Parameters for Expansion of sql-schema-specification
  • sql-schema-name
    Names the referenced SQL-defined-schema. This named schema must not itself reference another schema.
  • DBNAME
    sql-database-name
    Identifies the database containing the data described by the referenced SQL-defined schema.
    SQL-database-name
    must be a database name that is defined in the database name table or a segment name defined in the DMCL.
    If you do not specify DBNAME, no database name is included in the definition of schema-name. At runtime, the database to which the SQL session is connected must include segments containing the areas described by the referenced SQL-defined schema.
If You Omit DEFAULT AREA
If you do not associate a default area with the schema, you must assign an area to each table that you associate with the schema in a CREATE TABLE statement. You use the IN parameter of CREATE TABLE to assign an area to a table.
Creating a Referencing Schema
If either a FOR NETWORK SCHEMA or a FOR SQL SCHEMA clause is specified, then the new SQL-defined schema that is being created is said to reference the specified schema and itself becomes a referencing schema. If a network-defined schema is specified, then creation of a referencing schema enables SQL access to a network-defined database described by the referenced schema. Similarly, if the referenced schema is SQL-defined, then the creation of a referencing schema enables SQL access to an SQL-defined database described by the referenced schema.
In either case, if a DBNAME is specified, the referencing schema provides access to the database instance identified by database-name. If no DBNAME is specified, the referencing schema is unbound and the instance of the database to be accessed is determined at runtime. Access modules that reference tables through an unbound referencing schema can therefore be used to access more than one instance of a database.
You cannot define either a table or a view in a referencing schema. However, you can define a view in another schema that references a table through a referencing schema.
Specifying network-DBNAME
When you create a schema for a network-defined schema, you use the DBNAME parameter to specify the name of the database containing the data. The name specified can be the name of a segment or a database name defined in the database name table.
If you do not specify a database name, the database to which your SQL session is connected when accessing the network-defined tables must include the segments containing the data.
For more information about defining a schema for a network-defined schema, see SQL Schema Considerations.
Specifying SQL DBNAME
When you create a referencing schema, you use the DBNAME parameter to specify the name of the database containing the data. The name specified can be either the name of a database name defined in the database name table or the name of a segment included in the DMCL.
If you do not specify a database name, the database to which your SQL session is connected when accessing the data through the referencing schema must include the segments containing the data.
Examples
Defining a Schema with a Default Area
The following CREATE SCHEMA statement defines the schema SALES. The default area for the schema is SALES_SEG.SALES_AREA.
create schema sales    default area sales_seg.sales_area;
Defining a Schema for a Network-defined Schema
In this example, the statement creates schema SALES for a network schema:
create schema sales    for network schema corpdict.sales version 100;
Defining a Schema for an SQL-defined Schema
The following CREATE SCHEMA statement defines a schema for an SQL-defined schema:
create schema any_sales for sql schema sales;
More Information