CREATE DATABASE

Use the CREATE DATABASE command to create and become the owner of a database.

Syntax

Syntax for creating a database:

CREATE DATABASE <db_name> 
  [ WITH DEFAULT CHARACTER SET LATIN9 COLLATION BINARY ]
  [ COLLECT HISTORY { ON | OFF | DEFAULT ]
  [ DATA_VERSION_RETENTION_TIME <number-of-days> ]

Inputs

The CREATE DATABASE command takes the following inputs:

Table 1. CREATE DATABASE inputs
Input	Description
<db_name>	The name of the database to be created. This name must be unique among global objects, that is, it cannot be shared by another database, or by any user, group, or scheduler rule.
DEFAULT CHARACTER SET	The default character set is LATIN9. This is the default. Do not specify other values.
COLLATION	The collation is BINARY. This is the default. Do not specify other values.
COLLECT HISTORY	Whether history data is to be collected for sessions attached to this database. ON History is collected only if the user is a member of at least one group for which COLLECT HISTORY is set to ON. This is the default. OFF History is not collected for the database. DEFAULT History is collected for the database only if the user is a member of at least one group for which COLLECT HISTORY is set to ON and if one of the following criteria apply: The user is not a member of any user group. All the user groups of which the user is a member have COLLECT HISTORY set to DEFAULT. The user is a member of at least one user group that has COLLECT HISTORY set to ON.
DATA_VERSION_RETENTION_TIME	The `dbname` database that is created has the specified DATA_VERSION_RETENTION_TIME or gets the current value of the property from the system default if nothing is specified. In either case, the value of the property on a database determines the default value inherited by a subsequent CREATE SCHEMA command in that database 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. You can set this property at table, schema, database, and system level. The default value at all levels on new Netezza Performance Server systems and systems upgraded from a release that did not have the time travel feature is 0. A table with a 0 retention time interval is not a temporal table and does not support time travel queries. The value of the property on a table specifies the maximum number of days that historical (deleted) rows are potentially visible to temporal queries against that table. Current (not yet deleted) rows are not historical and are always retained and visible. If you set the property on a system, database, or schema allows, users can specify the default value that is inherited by newly created objects at the next level down (database, schema, or table, respectively). By setting the property on a system, database, or schema, you do not have to set the property on a large number of tables if the same value is desired on all of those tables. If you want to, you can set the property explicitly to a different value for any table.

Outputs

The CREATE DATABASE command has the following outputs:

Table 2. CREATE DATABASE outputs
Output	Description
`CREATE DATABASE`	The command completed successfully.
`ERROR: User 'username' is not allowed to create/drop databases.`	You do not have the necessary privilege to create a database.
`ERROR: Createdb: database 'name' already exists.`	A database with the specified name already exists.
`ERROR: Create database: may not be called in a transaction block.`	An explicit transaction block is in progress. You must finish the transaction before you can create a database.
`ERROR: Unable to create database directory 'path'.`	One of the following errors occurred: You have insufficient permissions for the data directory. You must have access to the location. A disk is full. There is another file system problem.
`ERROR: Could not initialize database directory.`

Privileges

You must be the admin user or your user account must have the Create Database privilege.

Usage

The following example provides sample usage.

Create a database with the name customers for which history data is not to be collected:
```
MYDB.SCH1(USER)=> CREATE DATABASE customers COLLECT HISTORY OFF;
```

watsonx.data considerations

You can add only one data source for a data lake database. If you require another data source, you must create another data lake database.

You cannot ALTER default schema for a data lake database.