CREATE SCHEMA statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

An authorization ID that holds DBADM authority can create a schema with any valid schema-name or authorization-name.

An authorization ID that does not hold DBADM authority can only create a schema with a schema-name or authorization-name that matches the authorization ID of the statement.

Syntax

>>-CREATE SCHEMA------------------------------------------------>

>--+-schema-name------------------------------------+----------->
   +-AUTHORIZATION--authorization-name--------------+   
   '-schema-name--AUTHORIZATION--authorization-name-'   

>--+---------------------------+--+--------------------------+-><
   '-DATA CAPTURE--+-NONE----+-'  | .----------------------. |   
                   '-CHANGES-'    | V                      | |   
                                  '---schema-SQL-statement-+-'   

Description

schema-name

An identifier that names the schema. The name must not identify a schema already described in the catalog (SQLSTATE 42710). The name cannot begin with 'SYS' (SQLSTATE 42939). The owner of the schema is the authorization ID that issued the statement.

AUTHORIZATION authorization-name

Identifies the user who is the owner of the schema. The value of authorization-name is also used to name the schema. The authorization-name must not identify a schema already described in the catalog (SQLSTATE 42710).

schema-name AUTHORIZATION authorization-name

Identifies a schema called schema-name, whose owner is authorization-name. The schema-name must not identify a schema already described in the catalog (SQLSTATE 42710). The schema-name cannot begin with 'SYS' (SQLSTATE 42939).

DATA CAPTURE

Indicates whether extra information for data replication is to be written to the log. The default is determined based on the value of database configuration parameter dft_schemas_dcc. If the value is "Yes" the default is CHANGES, otherwise the default is NONE.

NONE

Indicates that no extra information for data replication will be logged.

CHANGES

Indicates that extra information regarding SQL changes to this schema will be written to the log. This option is required if this schema will be replicated and a replication capture program is used to capture changes for this schema from the log.

schema-SQL-statement

SQL statements that can be included as part of the CREATE SCHEMA statement are:

• CREATE TABLE statement, excluding typed tables and materialized query tables
• CREATE VIEW statement, excluding typed views
• CREATE INDEX statement
• COMMENT statement
• GRANT statement

Notes

• The owner of the schema is determined as follows:
  • If an AUTHORIZATION clause is specified, the specified authorization-name is the schema owner
  • If an AUTHORIZATION clause is not specified, the authorization ID that issued the CREATE SCHEMA statement is the schema owner.
• The schema owner is assumed to be a user (not a group).
• When the schema is explicitly created with the CREATE SCHEMA statement, the schema owner is granted CREATEIN, DROPIN, and ALTERIN privileges on the schema with the ability to grant these privileges to other users.
• The definer of any object created as part of the CREATE SCHEMA statement is the schema owner. The schema owner is also the grantor for any privileges granted as part of the CREATE SCHEMA statement.
• Unqualified object names in any SQL statement within the CREATE SCHEMA statement are implicitly qualified by the name of the created schema.
• If the CREATE statement contains a qualified name for the object being created, the schema name specified in the qualified name must be the same as the name of the schema being created (SQLSTATE 42875). Any other objects referenced within the statements may be qualified with any valid schema name.
• It is recommended not to use "SESSION" as a schema name. Since declared temporary tables must be qualified by "SESSION", it is possible to have an application declare a temporary table with a name identical to that of a persistent table. An SQL statement that references a table with the schema name "SESSION" will resolve (at statement compile time) to the declared temporary table rather than a persistent table with the same name. Since an SQL statement is compiled at different times for static embedded and dynamic embedded SQL statements, the results depend on when the declared temporary table is defined. If persistent tables, views or aliases are not defined with a schema name of "SESSION", these issues do not require consideration.
• Setting the DATA CAPTURE attribute at the schema level causes newly created tables to inherit the DATA CAPTURE attribute from the schema if one is not specified at the table level.

Examples

• Example 1:  As a user with DBADM authority, create a schema called RICK with the user RICK as the owner.

     CREATE SCHEMA RICK AUTHORIZATION RICK

• Example 2:  Create a schema that has an inventory part table and an index over the part number. Give authority on the table to user JONES.

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

• Example 3:  Create a schema called PERS with two tables that each have a foreign key that references the other table. This is an example of a feature of the CREATE SCHEMA statement that allows such a pair of tables to be created without the use of the ALTER TABLE statement.

     CREATE SCHEMA PERS
  
       CREATE TABLE ORG (DEPTNUMB  SMALLINT NOT NULL,
                          DEPTNAME VARCHAR(14),
                          MANAGER  SMALLINT,
                          DIVISION VARCHAR(10),
                          LOCATION VARCHAR(13),
                          CONSTRAINT PKEYDNO
                            PRIMARY KEY (DEPTNUMB),
                          CONSTRAINT FKEYMGR
                            FOREIGN KEY (MANAGER)
                            REFERENCES STAFF (ID) )
  
       CREATE TABLE STAFF (ID        SMALLINT NOT NULL,
                           NAME     VARCHAR(9),
                           DEPT     SMALLINT,
                           JOB      VARCHAR(5),
                           YEARS    SMALLINT,
                           SALARY   DECIMAL(7,2),
                           COMM     DECIMAL(7,2),
                           CONSTRAINT PKEYID
                             PRIMARY KEY (ID),
                           CONSTRAINT FKEYDNO
                             FOREIGN KEY (DEPT)
                             REFERENCES ORG (DEPTNUMB) )