A nodegroup is a named set of one or more nodes that are defined as belonging to a database. Each database partition that is part of the database system configuration must already be defined in a partition configuration file called db2nodes.cfg. A nodegroup can contain as little as one database partition, or as much as the entire set of database partitions defined for the database system.
You create a new nodegroup using the CREATE NODEGROUP statement, and can modify it using the ALTER NODEGROUP statement. You can add or drop one or more database partitions from a nodegroup. The database partitions must be defined in the db2nodes.cfg file before modifying the nodegroup. Table spaces reside within nodegroups. Tables reside within table spaces.
When a nodegroup is created or modified, a partitioning map is associated with it. A partitioning map, in conjunction with a partitioning key and a hashing algorithm, is used by the database manager to determine which database partition in the nodegroup will store a given row of data. For more information about partitioning maps, see Partitioning Maps. For more information about partitioning keys, see Partitioning Keys.
In a non-partitioned database, no partitioning key or partitioning map is required. There are no nodegroup design considerations if you are using a non-partitioned database. A database partition is a part of the database, complete with user data, indexes, configuration files, and transaction logs. Default nodegroups that were created when the database was created, are used by the database manager. IBMCATGROUP is the default nodegroup for the table space containing the system catalogs. IBMTEMPGROUP is the default nodegroup for system temporary table spaces. IBMDEFAULTGROUP is the default nodegroup for the table spaces containing the user defined tables that you may choose to put there. A user temporary table space for a declared temporary table can be created in IBMDEFAULTGROUP or any user-created nodegroup, but not in IBMTEMPGROUP.
If you are using a multiple partition nodegroup, consider the following design points:
You should place small tables in single-partition nodegroups, 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 allow DB2 to utilize more efficient join strategies. Collocated tables can reside in a single-partition nodegroup. Tables are considered collocated if they reside in a multiple partition nodegroup, have the same number of columns in the partitioning key, and if the data types of the corresponding columns are partition compatible. Rows in collocated tables with the same partitioning key value are placed on the same database partition. Tables can be in separate table spaces in the same nodegroup, and still be considered collocated.
You should avoid extending medium-sized tables across too many database partitions. For example, a 100 MB table may perform better on a 16 partition nodegroup than on a 32 partition nodegroup.
You can use nodegroups to separate online transaction processing (OLTP) tables from decision support (DSS) tables, to ensure that the performance of OLTP transactions is not adversely affected.
Your logical database design, and the amount of data to be processed, will suggest whether your database needs to be partitioned. This section covers the following topics related to database partitioning:
DB2 supports a partitioned storage model that allows you to store data across several database partitions in the database. This means that the data is physically stored across more than one database partition, and yet can be accessed as though it were located in the same place. Applications and users accessing data in a partitioned database do not need to be aware of the physical location of the data.
The data, while physically split, is used and managed as a logical whole. Users can choose how to partition their data by declaring partitioning keys. Users can also determine across which and how many database partitions their table data can be spread, by selecting the table space and the associated nodegroup in which the data should be stored. In addition, an updatable partitioning map is used with a hashing algorithm to specify the mapping of partitioning key values to database partitions, which determines the placement and retrieval of each row of data. As a result, you can spread the workload across a partitioned database for large tables, while allowing smaller tables to be stored on one or more database partitions. Each database partition has local indexes on the data it stores, resulting in increased performance for local data access.
You are not restricted to having all tables divided across all database partitions in the database. DB2 supports partial declustering, which means that you can divide tables and their table spaces across a subset of database partitions in the system (that is, a nodegroup).
An alternative to consider when you want tables to be positioned on each database partition, is to use summary tables and then replicate those tables. You can create a summary table containing the information that you need, and then replicate it to each node. For more information, see Replicated Summary Tables.
In a partitioned database environment, the database manager must have a way of knowing which table rows are stored on which database partition. The database manager must know where to find the data it needs, and uses a map, called a partitioning map, to find the data.
A partitioning map is an internally generated array containing either 4 096 entries for multiple partition nodegroups, or a single entry for single-partition nodegroups. For a single-partition nodegroup, the partitioning map has only one entry containing the partition number of the database partition where all the rows of a database table are stored. For multiple partition nodegroups, the partition numbers of the nodegroup are specified in a round-robin fashion. Just as a city map is organized into sections using a grid, the database manager uses a partitioning key to determine the location (the database partition) where the data is stored.
For example, assume that you have a database created on four database partitions (numbered 0-3). The partitioning map for the IBMDEFAULTGROUP nodegroup of this database would be:
0 1 2 3 0 1 2 ...
If a nodegroup had been created in the database using database partitions 1 and 2, the partitioning map for that nodegroup would be:
1 2 1 2 1 2 1 ...
If the partitioning key for a table to be loaded in the database is an integer that has possible values between 1 and 500 000, the partitioning key is hashed to a partition number between 0 and 4 095. That number is used as an index into the partitioning map to select the database partition for that row.
Figure 35 shows how the row with the partitioning key value (c1, c2, c3) is mapped to partition 2, which, in turn, references database partition n5.
Figure 35. Data Distribution Using a Partition Map
A partition map is a flexible way of controlling where data is stored in a partitioned database. If you have a need at some future time to change the data distribution across the database partitions in your database, you can use the data redistribution utility. This utility allows you to rebalance or introduce skew into the data distribution. For more information about this utility, refer to "Redistributing Data Across Database Partitions" in the Administration Guide: Performance.
You can use the Get Table Partitioning Information (sqlugtpi) API to obtain a copy of a partitioning map that you can view. For more information about this API, refer to the Administrative API Reference.
A partitioning key is a column (or group of columns) that is used to determine the partition in which a particular row of data is stored. A partitioning key is defined on a table using the CREATE TABLE statement. If a partitioning key is not defined for a table in a table space that is divided across more than one database partition in a nodegroup, one is created by default from the first column of the primary key. If no primary key is specified, the default partitioning key is the first non-long field column defined on that table. (Long includes all long data types and all large object (LOB) data types). If you are creating a table in a table space associated with a single-partition nodegroup, and you want to have a partitioning key, you must define the partitioning key explicitly. One is not created by default.
If no columns satisfy the requirement for a default partitioning key, the table is created without one. Tables without a partitioning key are only allowed in single-partition nodegroups. You can add or drop partitioning keys at a later time, using the ALTER TABLE statement. Altering the partition key can only be done to a table whose table space is associated with a single-partition nodegroup.
Choosing a good partitioning key is important. You should take into consideration:
If collocation is not a major consideration, a good partitioning key for a table is one that spreads the data evenly across all database partitions in the nodegroup. The partitioning key for each table in a table space that is associated with a nodegroup determines if the tables are collocated. Tables are considered collocated when:
This ensures that rows of collocated tables with the same partitioning key values are located on the same partition. For more information about partition-compatibility, see Partition Compatibility. For more information about table collocation, see Table Collocation.
An inappropriate partitioning key can cause uneven data distribution. Columns with unevenly distributed data, and columns with a small number of distinct values should not be chosen as a partitioning key. The number of distinct values must be great enough to ensure an even distribution of rows across all database partitions in the nodegroup. The cost of applying the partitioning hash algorithm is proportional to the size of the partitioning key. The partitioning key cannot be more than 16 columns, but fewer columns result in better performance. Unnecessary columns should not be included in the partitioning key.
The following points should be considered when defining partitioning keys:
UPDATE emp_table SET ... WHERE emp_no = host-variable
In this case, the EMP_NO column would make a good single column partitioning key for EMP_TABLE.
Hash partitioning is the method by which the placement of each row in the partitioned table is determined. The method works as follows:
You may discover that two or more tables frequently contribute data in response to certain queries. In this case, you will want related data from such tables to be located as close together as possible. In an environment where the database is physically divided among two or more database partitions, there must be a way to keep the related pieces of the divided tables as close together as possible. The ability to do this is called table collocation.
Tables are collocated when they are stored in the same nodegroup, and when their partitioning keys are compatible. Placing both tables in the same nodegroup ensures a common partitioning map. The tables may be in different table spaces, but the table spaces must be associated with the same nodegroup. The data types of the corresponding columns in each partitioning key must be partition-compatible. For information about partition compatibility, see Partition Compatibility.
DB2 has the ability to recognize, when accessing more than one table for a join or a subquery, that the data to be joined is located at the same database partition. When this happens, DB2 can choose to perform the join or subquery at the database partition where the data is stored, instead of having to move data between database partitions. This ability to carry out joins or subqueries at the database partition has significant performance advantages. For more information, refer to "Collocated Joins" in the Administration Guide: Performance.
The base data types of corresponding columns of partitioning keys are compared and can be declared partition compatible. Partition compatible data types have the property that two variables, one of each type, with the same value, are mapped to the same partition number by the same partitioning algorithm.
Partition compatibility has the following characteristics:
A summary table is a table that is defined by a query that is also used to determine the data in the table. Summary tables can be used to improve the performance of queries. If DB2 determines that a portion of a query could be resolved using a summary table, the query may be rewritten by the database manager to use the summary table.
In a partitioned database environment, you can replicate summary tables. You can use replicated summary tables to improve query performance. A replicated summary table is based on a table that may have been created in a single-partition nodegroup, but that you want replicated across all of the database partitions in the nodegroup. To create the replicated summary table, invoke the CREATE TABLE statement with the REPLICATED keyword. The REPLICATED keyword can only be specified for a summary table that is defined with the REFRESH DEFERRED option.
For more information about summary tables, see Creating a Summary Table .
By using replicated summary tables, you can obtain collocation between tables that are not typically collocated. Replicated summary tables are particularly useful for joins in which you have a large fact table and small dimension tables. To minimize the extra storage required, as well as the impact of having to update every replica, tables that are to be replicated should be small and infrequently updated.
| Note: | You should also consider replicating larger tables that are infrequently updated: the one-time cost of replication is offset by the performance benefits that can be obtained through collocation. |
By specifying a suitable predicate in the subselect clause used to define the replicated table, you can replicate selected columns, selected rows, or both.
For more information about replicated summary tables, refer to the CREATE TABLE statement in the SQL Reference. For more information about collocated joins, refer to "Collocated Joins" in the Administration Guide: Implementation.