CREATE SCHEMA

The CREATE SCHEMA statement defines a collection and optionally creates tables, views, aliases, indexes, and distinct types. Comments and labels may be added in the catalog description of tables, views, aliases, indexes, columns, and distinct types. Table, view, and distinct type privileges can be granted to users.

Invocation

This statement can only be issued using the RUNSQLSTM command.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• The privileges defined for the following SQL statements:
  • CREATE COLLECTION and
  • Each SQL statement included in the CREATE SCHEMA statement
• Administrative authority

If the AUTHORIZATION clause is specified, the privileges held by the authorization ID of the statement must also include at least one of the following:

• The system authority *ADD to the user profile identified by authorization-name
• Administrative authority

Syntax

>>-CREATE SCHEMA----+-collection-name--------------------+------>
                    '-AUTHORIZATION--authorization-name--'
 
>-----+------------------+---+----------------------+----------->
      '-IN ASP--integer--'   '-WITH DATA DICTIONARY-'
 
>-----+------------------------------------------------------------+>
      |  .------------------------------------------------------.  |
      |  V                                                      |  |
      '-----+-COMMENT ON statement---------------------------+--+--'
            +-CREATE ALIAS statement-------------------------+
            +-CREATE DISTINCT TYPE statement-----------------+
            +-CREATE INDEX statement-------------------------+
            +-CREATE TABLE statement-------------------------+
            +-CREATE VIEW statement--------------------------+
            +-GRANT (Table Privileges) statement-------------+
            +-GRANT (User-Defined Type Privileges) statement-+
            |                    (1)                         |
            '-LABEL ON statement-----------------------------'
 
>--------------------------------------------------------------><
 

Notes:

1. Labels and comments on packages, procedures, functions, and parameters are not supported in the CREATE SCHEMA statement.

Description

collection-name

Names the schema. A collection is created using this name. If collection-name is specified, the authorization ID of the statement is the run-time authorization ID. The name must not be the same as the name of an existing collection or library at the current server.

authorization-name

Identifies the authorization ID of the statement. This authorization name is also the collection-name. The name must not be the same as the name of an existing collection or library at the current server.

IN ASP integer

Specifies the auxiliary storage pool (ASP) in which to create the collection. The integer must be between 1 and 16. If 1 is specified, the collection is created on the system ASP. If this clause is omitted, an ASP of 1 is assumed.

WITH DATA DICTIONARY

If this clause is specified, an IDDU data dictionary is created in the collection.

COMMENT ON statement

Adds or replaces comments in the catalog descriptions of tables, views, or columns. Comments on packages are not allowed. See the COMMENT ON statement ***.

CREATE ALIAS statement

Creates an alias into the collection. See the CREATE ALIAS statement ***.

CREATE DISTINCT TYPE statement

Creates a user-defined distinct type into the collection. See the CREATE DISTINCT TYPE statement "CREATE DISTINCT TYPE".

CREATE INDEX statement

Creates an index into the collection. See the CREATE INDEX statement ***.

CREATE TABLE statement

Creates a table into the collection. See the CREATE TABLE statement ***.

CREATE VIEW statement

Creates a view into the collection. See the CREATE VIEW statement ***.

GRANT (Table Privileges) statement

Grants privileges for tables and views in the collection. See the GRANT statement "GRANT (Table Privileges)".

GRANT (User-Defined Type Privileges) statement

Grants privileges for user-defined types in the collection. See the GRANT statement "GRANT (User-Defined Type Privileges)".

LABEL ON statement

Adds or replaces labels in the catalog descriptions of tables, views, or columns in the collection. Labels on packages are not allowed. See the LABEL ON statement***.

Notes

If a CREATE TABLE, CREATE INDEX, CREATE ALIAS, CREATE DISTINCT TYPE, or CREATE VIEW statement contains a qualified name for the table, index, alias, distinct type, or view being created, the collection name specified in that qualified name must be the same as the name of the collection being created. Any other table or view names referenced within the schema definition may be qualified by any collection name. Unqualified table, index, alias, distinct type, or view names in any SQL statement are implicitly qualified with the name of the created collection. The maximum length of any individual CREATE TABLE, CREATE INDEX, CREATE DISTINCT TYPE, CREATE VIEW, COMMENT ON, LABEL ON, or GRANT statements within the CREATE SCHEMA statement is 32766.

Delimiters are not used between the SQL statements.

The owner of the created objects is determined as follows:

• If an AUTHORIZATION clause is specified, the specified authorization ID owns all objects created by the statement.
• If an AUTHORIZATION clause is not specified and SQL names are specified, the owner of all objects created by the statement is the user profile with the same name as the collection-name (if a user profile with that name exists).
• Otherwise, the owner of all objects created by the statement is the user profile (or the group user profile) of the job executing the statement.

Examples

Example 1

Create a collection that has an inventory part table and an index over the part number. Give authority to the schema to the user profile JONES.

   CREATE SCHEMA INVENTORY
 
   CREATE TABLE PART (PARTNO SMALLINT NOT NULL,
                      DESCR  VARCHAR(24),
                      QUANTITY INT)
 
   CREATE INDEX PARTIND ON PART (PARTNO)
 
   GRANT ALL ON PART TO JONES

Example 2

Create a collection using the authorization ID of SMITH. Create a student table that has a comment on the student number column.

   CREATE SCHEMA AUTHORIZATION SMITH
 
   CREATE TABLE SMITH.STUDENT (STUDNBR SMALLINT NOT NULL UNIQUE,
                               LASTNAME CHAR(20),
                               FIRSTNAME CHAR(20),
                               ADDRESS CHAR(50))
 
   COMMENT ON STUDENT (STUDNBR IS 'THIS IS A UNIQUE ID#')