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.
NOTE
In Windows, instances do not really have an instance owner, but the name of the instance, i.e., DB2, will be used in place of the instance owner's ID.
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.
NOTE
The sqldbdir directory contains the database directory, i.e., a listing of all databases that are in the drive / path.