CREATE SCHEMA

 

Function

CREATE SCHEMA creates an SQL schema which serves as a logical container for the subsequent creation of catalog resident objects such as tables and views.

 

Syntax

 

 

create_schema.bmp

 

schema_name

A valid schema identifier representing the schema to be created.

user_identifier

A valid user identifier of an existing user.

directory_identifier

A valid directory to be used for file creation for the data source used in association with this schema.

 

Description

CREATE SCHEMA causes a schema to be entered into the catalog. The name of the schema is:

  • explicitly provided as the schema identifier in the CREATE SCHEMA statement or

  • derived from the user identifier, if the schemas identifier has been omitted.

A schema must have an owner. This owner can be explicitly specified in the AUTHORIZATION clause as a user identifier. If the owner is not explicitly specified, the user identifier is derived from the user identifier of the statement executor. In both cases, the resulting user identifier must be the same as a known server user.

If the statement is invoked statically, any user identifier does not have to exist in the catalog until statement execution.

Limitations

The resulting schema identifier must be unique within the catalog.

The user identifier must be equal to an already defined user identifier as defined using a CREATE USER statement.

The contents of dir_identifier will be ignored if the database is not a ROSIO data source.

Note: The CREATE SCHEMA statement can only be executed by the DBA. The DBA must be a member of the group CONNXCDDAdministrators:
 

image3.gif
 

Caution: This statement is not subject to transaction logic. An implicit COMMIT is performed after successful execution of this statement. If an error is detected during execution of this statement, an implicit ROLLBACK is performed. Therefore, before executing this statement, it is strongly recommended to complete any open transaction containing INSERT, UPDATE and/or DELETE statements by issuing an explicit COMMIT or ROLLBACK statement.

 

ANSI Specifics

The CREATE SCHEMA statement provided by CONNX is a reduced version of that specified in the SQL-2 standard. In particular, it is not possible to specify the object that belongs to a schema in this statement (i.e., base tables, views and constraints).

 

Example

The following example creates a schema with the name Wiltshire and assign it to the owner TIM.

 

CREATE SCHEMA Wiltshire AUTHORIZATION 'TIM' ;
 

The following example creates a schema with the name TIM and assigns it to the owner TIM.

 

CREATE SCHEMA AUTHORIZATION 'TIM' ;