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.

Syntax

Syntax for creating a new schema:

CREATE SCHEMA [<database_name>.]<schema_name> [ AUTHORIZATION <user> ] 
[ PATH '<schema_path>' ] [ DATA_VERSION_RETENTION_TIME <number-of-days> ] [ <schema_element_clause> ]

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	Creates a schema with a retention time. You must specify DATA_VERSION_RETENTION_TIME if you want to run time travel queries. The default value is 0. The maximum allowed value is 99 days. The schema that is created has the specified DATA_VERSION_RETENTION_TIME or gets the current value of the property from the database if none 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. If retention time is set to a nonzero value, the schema is a temporal schema. Historical rows that go back the number of days that are specified by DATA_VERSION_RETENTION_TIME are available for time travel queries. For more information about time travel and DATA_VERSION_RETENTION_TIME, see Getting started with time travel.

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.

watsonx.data considerations

By default, you cannot CREATE SCHEMA in a data lake remote database.