CREATE DATABASE statement

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

Syntax

1  CREATE DATABASE? IF NOT EXISTS database?  IN dbspace ?  WITH
2.1 ?  BUFFERED LOG
2.1 LOG MODE ANSI
2.1! NLSCASE SENSITIVE
2.1? NLSCASE INSENSITIVE

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.)

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 , 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 HCL OneDB™ 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.

CREATE DATABASE vehicles; 

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.