Section 8.2. Databases: Logical and Physical Storage of Your Data

8.2. Databases: Logical and Physical Storage of Your Data

This section discusses how DB2 logically and physically creates databases.

8.2.1. Creating a Database

As mentioned in Chapter 2, DB2 at a Glance: The Big Picture, you used the CREATE DATABASE command to create a database. The basic syntax of the command is

 CREATE DATABASE database name ON drive/path

The database name:

Can be a maximum of eight characters long
Cannot contain all numbers
Cannot start with a number
Cannot contain spaces
Cannot already have been used within the same DB2 instance

DB2 creates the database on the drive or path specified by the DFTDBPATH Database Manager Configuration parameter. On Windows this will be a drive, and on Linux and UNIX this will be the path to a file system. By default, the DFTDBPATH configuration parameter is the drive where DB2 is installed on Windows. On Linux and UNIX it is the instance owner's home directory. When you do specify the drive or the path for the database, keep the following in mind.

It cannot be a LAN drive, an NFS mounted file system, or a General Parallel File System (GPFS).
The file system or drive cannot be a read-only device.
The instance owner must have write permission on the drive or file system.
There must be sufficient space available on the drive or file system to hold at least the system catalogs.

In addition, in the CREATE DATABASE command you can optionally specify

The database partition number for the catalog table space (multi-partition environments).
The definition of the temporary and default user table spaces if you do not want to use the default locations.
The code set and territory allow you to specify the character set that you want DB2 to use to store your data and return result sets.
The collating sequence lets you specify how DB2 should sort data when you create indexes or use the SORT or ORDER BY clauses in select statements.
Whether you want to automatically configure the instance for the specified workload.

When a database is created using the default syntax of the CREATE DATABASE command, several objects are created.

The partition group IBMCATGROUP, which contains
- The table space SYSCATSPACE (catalog table space), which contains the DB2 catalog tables and views
The partition group IBMTEMPGROUP, which contains
- The table space TEMPSPACE1 (system temporary table space)
The partition group IBMDEFAULTGROUP, which contains
- The table space USERSPACE1 (user table space)
The buffer pool IBMDEFAULTBP
A database configuration file

Figure 8.2 below shows these dafault objects that are created when you create a database.

Figure 8.2. A database with default database objects created

When you create a database you can specify different locations and table space types for the temporary and user table spaces.

 CREATE DATABASE sales ON /data     TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ('/temp')     USER TABLESPACE MANAGED BY SYSTEM USING ('/userspc')

The example above lets the catalog table space default to a directory under the database path (/data). You are also specifying to use SMS table spaces for the user and temporary table space, but that the temporary table space will use the file system /temp, and the user table space will use the file system /userspc.

You can also create the user table space as a DMS table space:

 create database sales on /data     temporary tablespace managed by system using ('/temp')     user tablespace managed by database using (file '/userspc/cont1' 40M)

SMS and DMS table spaces are discussed in detail in section 8.4, Table Spaces.

The CREATE DATABASE command in a multi-partition environment automatically takes the contents of the database partition configuration file (db2nodes.cfg) into consideration. The partition where you issue the CREATE DATABASE command becomes the catalog partition for the database, and the system catalog tables for this database will be created on that partition. If you do not explicitly connect to a database partition or server, the database will be created with the system catalogs on the first partition in the db2nodes.cfg file.

8.2.2. The Default Database Structure

When you create a database using default values, DB2 automatically creates a set of directories that correspond to the objects it creates by default. Figure 8.3 shows the default directory structure that is created.

Figure 8.3. The default database structure

In Figure 8.3, NODE0000 represents the partition number of the database. In a multi-partition environment, there will be one NODExxxx directory per partition, where xxxx matches the partition number specified in the first column of the db2nodes.cfg file.

SQL00001 represents the directory where the first database created resides. This is a unique directory name. If you create another database on the same server in the same instance using the default drive/path, DB2 creates a directory SQL00002 for this database. Subsequent databases created in the same manner are each stored in similar directories. Note that if you drop a database and later create a new database on the same drive or path, DB2 will reuse the directory name from the database that was dropped.

In Figure 8.3 you can see that underneath the SQLxxxxx directory, DB2 also creates a number of additional subdirectories. Table 8.1 explains DB2's usage of each of these directories.

Table 8.1. Description of the Subdirectories of SQLxxxxx
Directory Name	Description
DB2EVENT	The event monitor output directory.
SQLLOGDIR	The default directory for the transaction logs.
SQLT0000.0	The directory for table space SYSCATSPACE (the catalog table space).
SQLT0001.0	The directory for table space TEMPSPACE1 (the system temporary table space).
SQLT0002.0	The directory for table space USERSPACE1 (the default user table space).

8.2.3. Database Creation Examples

In this section we provide two examples of how to create a database. The first example is for a single-partition environment, and the second example is for a multi-partition environment.

8.2.3.1 Creating a Database in a Single-Partition Environment

Let's say you are working on a single-partition DB2 environment running on a Windows server and the DB2 instance name you created is myinst. If you issue the command:

 CREATE DATABASE sales ON E:

several directories will be created on the E: drive as shown in Figure 8.4.

Figure 8.4. Directories created when a database is created

Continuing with the example, you can create two additional databases, test and prod, using the following commands:

 CREATE DATABASE test ON E: CREATE DATABASE prod ON E:

Figure 8.5 shows the additional directories these commands create. Table 8.2 shows the database name and the directory that DB2 used when the database was created.

Table 8.2. The Databases and Their Directories
Database Name	Directory Name
Sales	SQL00001
Test	SQL00002
Prod	SQL00003

Figure 8.5. Directories created for the databases sales, test, and prod

Using the LIST DB DIRECTORY ON drive/path lets you map the SQLxxxxx directory to the actual database name. In this case if you issue the command:

 LIST DB DIRECTORY ON E:

you would get the output shown in Figure 8.6.

Figure 8.6. Output from the command list db directory on E:

If you drop one of these databases, its SQLxxxxx directory will be deleted as well. If you create a new database at a later time, this directory name will be reused. For example, if you drop the database test, the directory SQL00002 will be deleted. If you then create a new database called QA, the directory SQL00002 will be recreated and used for the QA database.

8.2.3.2 Creating a Database in a Multi-Partition Environment

Let's say you are working on a DB2 multi-partition environment running on a single SMP Linux server with the following db2nodes.cfg file:

 0  mylinx1  0 1  mylinx1  1 2  mylinx1  2

If you log in as the instance owner db2inst1 on this server and create a database with this command:

 create database sales on /data

the directory structure shown in Figure 8.7 will be created.

Figure 8.7. Directory structure for a three-partition database

As Figure 8.7 illustrates, there are three NODExxxx directories, one for each database partition. The NODExxxx directory is named based on the database instance's expanded four-digit partition number designated in the first column in the db2nodes.cfg file. Since the partition numbers used in the db2nodes.cfg file are 0, 1, and 2, these directories are NODE0000, NODE0001, and NODE0002.

8.2.4. Listing Databases

When you create a database with the CREATE DATABASE command, entries in the system database directory and local database directory are automatically entered. To list the system database directory contents, issue the command:

 list db directory

To list the local database directory contents, issue the command:

 list db directory on drive/path

Chapter 6, Configuring Client and Server Connectivity, discusses the system and local database directories in detail.

8.2.5. Dropping Databases

If you no longer need the data in a database, you can drop or (remove) the database from the system using the DROP DATABASE command. This command removes the database from the local and system database directories and deletes all table spaces, tables, logs, and directory structure supporting the database. After dropping a database, the space is immediately available for reuse.

For example, if you run the command:

 DROP DATABASE sales

the entries in the system and local database directories for this database are removed, and the database's SQLxxxxx directory is also removed. The local database directory (SQLDBDIR) is not removed when you drop a database, because there may be other databases in the same path or on the same drive.

NOTE

Removing a database is only supported using the DROP DATABASE command. Manually deleting the SQLxxxxx directory for the database is not supported, because it leaves the database entries in both the local and system database directories.

8.2.6. The Sample Database

DB2 contains a program to create a sample database that can be used for testing, or for learning purposes when you first start working with DB2. To create this database the instance must be started, and then you can run the program db2sampl. This creates a new database called sample, and the database will contain some tables with a few rows of data in each.

Use the command's -k option if you would like the sample database to be created with primary keys. In addition, you can specify the path if you would like this database to be created in a different location. For example, the command creates the sample database in the /data path, and the tables in the database have primary keys associated with them.

 db2sampl /data -k