Learning Visually with Examples [Electronic resources] نسخه متنی

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.

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.

In Figure 8.3, NODE0000 represents the partition number of the database. In a multi-partition environment, there will be one NODE

xxxx 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 SQL

xxxxx directory, DB2 also creates a number of additional subdirectories. Table 8.1 explains DB2's usage of each of these directories.

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.

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.

Using the

LIST DB DIRECTORY ON

drive

/

path lets you map the SQL

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

If you drop one of these databases, its SQL

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

As Figure 8.7 illustrates, there are three NODE

xxxx directories, one for each database partition. The NODE

xxxx 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 SQL

xxxxx 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 SQL

xxxxx 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