The CREATE SCHEMA data description statement defines a schema in the dictionary.
To issue a CREATE SCHEMA statement, you must have the CREATE privilege on the schema named in the statement.
If you specify FOR NONSQL SCHEMA, you must have the USE privilege on the non-SQL 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.
►►─── CREATE SCHEMA schema-name ──────────────────────────────────────────────► ►─┬─────────────────────────────────────────────────┬────────────────────────►◄ ├─ DEFAULT AREA segment-name.area-name ───────────┤ ├─ FOR NONSQL SCHEMA nonsql-schema-specification ─┤ └─ FOR SQL SCHEMA sql-schema-specification ───────┘
Expansion of nonsql-schema-specification
►►─┬────────────────────┬─ nonsql-schema-name ─┬──────────────────────────┬───► └─ dictionary-name. ─┘ └─ VERSION version-number ─┘ ►─┬───────────────────────────────┬──────────────────────────────────────────►◄ └─ DBNAME nonsql-database-name ─┘
Expansion of sql-schema-specification
►►──────────────────────── sql-schema-name ───────────────────────────────────► ►─┬────────────────────────────┬─────────────────────────────────────────────►◄ └─ DBNAME sql-database-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.
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.
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.
Identifies the nonSQL-defined schema to associate with the SQL schema.
Expanded syntax for nonsql-schema-specification appears immediately following the statement syntax. Descriptions for these parameters are located at the end of this section.
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 nonsql-schema-specification
Names the nonSQL-defined schema.
Names the dictionary that contains the nonSQL-defined schema.
If you do not specify dictionary-name, it defaults to the dictionary to which the SQL session is connected.
Identifies the version number of the nonSQL-defined schema. If VERSION version-number is not specified, version-number defaults to 1.
Identifies the database containing the data described by the nonSQL-defined schema. nonsql-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 non-SQL-defined schema.
For considerations about whether to specify the database when you create a schema for a non-SQL-defined schema, see "Usage," later in this section.
Parameters for Expansion of sql-schema-specification
Names the referenced SQL-defined-schema. This named schema must not itself reference another schema.
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 NONSQL 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 non-SQL-defined schema is specified, then creation of a referencing schema enables SQL access to a non-SQL-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 non-SQL-DBNAME
When you create a schema for a non-SQL-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 non-SQL-defined tables must include the segments containing the data.
Note: For more information about defining a schema for a non-SQL-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.
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 Non-SQL-defined Schema
In this example, the statement creates schema SALES for a non-SQL schema:
create schema sales for nonsql 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;
|
Copyright © 2014 CA.
All rights reserved.
|
|