官术网_书友最值得收藏!

首頁 >

IBM DB2 9.7 Advanced Administration Cookbook

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.

Using Control Center

  1. In Control Center, navigate to the NAV database, right-click on Database partition groups, and choose Create…. Name the database partition group as NAVDATAGRP and comment with "NAV data partition group".
  2. Next, move all partitions from the Available database partitions to the Selected database partitions listbox and click OK.
Using Control Center

Using the command line

  1. Create the NAVDATAGRP database 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 ~]$
  2. To increase separation from other partition groups, we first need to create the table spaces and define the NAV_BPT8k and NAV_BPI8k. To increase separation from other partition groups in terms of memory caching, we will create and assign first NAV_BPT8k and NAV_BPI8k buffer pools on the NAVDATAGRP database partition group. These two buffer pools will be assigned to table spaces NAV_TBLS and NAV_INDX, as in the non-partitioned counterpart.

Using Control Center

  1. 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).
    Using Control Center
  2. Create the NAV_BPI8k buffer pool identically.

Using the command line

  1. To create the NAV_BPT8K buffer pool, execute the following command: 
    [db2instp@nodedb21 ~]$ db2 "CREATE BUFFERPOOL NAV_BPT8K IMMEDIATE DATABASE PARTITION GROUP "NAVDATAGRP" SIZE 1000 AUTOMATIC PAGESIZE 8 K " [db2instp@nodedb21 ~]$
  2. To create the NAV_BPI8k buffer pool, execute the following command:
[db2instp@nodedb21 ~]$ db2 "CREATE BUFFERPOOL NAV_BPI8K IMMEDIATE DATABASE PARTITION GROUP "NAVDATAGRP" SIZE 1000 AUTOMATIC PAGESIZE 8 K" [db2instp@nodedb21 ~]$

Create the NAV application's table spaces

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.

  1. To create the table space NAV_TBLS on partitions 0, 1, and 2, 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 ~]$
  2. To create the table space NAV_INDX on partitions 0, 1, and 2, 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 ~]$

Note

It is mandatory to create the table space containers on the same partitions as the associated buffer pool. Notice also that a buffer pool in a multipartitioned environment that can be defined on one or more partition groups.

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 IBMDEFAULTGROUP database partition group spreads on all existent partitions. The USERSPACE1 table space is defined on this partition group. You cannot drop but you can alter this partition group.
  • The IBMCATGROUP database partition group is defined on only one partition (the catalog partition).The SYSCATSPACE1 table space is defined on this group. You cannot drop or alter this partition group.
  • The IBMTEMPGROUP database partition group is defined on all partitions. The TEMPSPACE1 temporary 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.DBPARTITIONGROUPS
  • SYSCAT.DBPARTITIONGROUPDEF

See also

  • The Creating and configuring table spaces in multipartitioned environment recipe, in Chapter 4, Storage—Using DB2 Table Spaces.
  • The Managing buffer pools in a multipartitioned database recipe in Chapter 5, DB2 Buffer Pools.
主站蜘蛛池模板： 安庆市| 株洲市| 基隆市| 辰溪县| 民乐县| 三门峡市| 宿州市| 依安县| 崇信县| 巴楚县| 延津县| 德清县| 凤阳县| 衢州市| 来宾市| 鹤庆县| 凌海市| 邵阳市| 临清市| 玉田县| 大庆市| 老河口市| 长沙市| 阜城县| 平果县| 沐川县| 绍兴市| 萨嘎县| 文水县| 沙河市| 江源县| 松原市| 通许县| 五华县| 于都县| 健康| 界首市| 阿克苏市| 元谋县| 博湖县| 淄博市|