CREATE DATABASE statement

Use the CREATE DATABASE statement to create a new database.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

CREATE DATABASE [IF NOT EXISTS] database [ INdbspace ] [ WITH { [ BUFFERED ] LOG | LOG MODE ANSI } ] { NLSCASE SENSITIVE | [NLSCASE INSENSITIVE] }
Element Description Restrictions Syntax
database Name that you declare here for the new database that you are creating Must be unique among names of databases of the database server Database Name
dbspace The dbspace to store the data for this database; default is the root dbspace Must exist

Cannot be the name of a dbspace that is dedicated to a tenant database.

 Identifier

Usage

This statement is an extension to ANSI-standard syntax. (The ANSI/ISO standard for the SQL language does not specify any syntax for construction of a database, the process by which a database comes into existence and has its name declared.)

Important: If you plan to use UTF-8 character encoding, including the Chinese GB18030-2000 code set, you must set the GL_USEGLU environment variable before you create the database.

The database that CREATE DATABASE specifies becomes the current database.

You cannot create a tenant database with the CREATE DATABASE statement. To create a tenant database, run the admin() or task() SQL administration API command with the tenant create argument.

If the DBCREATE_PERMISSION configuration parameter is not set, any user can create a database. If the configuration file includes one or more DBCREATE_PERMISSION specifications, however, only the specified users can create databases. Whether DBCREATE_PERMISSION is set, user informix can use the CREATE DATABASE statement. For additional information about how to set the DBCREATE_PERMISSION parameter to control which users can create new databases.

The database name that you declare must be unique within the database server environment in which you are working. The database server creates the system catalog tables that describe the structure of the new database.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a database of the specified name exists among the databases that are managed by the database server instance to which you are connected.

When you create a database, you alone can access it. It remains inaccessible to other users until you, as DBA, grant database privileges by running the GRANT statement.

If a previous CONNECT statement established an explicit connection to a database, and that connection is still your current connection, you cannot use the CREATE DATABASE statement (nor any SQL statement that creates an implicit connection) until after you use the DISCONNECT statement to close the explicit connection.

In Informix® ESQL/C, the CREATE DATABASE statement cannot appear in a multistatement PREPARE operation.

The SQL_LOGICAL_CHAR configuration parameter setting for the database server instance to which you are connected is recorded in the system catalog of the new database. The SQL_LOGICAL_CHAR configuration parameter controls the expansion of size specifications in declarations of built-in character data types. This setting cannot be changed, and persists until the database is dropped, even if the Informix® instance that manages the database is stopped and restarted with a new SQL_LOGICAL_CHAR value. The flags column of the systables system catalog table encodes the SQL_LOGICAL_CHAR setting for the database.

If you do not specify a dbspace, the database server creates the system catalog tables in the root dbspace by default. However, if you enabled the automatic location of databases, databases are created by default in the dbspace that is chosen by the server. To enable the automatic location of databases, set the AUTOLOCATE configuration parameter or session environment variable to a positive integer.

The following statement creates the vehicles database in the root dbspace or in a dbspace that is chosen by the server, depending on whether automatic location is enabled: 
CREATE DATABASE vehicles;
Because the example above includes no logging specification and no NLSCASE specification, then by default
  • the vehicles database cannot support transaction logging,
  • and if its locale uses a code set that distinguishes between uppercase and lowercase letters, the database is case-sensitive for all built-in character data types.
The following statement defines the vehicles database in the research dbspace: 
CREATE DATABASE vehicles IN research;

But if no DROP DATABASE statement dropped existing the vehicles database that the first example created, the second example fails with an error, and no new database is created, because the identifier of the vehicles database is not unique for the database server instance.