Using CREATE DATABASE

A database is a container that holds all parts of a data model. These parts include not only the tables but also views, indexes, synonyms, and other objects that are associated with the database. You must create a database before you can create anything else.

When the database server creates a database, it sets up records that show the existence of the database and its mode of logging. These records are not visible to operating-system commands because the database server manages disk space directly.

Avoiding Name Conflicts

Normally, only one copy of the database server is running on a computer, and the database server manages the databases that belong to all users of that computer. The database server keeps only one list of database names. The name of your database must be different from that of any other database that the database server manages. (It is possible to run more than one copy of the database server. You can create more than one copy of the database server, for example, to create a safe environment for testing apart from the operational data. In this case, be sure that you are using the correct database server when you create the database and again when you access it later.)

Selecting a Dbspace

The database server lets you create the database in a particular dbspace. A dbspace is a named area of disk storage. Ask your database server administrator whether you should use a particular dbspace. You can put a database in a separate dbspace to isolate it from other databases or to locate it on a particular disk device. For information about dbspaces and their relationship to disk devices, see your IBM Informix: Administrator's Guide. For information about how to fragment the tables of your database across multiple dbspaces, see Building a Dimensional Data Model.

Some dbspaces are mirrored (duplicated on two disk devices for high reliability). You might put your database in a mirrored dbspace if its contents are of exceptional importance.

Choosing the Type of Logging

To specify a logging or nonlogging database, use the CREATE DATABASE statement. The database server offers the following choices for transaction logging:

• No logging at all. This is not a recommended choice. If you lose the database because of a hardware failure, you lose all data alterations since the last backup.

  CREATE DATABASE db_with_no_log

  When you do not choose logging, BEGIN WORK and other SQL statements that are related to transaction processing are not permitted in the database. This situation affects the logic of programs that use the database.

  Extended Parallel Server

  Extended Parallel Server does not support nonlogging databases. However, the database server does support nonlogging tables. For more information, see Configuring the Database Server to Use Distributed Queries.

  End of Extended Parallel Server
• Regular (unbuffered) logging. This choice is best for most databases. In the event of a failure, you lose only uncommitted transactions.

  CREATE DATABASE a_logged_db WITH LOG

• Buffered logging. If you lose the database, you lose few or possibly none of the most recent alterations. In return for this small risk, performance during alterations improves slightly.

  CREATE DATABASE buf_log_db WITH BUFFERED LOG

  Buffered logging is best for databases that are updated frequently (so that speed of updating is important), but you can re-create the updates from other data in the event of a failure. Use the SET LOG statement to alternate between buffered and regular logging.

• ANSI-compliant logging. This logging is the same as regular logging, but the ANSI rules for transaction processing are also enforced. For more information, refer to Using ANSI-Compliant Databases.

  CREATE DATABASE std_rules_db WITH LOG MODE ANSI

  The design of ANSI SQL prohibits the use of buffered logging. When you create an ANSI-compliant database, you cannot turn off transaction logging.