Informix Guide to SQL: Syntax SQL Statements

Use the CREATE DATABASE statement to create a new database.

This statement is an extension to ANSI-standard syntax. The ANSI standard does not provide any syntax for the construction of a database, that is how a database comes into existence.

The database that you create becomes the current database.

The database name that you use 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 database.

When you create a database, you alone have access to it. The database remains inaccessible to other users until you, as DBA, grant database privileges. For information on how to grant database privileges, see GRANT.

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

If you do not specify the dbspace, the database server creates the system catalog tables in the root dbspace. The following statement creates the vehicles database in the root dbspace:

CREATE  DATABASE  vehicles 

The following statement creates the vehicles database in the research dbspace:

CREATE  DATABASE  vehicles IN research 

The logging options of the CREATE DATABASE statement determine the type of logging that is done for the database.

In the event of a failure, the database server uses the log to re-create all committed transactions in your database.

If you do not specify the WITH LOG option, you cannot use transactions or the statements that are associated with databases that have logging (BEGIN WORK, COMMIT WORK, ROLLBACK WORK, SET LOG, and SET ISOLATION).

If you are using Enterprise Decision Server, the CREATE DATABASE statement always creates a database with unbuffered logging. The database server ignores any logging specifications included in a CREATE DATABASE statement.

The following example creates a database that uses a buffered log:

CREATE  DATABASE  vehicles  WITH  BUFFERED  LOG 

If you use a buffered log, you marginally enhance the performance of logging at the risk of not being able to re-create the last few transactions after a failure. (See the discussion of buffered logging in the Informix Guide to Database Design and Implementation.)

When you use the LOG MODE ANSI option in the CREATE DATABASE statement, the database that you create is an ANSI-compliant database. The following example creates an ANSI-compliant database:

CREATE  DATABASE  employees  WITH  LOG  MODE  ANSI 

ANSI-compliant databases are set apart from databases that are not ANSI-compliant by the following features:

• All statements are automatically contained in transactions.

All databases use unbuffered logging.

• Owner-naming is enforced.

You must use the owner name when you refer to each table, view, synonym, index, or constraint unless you are the owner.

• For databases, the default isolation level is repeatable read.
• Default privileges on objects differ from those in databases that are not ANSI-compliant.

Users do not receive PUBLIC privilege to tables and synonyms by default.

Other slight differences exist between databases that are ANSI-compliant and those that are not. These differences are noted as appropriate with the related SQL statement. For a detailed discussion of the differences between ANSI-compliant databases and databases that are not ANSI-compliant, see the Informix Guide to Database Design and Implementation.

Creating an ANSI-compliant database does not mean that you get ANSI warnings when you run the database. You must use the -ansi flag or the DBANSIWARN environment variable to receive warnings.

For additional information about -ansi and DBANSIWARN, see the Informix Guide to SQL: Reference.

Related statements: CLOSE DATABASE, CONNECT, DATABASE, DROP DATABASE

For discussions of how to create a database and of ANSI-compliant databases, see the Informix Guide to Database Design and Implementation.