Defining a Database Using SQL › Creating a Schema

Creating a Schema

You create a schema by issuing a CREATE SCHEMA statement.

Things You Can Specify

1. Schema name
2. Optionally a default area
3. Optionally a reference to another schema, either an SQL or non-SQL schema

Considerations

• The default area specified in the CREATE SCHEMA statement must be defined to the application dictionary in which the schema is being defined. The default area is used to contain table rows if no area is specified as part of the table definition.
• If reference is made to another schema, the schema containing the reference is called a referencing schema and the schema that it refers to is a referenced schema. A referencing schema cannot contain table or view definitions.
  • You can reference a non-SQL schema to enable SQL access to a non-SQL defined database.
  • You can reference an SQL schema to allow identical SQL defined databases to be accessed through a single schema definition. The referenced schema must not be itself a referencing schema nor contain tables that reference or are referenced by tables in other schemas. For other considerations associated with referencing SQL schemas, see the CA IDMS SQL Reference Guide.
• A referencing schema can be bound to a specific database instance or unbound by not specifying a DBNAME as part of the referencing schema definition. Accessing tables through an unbound referencing schema allows runtime determination of the database instance to be accessed based on the database to which an SQL session connects. Therefore, the same table name (and access modules) can be used to access different database instances by connecting to different database names. Each database name definition must include the appropriate database segments to be accessed.
• The owner of the schema being created (and, therefore, all tables and views within the schema) is the user issuing the CREATE SCHEMA statement. To reassign ownership to another authorization ID, use the TRANSFER OWNERSHIP statement, as described in the CA IDMS SQL Reference Guide.

Examples

In the following example, the schema PROD is defined. The default area for the schema is PROD_AREA. Rows in tables associated with this schema will be stored in PROD_AREA unless an area name is explicitly coded in the CREATE TABLE statement.

create schema prod
   default area prod_area;

In the following example, the schema WINDOW is defined and associated with the non-SQL defined schema SCHED. Programs using SQL data manipulation language statements can access data in the non-SQL database by using the schema WINDOW.

create schema window
    for nonsql schema sched;

In the following example, the schemas HRTEST1 and HRTEST2 are defined as referencing schemas for SQL schema HRTEST0. References to tables in HRTEST1 will access data in the HRTEST1 database while those in HRTEST2 will access data in the HRTEST2 database. These databases contain identically-defined base tables as described by the HRTEST0 schema.

create schema hrtest1
   for sql schema hrtest0 dbname hrtest1;

create schema hrtest2
   for sql schema hrtest0 dbname hrtest2;

In the following example, the schema HRTEST is also defined as a referencing schema for SQL schema HRTEST0; however, HRTEST is not associated with any specific database instance. Consequently, the data that is accessed through references to HRTEST tables will be determined at runtime by the database to which the SQL session connects.

create schema hrtest
   for sql schema hrtest0;