Database partition groups

A database partition group is a named set of one or more database partitions that belong to a database.

A database partition group that contains more than one database partition is known as a multiple partition database partition group. Multiple partition database partition groups can only be defined with database partitions that belong to the same instance.

Figure 1 shows an example of a database with five database partitions.
  • Database partition group 1 contains all but one of the database partitions.
  • Database partition group 2 contains one database partition.
  • Database partition group 3 contains two database partitions.
  • The database partition in Group 2 is shared (and overlaps) with Group 1.
  • A single database partition in Group 3 is shared (and overlaps) with Group 1.
Figure 1. Database partition groups in a database
Five database partitions are grouped into three database partition groups.

When a database is created, all database partitions that are specified in the database partition configuration file named db2nodes.cfg are created as well. Other database partitions can be added or removed with the ADD DBPARTITIONNUM or DROP DBPARTITIONNUM VERIFY command, respectively. Data is divided across all of the database partitions in a database partition group.

When a database partition group is created, a distribution map is associated with the group. The distribution map, along with a distribution key and a hashing algorithm are used by the database manager to determine which database partition in the database partition group will store a given row of data.

Default database partition groups

Three database partition groups are defined automatically at database creation time:
  • IBMCATGROUP for the SYSCATSPACE table space, holding system catalog tables
  • IBMTEMPGROUP for the TEMPSPACE1 table space, holding temporary tables created during database processing
  • IBMDEFAULTGROUP for the USERSPACE1 table space, holding user tables and indexes. A user temporary table space for a declared temporary table or a created temporary table can be created in IBMDEFAULTGROUP or any user-created database partition group, but not in IBMTEMPGROUP.

Table spaces in database partition groups

When a table space is associated with a multiple partition database partition group (during execution of the CREATE TABLESPACE statement), all of the tables within that table space are partitioned across each database partition in the database partition group. A table space that is associated with a particular database partition group cannot later be associated with another database partition group.

Creating a database partition group

Create a database partition group by using the CREATE DATABASE PARTITION GROUP statement. This statement specifies the set of database partitions on which the table space containers and table data are to reside. This statement also performs the following actions:
  • It creates a distribution map for the database partition group.
  • It generates a distribution map ID.
  • It inserts records into the following catalog views:
    • SYSCAT.DBPARTITIONGROUPDEF
    • SYSCAT.DBPARTITIONGROUPS
    • SYSCAT.PARTITIONMAPS

Altering a database partition group

Use the ALTER DATABASE PARTITION GROUP statement to add database partitions to (or drop them from) a database partition group. After adding or dropping database partitions, use the REDISTRIBUTE DATABASE PARTITION GROUP command to redistribute the data across the set of database partitions in the database partition group.

Database partition group design considerations

Place small tables in single-partition database partition groups, except when you want to take advantage of collocation with a larger table. Collocation is the placement of rows from different tables that contain related data in the same database partition. Collocated tables help the database manager to use more efficient join strategies. Such tables can exist in a single-partition database partition group. Tables are considered to be collocated if they are in a multiple partition database partition group, have the same number of columns in the distribution key, and if the data types of corresponding columns are compatible. Rows in collocated tables with the same distribution key value are placed on the same database partition. Tables can be in separate table spaces in the same database partition group, and still be considered collocated.

Avoid extending medium-sized tables across too many database partitions. For example, a 100-MB table might perform better on a 16-partition database partition group than on a 32-partition database partition group.

You can use database partition groups to separate online transaction processing (OLTP) tables from decision support (DSS) tables. This will help to ensure that the performance of OLTP transactions is not adversely affected.

If you are using a multiple partition database partition group, consider the following points:
  • In a multiple partition database partition group, you can only create a unique index if the index is a superset of the distribution key.
  • Each database partition must be assigned a unique number, because the same database partition might be found in one or more database partition groups.
  • To ensure fast recovery of a database partition containing system catalog tables, avoid placing user tables on the same database partition. Place user tables in database partition groups that do not include the database partition in the IBMCATGROUP database partition group.