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

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.

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.

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.

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.

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.

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

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.

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.