Understanding DB2: Learning Visually with Examples (2nd Edition)

8.3. Database Partition Groups

In a multi-partition environment, a database partition is an independent subset of a database that contains its own data, indexes, configuration files, and transaction logs. A partition group is a logical grouping of one or more database partitions that lets you control the placement of table spaces and buffer pools within the database partitions.

8.3.1. Database Partition Group Classifications

Partition groups are classified based on the number of database partitions they contain.

• Single-partition partition groups contain only one database partition.

• Multi-partition partition groups contain more than one database partition.

Figure 8.8 shows four database partition groups.

• pgall is a multi-partition partition group that spreads across all the database partitions.

• pg01 is a multi-partition partition group that spreads across partitions 0 and 1.

• pg12 is a multi-partition partition group that spreads across partitions 1 and 2.

• pg1 is a single-partition partition group that resides on database partition 1.

NOTE

Database partitions can belong to more than one partition group. For example, in Figure 8.8 database partition 1 is part of all four partition groups.

8.3.2. Default Partition Groups

When you create a database, DB2 automatically creates three partition groups within that database. Table 8.3 describes these partition groups.

NOTE

If you create a user temporary table space, you must create it in the IBMDEFAULTGROUP or any other partition group that you have created. DB2 does not allow you to create a user temporary table in the IBMTEMPGROUP. (User temporary table spaces are used for declared global temporary tables, which are described in Chapter 7, Working with Database Objects.)

8.3.3. Creating Database Partition Groups

You create a database partition group with the statement CREATE DATABASE PARTITION GROUP. The statement also records the partition group definition in the database system catalog tables.

The following commands show how to create the partition groups you see in Figure 8.8. For this example assume that the db2nodes.cfg file contains the following entries for the database partitions numbered 0, 1, and 2:

0 mylinx1 0 1 mylinx1 1 2 mylinx1 2

Starting with pgall, there are two ways to create this partition group using the CREATE DATABASE PARTITION GROUP statement:

create database partition group pgall on dbpartitionnums (0,1,2)

or

create database partition group pgall on all dbpartitionnums

You would create the other partition groups in Figure 8.8 as follows:

create database partition group pg01 on dbpartitionnums (0,1) create database partition group pg12 on dbpartitionnums (1,2) create database partition group pg1 on dbpartitionnums (1)

8.3.4. Modifying a Database Partition Group

You can modify a partition group with the ALTER DATABASE PARTITION GROUP statement. This statement changes the definition of an existing partition group by adding or removing partitions. If you want to add a new partition to the partition group, that partition must already be defined in the db2nodes.cfg file.

Continuing with the example from the previous section, you can add a new database partition to the instance by editing the db2nodes.cfg file and adding a fourth line:

0 mylinx1 0 1 mylinx1 1 2 mylinx1 2 3 mylinx1 3

If you now want to alter the partition group pgall to add partition number 3, issue this statement:

alter database partition group pgall add dbpartitionnum (3)

Notice that partition number 1 in this example is one part of all partitions groups. To reduce some of the load on that partition you can remove it from partition group pgall, as follows:

alter database partition group pgall drop dbpartitionnum (1)

8.3.5. Listing Database Partition Groups

You can list all partition groups in your database with the LIST DATABASE PARTITION GROUP statement. This lists all the partition groups that are defined in the database, regardless of which database partition you are currently connected to. The following is the output of this statement for the example we have been discussing.

DATABASE PARTITION GROUP ---------------------------------------------- IBMCATGROUP IBMDEFAULTGROUP PGALL PG01 PG1 PG12

To see which partitions are included in each partition group, use the SHOW DETAIL option with the LIST DATABASE PARTITION GROUP statement. This option provides additional information, including:

• PMAP_ID: The partitioning map associated with the partition group.

• DATABASE PARTITION NUMBER: The database partition number as defined in the db2nodes.cfg file.

• IN_USE: The status of the database partition.

The output of this command contains three columns, and one row for each database partition that is part of the partition group, with the exception of the IBMTEMGROUP.

DATABASE PARTITION GROUP DATABASE PARTITION NUMBER IN_USE ------------------ ------------------- ------- IBMCATGROUP 0 Y IBMDEFAULTGROUP 0 Y IBMDEFAULTGROUP 1 Y IBMDEFAULTGROUP 2 Y IBMDEFAULTGROUP 3 Y PGALL 0 Y PGALL 1 Y PGALL 2 Y PGALL 3 Y PG01 2 Y PG01 3 Y PG12 2 Y PG12 3 Y PG1 2 Y

NOTE

This information is also available in the system catalog table SYSCAT.NODEGROUPDEF.

8.3.6. Dropping a Database Partition Group

While a partition group does not consume any system resources, if a partition group is not being used, you can drop it using the DROP DATABASE PARTITION GROUP statement. If you wanted to drop the partition group pg12 from our example, use the statement:

DROP DATABASE PARTITION GROUP pg12