The Default Database

The CREATE DATABASE command allows a user to specify the drive or directory on which to create the database, depending on the operating system. The Linux and UNIX operating systems allow a user to specify the directory in which to create the database. On the Windows operating system, a user can specify only the drive on which to create the database. If no drive or directory is specified, the database will be created on the path specified by the DFTDBPATH instance (database manager) configuration parameter.

On the drive or directory specified in the CREATE DATABASE command, DB2 will create a series of subdirectories, as shown in Figure 1.2. The first subdirectory is named after the instance owner for the instance in which the database was created. Under this subdirectory, DB2 will create a directory that indicates which database partition the database was created in. For a nonpartitioned database, the directory will be NODE0000. For a partitioned database, the directory will be named NODExxxx where xxxx will be the database instance's four-digit partition number, as designated in the db2nodes.cfg file. For example, for partition number 43, this directory would be NODE0043.

Because more than one database can be created on the same drive or directory, each database must have its own unique subdirectory. Under the NODExxxx directory, there will be an SQLxxxxx directory for every database that was created on the drive/directory. For example, there are two databases, MYDB and SAMPLE, that were both created on the C: drive on Windows, so there will be two directories: SQL00001 and SQL00002.

To determine under which directory the database was created, enter the command LIST DATABASE DIRECTORY ON C:. This will produce output like the following:

Database 1 entry:

  Database alias                           = MYDB
  Database name                            = MYDB
  Database directory                       = SQL00002
  Database release level                   = a.00
  Comment
  Directory entry type                     = Home
  Catalog database partition number        = 0
  Database partition number                = 0

Database 2 entry:

  Database alias                           = SAMPLE
  Database name                            = SAMPLE
  Database directory                       = SQL00001
  Database release level                   = a.00
  Comment                                  =
  Directory entry type                     = Home
  Catalog database partition number        = 0
  Database partition number                = 0

In the example above, the database SAMPLE would have been created in the SQL00001 directory, and the database MYDB would have been created in the SQL00002 directory under the NODExxxx directory.

Under the database's SQL0000x directory, DB2 will create one directory for each of the three default table spaces, unless the table spaces were defined to use different containers in the CREATE DATABASE command.

By default, the system catalog table space will use the directory SQLT0000.0, the system temporary table space will use the directory SQLT0001.0, and the default user table space (USERSPACE1) will use the directory SQLT0002.0.

There is also a subdirectory named SQLOGDIR to hold the database log files. This location can be changed once the database has been created.

Given the following command, execute in the instance named db2inst1.

create database sample on /database

On the Linux or UNIX server where database partition 0 is defined, the following directory structures are created:

/database/db2inst1/NODE0000/sqldbdir
/database/db2inst1/NODE0000/SQL00001

On the server where database partition 1 is defined, the following directory structures are created:

/database/db2inst1/NODE0001/sqldbdir
/database/db2inst1/NODE0001/SQL00001

These directories would be created as illustrated in Figure 1.3.

If a second database is created in the same instance (i.e., db2inst1) on the same path using the command:

create database sample on /database

the directory structure would then look like Figure 1.4.