- IBM DB2 9.7 Advanced Administration Cookbook
- Adrian Neagu Robert Pelletier
- 1036字
- 2021-08-20 15:33:22
Creating database partition groups
In a multipartitioned environment, we have a separation mechanism named partition groups. Inside partition groups, we can isolate tables, based on transaction type, or we can group tables that are frequently used in joins together. Data in partition groups are by default spread on all partitions on which they are defined, using a round-robin distribution method, unless you decide to spread data by your own rules using customdistribution maps. However remember that, not every table is a good candidate for spreading on all partitions, especially small tables or huge tables on which we are constantly involved in ad hoc queries. In these cases, the inter-partition communication mechanism used in multipartitioned environments could act as a performance bottleneck. Any complex environment comes with its advantages and disadvantages. Therefore, you have to take into consideration these aspects and be careful with the application design.
Getting ready
In the following recipe, will describe how to create a database partition group named NAVDATAGRP, which will contain the data and index table spaces of the NAV application.
How to do it…
In many organizations, it is prohibited to use graphical tools for administration; in these cases, you should probably rely only on the command line. Therefore, in almost all recipes, we try to present both administration methods.
- In Control Center, navigate to the NAV database, right-click on Database partition groups, and choose Create…. Name the database partition group as
NAVDATAGRPand comment with "NAV data partition group". - Next, move all partitions from the Available database partitions to the Selected database partitions listbox and click OK.
- Create the
NAVDATAGRPdatabase partition group by executing the following command:[db2instp@nodedb21 ~]$ db2 "CREATE DATABASE PARTITION GROUP "NAVDATAGRP" ON DBPARTITIONNUMS (0,1,2) COMMENT ON DATABASE PARTITION GROUP "NAVDATAGRP" IS 'NAV data partition group'" [db2instp@nodedb21 ~]$ - To increase separation from other partition groups, we first need to create the table spaces and define the
NAV_BPT8kandNAV_BPI8k. To increase separation from other partition groups in terms of memory caching, we will create and assign firstNAV_BPT8kandNAV_BPI8kbuffer pools on theNAVDATAGRPdatabase partition group. These two buffer pools will be assigned to table spacesNAV_TBLSandNAV_INDX, as in the non-partitioned counterpart.
- In Control Center, navigate to the NAV database, right-click on Buffer pools, and choose Create… (place a checkmark next to Enable self tuning (automatic memory management for buffer pools is not mandatory in multipartitioned enviorments, you can set your own values for buffer pool size) and move NAVDATAGRP to Selected database partition groups).
- Create the
NAV_BPI8kbuffer pool identically.
[db2instp@nodedb21 ~]$ db2 "CREATE BUFFERPOOL NAV_BPI8K IMMEDIATE DATABASE PARTITION GROUP "NAVDATAGRP" SIZE 1000 AUTOMATIC PAGESIZE 8 K" [db2instp@nodedb21 ~]$
The next step is to create the data and index table spaces by using the MANAGED BY DATABASE option. One difference that you should notice between creating table spaces on non-partitioned and multipartitioned databases is that you can specify the partition number; in this way, you can implicitly define the host for containers.
- To create the table space
NAV_TBLSon partitions0, 1, and2, execute the following command:[db2instp@nodedb21 ~]$ db2 "CREATE LARGE TABLESPACE NAV_TBLS IN DATABASE PARTITION GROUP "NAVDATAGRP" PAGESIZE 8 K MANAGED BY DATABASE USING ( FILE '/data/db2/db2instp/nav_tbls0p0.dbf' 2560 ) ON DBPARTITIONNUM (0) USING ( FILE '/data/db2/db2instp/nav_tbls0p1.dbf' 2560 ) ON DBPARTITIONNUM (1) USING ( FILE '/data/db2/db2instp/nav_tbls0p2.dbf' 2560 ) ON DBPARTITIONNUM (2) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL NAV_BPT8K" [db2instp@nodedb21 ~]$ - To create the table space
NAV_INDXon partitions0, 1, and2, execute the following command:
[db2instp@nodedb21 ~]$ db2 "CREATE LARGE TABLESPACE NAV_INDX IN DATABASE PARTITION GROUP "NAVDATAGRP" PAGESIZE 8 K MANAGED BY DATABASE USING ( FILE '/data/db2/db2instp/nav_indx0p0.dbf' 2560 ) ON DBPARTITIONNUM (0) USING ( FILE '/data/db2/db2instp/nav_indx0p1.dbf' 2560 ) ON DBPARTITIONNUM (1) USING ( FILE '/data/db2/db2instp/nav_indx0p2.dbf' 2560 ) ON DBPARTITIONNUM (2) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL NAV_BPI8K" [db2instp@nodedb21 ~]$
How it works...
Consider database partition groups as logical containers for tables and indexes. Their definition influences how the tables are spread among partitions. If a partition group has two partitions, the table is spread across these two partitions; if you have four partitions; tables are spread implicitly on four partitions, and so on. This might be considered as the implicit behavior.
You can control table spreading across partitions using custom distribution maps covered in the following recipes.
Implicitly, when a database is created, three database partition groups are defined, as follows:
- The
IBMDEFAULTGROUPdatabase partition group spreads on all existent partitions. TheUSERSPACE1table space is defined on this partition group. You cannot drop but you can alter this partition group. - The
IBMCATGROUPdatabase partition group is defined on only one partition (the catalog partition).TheSYSCATSPACE1table space is defined on this group. You cannot drop or alter this partition group. - The
IBMTEMPGROUPdatabase partition group is defined on all partitions. TheTEMPSPACE1temporary table space is defined on this group. You cannot drop but you can alter this partition group.
There's more...
If you define table spaces with automatic storage, they will spread automatically among all partitions defined in the database partition group assigned to them.
If you have tens or hundreds of partitions and use MANAGED BY DATABASE as storage option take in consideration the use of $N variable in container definition across the partitions.
Information about existent partition groups can be found in the following catalog views:
SYSCAT.DBPARTITIONGROUPSSYSCAT.DBPARTITIONGROUPDEF