CREATE SCHEMA

Use the CREATE SCHEMA command to create a schema and to create tables or views or grant privileges in that schema.

The Netezza Performance Server system must be configured to support multiple schemas. For more information on enabling multiple schema support, see the IBM® Netezza® System Administrator’s Guide.

Syntax

Syntax for creating a new schema:
CREATE SCHEMA [<database_name>.]<schema_name> [ AUTHORIZATION <user> ] 
[ PATH '<schema_path>' ] [ <schema_element_clause> ] [ DATA_VERSION-RETENTION_TIME <number-of-days> ]

Inputs

The CREATE SCHEMA command takes the following inputs:
Table 1. CREATE SCHEMA inputs
Input Description
<database_name> The name of the database in which to add the schema. The database name is required when the schema is not in the current database.
<schema_name> The name of the new schema.
AUTHORIZATION TO <user> The authorization user, or owner, of the schema. The name must already exist in the system.
PATH <schema_path> The list of schema names that the system searches through to resolve unqualified routine names, such as the names of functions, stored procedures, and user-defined objects such as functions, aggregates, and libraries. A schema name that is not fully qualified (that is, that does not include a database prefix) is in the current database.
<schema_element_clause> Specifies one or more CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, or GRANT commands that you can specify for the new schema. The GRANT commands can refer to the tables, views, or sequences that you created earlier in the clause, any existing objects, or object classes. If any one of the SQL commands in the clause fails, the CREATE SCHEMA command fails and is rolled back.
DATA_VERSION_RETENTION_TIME

The sch schema that is created has the specified DATA_VERSION_RETENTION_TIME or gets the current value of the property from the database if nothing is specified. Schemas that are implicitly created (for example, for the database owner) get the current value of the property from the database.

In either case, the value of the property on a schema determines the default value that is inherited by a subsequent CREATE TABLE command in that schema that does not explicitly specify this property.

The maximum allowed value is 92 days, which is the maximum number of days in a calendar quarter.

Outputs

The CREATE SCHEMA command produces the following outputs:
Table 2. CREATE SCHEMA outputs
Output Description
CREATE SCHEMA The command was successful.

Privileges

You must be the admin user, the owner of the database, or have the Create Schema privilege. If you specify an authorization user or database you must also have the List privilege for the corresponding object.