CREATE SCHEMA

Scope

SQL Syntax

  CREATE SCHEMA AUTHORIZATION AuthorizationName
    [TableDefinition
     ViewDefinition
     IndexDefinition
     ProcedureDefinition
     RuleDefinition
     CreateGroup
     AddToGroup
     GrantStatement    ][...]

Parameters

AuthorizationName

specifies the owner of the database objects.

If you have RESOURCE authority, the AuthorizationName must be your DBEUserID, a class name, or an authorization group name to which you belong. You cannot specify a different owner for the objects you create.

If you have DBA authority, the AuthorizationName can be any DBEUserID, class name, or authorization group name. The owner of the objects you create does not have to match the AuthorizationName if the owner has DBA authority.

You must specify an AuthorizationName; there is no default.

TableDefinition

defines a table and automatic locking strategy. For complete syntax, refer to the CREATE TABLE syntax.

ViewDefinition

defines a view of a table, another view, or a combination of tables and views. For complete syntax, refer to the CREATE VIEW syntax.

IndexDefinition

creates an index on one or more columns. For complete syntax, refer to the CREATE INDEX syntax.

ProcedureDefinition

creates a procedure which defines a sequence of SQL statements. For correct syntax, refer to the CREATE PROCEDURE syntax.

RuleDefinition

creates a rule to fire a stored procedure. For complete syntax, refer to the CREATE RULE syntax.

CreateGroup

defines an authorization group. For complete syntax, refer to the CREATE GROUP syntax.

AddToGroup

adds one or more users, authorization groups, or combination of users and authorization groups to an authorization group. For complete syntax, refer to the ADD TO GROUP syntax.

GrantStatement

specifies the type of authorities for a table, view, or module. For complete syntax, refer to the GRANT syntax.

Description

• Note that a comma or semicolon is not allowed between the object definitions in the CREATE SCHEMA syntax.

• You cannot use the following CREATE statements within the CREATE SCHEMA statement:

  • CREATE DBEFILE

  • CREATE DBEFILESET

• You cannot use this statement to add to a schema that already exists. A schema for a given authorization name exists if there are any objects (tables, views, indexes, procedures, rules, or groups) owned by that authorization name.

• When the CREATE SCHEMA statement is part of a procedure, no ProcedureDefinition may be included.

Authorization

Example

   CREATE SCHEMA AUTHORIZATION RecDB
   CREATE PUBLIC TABLE Clubs
                       (ClubName CHAR(15) NOT NULL
                       PRIMARY KEY CONSTRAINT Clubs_PK,
                       ClubPhone SMALLINT,
                       Activity CHAR(18))
                    IN RecFS

   CREATE PUBLIC TABLE Members
                       (MemberName CHAR(20) NOT NULL,
                       Club CHAR(15) NOT NULL,
                       MemberPhone SMALLINT,
                       PRIMARY KEY (MemberName, Club)
                          CONSTRAINT Members_PK,
                       FOREIGN KEY (Club) REFERENCES Clubs (ClubName)
                       CONSTRAINT Members_FK)
                    IN RecFS

   CREATE PUBLIC TABLE Events
                       (SponsorClub CHAR(15),
                       Event CHAR(30),
                       Date DATE DEFAULT CURRENT_DATE,
                       Time TIME,
                       Coordinator CHAR(20),
                       FOREIGN KEY (Coordinator, SponsorClub)
                       REFERENCES Members (MemberName, Club)
                          CONSTRAINT Events_FK)
                    IN RecFS