Section 7.2. Databases

7.2. Databases

A database is a collection of information organized into interrelated objects such as table spaces, partition groups, and tables. Each database is an independent unit containing its own system information, temporary space, transaction logs, and configuration files, as illustrated in Figure 7.2.

Figure 7.2. A database and its objects

Figure 7.2 shows two databases, MYDB1 and MYDB2, inside instance DB2 in a single-partition environment (Database Partition 0). The box showing Database Partition 0 is included for completeness; in a single-partition environment you can ignore this box. Since databases are independent units, object names from different databases can be the same. For example, the name of the table space MyTablespace1 is repeated in both databases in the figure.

Figure 7.2 also shows three table spaces that DB2 creates by default when you create a database: SYSCATSPACE, TEMPSPACE1, and USERSPACE1. These table spaces are described in section 7.4, Table Spaces.

In this chapter we only discuss database objects. Configuration files are discussed in Chapter 5, Understanding the DB2 Environment, DB2 Instances, and Databases. The local database directory is discussed in Chapter 6, Configuring Client and Server Connectivity. Logs are discussed in Chapter 13, Developing Database Backup and Recovery Solutions.

To create a database, use the CREATE DATABASE command (Chapter 8, The DB2 Storage Model, discusses this command in detail). To perform operations against database objects, you first need to connect to the database using the CONNECT statement (see Chapter 6, Configuring Client and Server Connectivity).

7.2.1. Database Partitions

You can create a single-partition or a multi-partition database, depending on your needs.

In a multi-partition environment, a database partition (or simply partition) is an independent part of a database containing its own data, indexes, configuration files, and transaction logs. Database functions are also distributed between all the database partitions. This provides for unlimited scalability.

Multiple database partitions can reside on one physical server; these are sometimes referred to as logical partitions sharing the resources of the machine. Database partitions can also reside on different physical servers. The collection of all of these partitions corresponds to one database.

Multi-partition database support uses the database partitioning feature of DB2 UDB Enterprise Server Edition (ESE), which comes built-in with the product but requires the purchase of a separate license.

Figure 7.3 shows two databases, MYDB1 and MYDB2, in a multi-partition environment with three database partitions.

Figure 7.3. Database partitions

As mentioned in Chapter 2, DB2 at a Glance: The Big Picture, an instance associates the DB2 binary code to databases. Database partitions, on the other hand, are used to split your database into different parts. Therefore, an instance in a multi-partition environment associates the DB2 binary code to the different database partitions. Figure 7.3 shows this association.

7.2.2. The Database Node Configuration File (db2nodes.cfg)

In a multi-partition environment you define the database partitions that are part of your database by entering the appropriate information in the database node configuration file, db2nodes.cfg.

On Linux and UNIX platforms, the db2nodes.cfg file can contain up to five columns, as shown in Table 7.1.

Table 7.1. Columns of the db2nodes.cfg Configuration File for Linux and UNIX

	Partition Number	Hostname	Logical Port	Netname	Resourcesetname
Is the Column Required?	Yes.	Yes.	Sometimes.	Sometimes.	Optional.
Description	DB2 uses this column to identify the partition.	The TCP/IP host name of the server where the partition is created.	This column must be used if you want to create more than one partition on the same server. It specifies the logical port for the partition within the server and must be unique within a server.	Supports a host that has more than one active TCP/IP interface, each with its own host name. It is required if you are using a high speed interconnect for inter-partition communication or if the resourcesetname column is used	It specifies the operating system resource that the partition should be started in.

On Windows there is also another column, Computername, which contains the computer name for the machine on which a partition resides. Table 7.2 shows the order of the columns on Windows systems and whether they are required.

Table 7.2. Columns of the db2nodes.cfg Configuration File for Windows

	Partition Number	Hostname	Computername	Logical Port	Netname	Resourcesetname
Is the Column Required?	Yes.	Yes.	Yes.	Sometimes.	Sometimes.	Optional.

When you create an instance in DB2 ESE, a default db2nodes.cfg file is created with one row. On Linux and UNIX the default file has three columns and looks like the following:

 0    mypenguin    0 

On Windows the default file has four columns and looks like the following:

 0    myserver    myserver    0 

The db2nodes.cfg file is located

• Under the sqllib directory for the instance owner on Linux and UNIX

• Under the SQLLIB\Instance_name directory on Windows

There is only one db2nodes.cfg file per instance, and all the databases you create under this instance will be partitioned at CREATE DATABASE time based on the contents of this file. To create multiple partitions, edit the db2nodes.cfg file and add an entry for each database partition. For example, assume you have an eight-way SMP server (a server with eight CPUs) running Linux as shown in Figure 7.4.

Figure 7.4. An eight-way SMP Linux server with four database partitionsYou need to edit the db2nodes.cfg file to make it look like the following:

 0    mypenguin    0 1    mypenguin    1 2    mypenguin    2 3    mypenguin    3 

In another scenario, assume you are installing DB2 on a cluster of eight two-way SMP Linux servers, and you want to create one partition on each server as illustrated in Figure 7.5 (not all servers are shown).

Figure 7.5. A cluster of eight two-way SMP Linux servers with eight partitions in total

You need to edit the db2nodes.cfg file to make it look like the following:

 0    mypena    0 1    mypenb    0 2    mypenc    0 3    mypend    0 4    mypene    0 5    mypenf    0 6    mypeng    0 7    mypenh    0 

In yet another scenario, assume you are installing DB2 on a cluster of four UNIX servers with four CPUs each and you want to create two partitions on each server as shown in Figure 7.6.

Figure 7.6. A cluster of four four-way SMP UNIX servers with eight database partitions in total

You need to edit the db2nodes.cfg file to make it look like the following:

 0    myuxa    0 1    myuxa    1 2    myuxb    0 3    myuxb    1 4    myuxc    0 5    myuxc    1 6    myuxd    0 7    myuxd    1 

On Linux and UNIX you can edit the db2nodes.cfg file with any ASCII editor. On Windows, you cannot edit the db2nodes.cfg file directly; instead, use the db2ncrt and db2ndrop commands to add and drop database partitions, respectively.

You can also use the db2start command to add and/or remove a database partition from the DB2 instance and the db2nodes.cfg file as follows.

• Use the db2start command with the add dbpartitionnum option to add a partition to the database and insert an entry for the partition into the db2nodes.cfg file.

• Use the db2start command with the drop dbpartitionnum option to remove a partition from the database and delete its entry from the db2nodes.cfg file.

You can also use the add dbpartitionnum command to add a partition to the database even if the partition already has an entry in the db2nodes.cfg file. The drop dbpartitionnum command will remove the specified partition from the database but will not remove its entry from the instance's db2nodes.cfg file.