DB2 Version 9.7 for Linux, UNIX, and Windows

Creating databases

You create a database using the CREATE DATABASE command. To create a database from a client application, call the sqlecrea API.

About this task

When you create a database, each of the following tasks are done for you:

Setting up of all the system catalog tables that are needed by the database
Allocation of the database recovery log
Creation of the database configuration file and the default values are set
Binding of the database utilities to the database

Before you begin

It is important to plan your database, keeping in mind the contents, layout, potential growth, and how it will be used before you create it. Once it has been created and populated with data, changes can be made. However depending on how you set up the database initially, it will likely require more effort and make your data unavailable for use while the changes are being made.

The following database privileges are automatically granted to PUBLIC: CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA, and SELECT on the system catalog views. However, if the RESTRICTIVE option is present, no privileges are automatically granted to PUBLIC. For more information on the RESTRICTIVE option, see the CREATE DATABASE command.

Procedure

To create a database from a client application, call the sqlecrea API.
To create a database using the command line processor, issue the CREATE DATABASE command.
For example, the following command creates a database called PERSON1, in the default location, with the associated comment "Personnel DB for BSchiefer Co".
```
CREATE DATABASE personl
  WITH "Personnel DB for BSchiefer Co"
```
To create a database using IBM® Data Studio, right-click the instance on which you want to create the database and select the task assistant to the create it.

What to do next

Configuration Advisor

The Configuration Advisor helps you to tune performance and to balance memory requirements for a single database per instance by suggesting which configuration parameters to modify and providing suggested values for them. The Configuration Advisor is automatically invoked by default when you create a database.

You can override this default so that the configuration advisor is not automatically invoked by using one of the following methods:

Issue the CREATE DATABASE command with the AUTOCONFIGURE APPLY NONE parameter.
Set the DB2_ENABLE_AUTOCONFIG_DEFAULT registry variable to NO:
```
db2set DB2_ENABLE_AUTOCONFIG_DEFAULT=NO
```
However, if you specify the AUTOCONFIGURE parameter with the CREATE DATABASE command, the setting of this registry variable is ignored.

See Automatic features for other features that are enabled by default when you create a database.

Event Monitor

At the same time a database is created, a detailed deadlocks event monitor is also created. As with any monitor, there is some overhead associated with this event monitor. If you do not want the detailed deadlocks event monitor, then the event monitor can be dropped using the command:

 DROP EVENT MONITOR db2detaildeadlock

To limit the amount of disk space that this event monitor consumes, the event monitor deactivates, and a message is written to the administration notification log, once it has reached its maximum number of output files. Removing output files that are no longer needed allows the event monitor to activate again on the next database activation.

Remote databases

You have the ability to create a database in a different, possibly remote, instance. To create a database at another (remote) database partition server, you must first attach to that server. A database connection is temporarily established by the following command during processing:

CREATE DATABASE database_name AT DBPARTITIONNUM options

In this type of environment you can perform instance-level administration against an instance other than your default instance, including remote instances. For instructions on how to do this, see the db2iupdt (update instance) command.

Database code pages

By default, databases are created in the UTF-8 (Unicode) code set.

To override the default code page for the database, it is necessary to specify the desired code set and territory when creating the database. See the CREATE DATABASE command or the sqlecrea API for information on setting the code set and territory.