Creating databases

You create a database using the CREATE DATABASE command. To create a database from a client application, call the sqlecrea API. All databases are created with the default storage group IBMSTOGROUP, unless you specify otherwise. Automatic storage managed table spaces use storage groups for their storage definitions.

Before you begin

The database manager must be running. Use the db2start command to start the database manager.

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. After it has been created and populated with data, changes can be 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 about the RESTRICTIVE option, see the CREATE DATABASE command.

Restrictions

Storage paths cannot be specified using relative path names; you must use absolute path names. The storage path can be up to 175 characters long.
On Windows operating systems, do not create a database under an instance profile folder. The instance profile is copied and moved during an upgrade. Because database files tend to be large, moving them during an upgrade can take a long time and result in upgrade failure.
On Windows operating systems, the database path must be a drive letter only, unless the DB2_CREATE_DB_ON_PATH registry variable is set to YES.
If you do not specify a database path using the DBPATH ON clause of the CREATE DATABASE command, the database manager uses the first storage path specified for the ON clause for the database path. (On Windows operating systems, if this clause is specified as a path, and if the DB2_CREATE_DB_ON_PATH registry variable is not set to YES, you receive a SQL1052N error message.) If no ON clause is specified, the database is created on the default database path that is specified in the database manager configuration file (dftdbpath parameter). The path is also used as the location for the single storage path associated with the database.
For partitioned databases, you must use the same set of storage paths on each database partition (unless you use database partition expressions).
Database partition expressions are not valid in database paths, whether you specify them explicitly by using the DBPATH ON clause of the CREATE DATABASE command, or implicitly by using a database partition expression in the first storage path.
A storage group must have at least one storage path associated with it.

Note: Although, you can create a database specifying the AUTOMATIC STORAGE NO clause, the AUTOMATIC STORAGE clause is deprecated and might be removed from a future release.

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

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

Example

Example 1: Creating a database on a UNIX or Linux® operating system:

To create a database named TESTDB1 on path /DPATH1 using /DATA1 and /DATA2 as the storage paths defined to the default storage group IBMSTOGROUP, use the following command:

   CREATE DATABASE TESTDB1 ON '/DATA1','/DATA2' DBPATH ON '/DPATH1'

Example 2: Creating a database on a Windows operating system, specifying both storage and database paths:

To create a database named TESTDB2 on drive D:, with storage on E:\DATA, use the following command:

   CREATE DATABASE TESTDB2 ON 'E:\DATA' DBPATH ON 'D:'

In this example, E:\DATA is used as both the storage path defined to the default storage group IBMSTOGROUP and the database path.

Example 3: Creating a database on a Windows operating system, specifying only a storage path:

To create a database named TESTDB3 with storage on drive F:, use the following command:

   CREATE DATABASE TESTDB3 ON 'F:'

In this example, F: is used as both the storage path defined to the default storage group IBMSTOGROUP and the database path.

If you specify a directory name such as F:\DATA for the storage path, the command fails, because:

When DBPATH is not specified, the storage path -- in this case, F:\DATA -- is used as the database path
On Windows, the database path can only be a drive letter (unless you change the default for the DB2_CREATE_DB_ON_PATH registry variable from NO to YES).

If you want to specify a directory as the storage path on Windows operating systems, you must also include the DBPATH ON drive clause, as shown in Example 2.

Example 4: Creating a database on a UNIX or Linux operating system without specifying a database path:

To create a database named TESTDB4 with storage on /DATA1 and /DATA2, use the following command:

   CREATE DATABASE TESTDB4 ON '/DATA1','/DATA2'

In this example, /DATA1 and /DATA2 are used as the storage paths defined to the default storage group IBMSTOGROUP and /DATA1 is the database path.

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.

Event Monitor

At the same time a database is created, a detailed deadlocks event monitor is also created. As with any monitor, there is extra processing time and resources associated with this event monitor. If you do not want the detailed deadlocks event monitor, then the event monitor can be dropped by 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 can 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 required code set and territory when creating the database. See the CREATE DATABASE command or the sqlecrea API for information about setting the code set and territory.