- IBM DB2 9.7 Advanced Administration Cookbook
- Adrian Neagu Robert Pelletier
- 1148字
- 2021-08-20 15:33:23
Managing data redistribution on database partition groups
In this recipe, we will perform data redistribution on database partitions 3 and 4. For the method, we will use the default uniform distribution.
Getting ready
Now that we have added partitions 3 and 4 to partition group NAVDATAGRP and the corresponding containers to the NAV_TBLS and NAV_INDX table spaces, the next logical step should be to redistribute data on all existent partitions.
How to do it...
Internally, DB2 9.7 uses a distribution mechanism based on a partition map, which is actually an array containing 4,096 entries for backward compatibility. It can be extended to 32768 by setting the DB2_PMAP_COMPATIBILITY registry variable to OFF. The distribution map is generated and associated internally with a database partition group when is created. Every partition defined on a database partition group is mapped inside this array in a round-robin fashion.
We will generate a distribution map once before data redistribution for database partition group NAVDATAGRP and table COMM and once after data redistribution of table COMM, to give you an idea of how the partitions are remapped inside NAVDATAGRP database partition group.
- Execute the following command to generate the distribution map file for the
NAVDATAGRPdatabase partition group:[db2instp@nodedb21 ~]$ db2gpmap -d NAV -m ~/beforerenavdatagrp.map -g NAVDATAGRP Connect to NAV. Successfully connected to database. Retrieving the partition map ID using nodegroup NAVDATAGRP. The partition map has been sent to /db2partinst/db2instp/beforerenavdatagrp.map. [db2instp@nodedb21 ~]$A short listing from the file generated /db2partinst/db2instp/beforenavdatagrp.map, to see the partition mapping before redistribution inside database partition group NAVDATAGRP:
……………………………………………………………………………… 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 ……………………………………………………………………………… - To generate the distribution map at the
COMMtable level, issue the following command:[db2instp@nodedb21 ~]$ db2gpmap -d NAV -m ~/beforenavdatagrpCOMM.map -t NAV.COMM Successfully connected to database. Retrieving the partition map ID using table COMM. The partition map has been sent to /db2partinst/db2instp/beforenavdatagrpCOMM.map [db2instp@nodedb21 ~]$Short listing from the
beforenavdatagrpCOMM.mapfile:………………………………………………………………………………. 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 2 2 0 ……………………………………………………………………………….. - To redistribute data on all the partitions in uniform mode, execute the following command:
[db2instp@nodedb21 ~]$ db2 "REDISTRIBUTE DATABASE PARTITION GROUP NAVDATAGRP UNIFORM" DB20000I The REDISTRIBUTE NODEGROUP command completed successfully. [db2instp@nodedb21 ~]$Note
Any redistribution operation generates a redistribution log file in the
<instance_owner_home>/sqllib/redistdirectory. Here, you will find one or more files that contain the steps performed by the redistribution process having a generic format:Databasename.Databasepartitiongroupname.yyyymmddhhmiss. - Generate the distribution map for the table
COMM, to see how the table data has been redistributed across database partitions:[db2instp@nodedb21 ~]$ db2gpmap -d NAV -m ~/afternavdatagrpcomm.map -t comm Connect to NAV. Successfully connected to database. Retrieving the partition map ID using table COMM. The partition map has been sent to /db2partinst/db2instp/afternavdatagrpcomm.map.A short listing from the map file is as follows:
…………………………………………………………………………… 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 3 4 3 3 4 3 3 4 3 3 4 3 3 4 3 3 4 3 3 4 3 3 4 1 2 0 1 2 0 1 2 0 1 2 0 1 2 0 1 2 0 1 2 0 1 2 0 1 2 0 1 2 ……………………………………………………………………………… - Connnect to database
NAVand collect statistics for all tables and indexes fromNAVschema:
[db2instp@nodedb21 ~]$ RUNSTATS ON TABLE NAV.COMM ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS" DB20000I The RUNSTATS command completed successfully.
How it works…
The partition map is built up at database partition group creation. Any table created in NAVDATAGRP will be redistributed as the internal map directs it, by default, in round-robin fashion, as stated previously. The default redistribution method is UNIFORM. You can build and use, for redistribution, your own customized partition maps.
There's more…
We used a simple method to redistribute data. Generally if you want to redistribute a large volume of data preliminary resource analysis is required. DB2 provides a set of so called step-wise procedures for log space analysis and creating the best redistribution plan. These procedures are implemented in Control Center redistribute wizard.
In Control Center, right-click on the NAVDATAGRP database partition group and choose Redistribute…:
More information and examples with step wise procedures can be found at the following link:
For detailed usage and options of REDISTRIBUTE DATABASE PARTITION GROUP command consult the following link:
- Web開發的貴族：ASP.NET 3.5+SQL Server 2008
- Learning ArcGIS Pro
- JavaScript by Example
- Mastering RStudio：Develop，Communicate，and Collaborate with R
- 運用后端技術處理業務邏輯（藍橋杯軟件大賽培訓教材-Java方向）
- 智能搜索和推薦系統：原理、算法與應用
- Node.js開發指南
- Natural Language Processing with Python Quick Start Guide
- Go語言從入門到精通
- Learning Jakarta Struts 1.2: a concise and practical tutorial
- 計算語言學導論
- Redmine Cookbook
- Getting Started with Web Components
- Processing開發實戰
- C語言程序設計