- 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:
- SoapUI Cookbook
- The Modern C++ Challenge
- Python 深度學習
- Django：Web Development with Python
- C語言程序設計
- Blockly創意趣味編程
- 深入理解Java7：核心技術與最佳實踐
- 零基礎學Java程序設計
- 嚴密系統設計：方法、趨勢與挑戰
- Java程序設計：原理與范例
- Mastering Unity 2D Game Development（Second Edition）
- 后臺開發：核心技術與應用實踐
- Flink技術內幕：架構設計與實現原理
- MongoDB Cookbook（Second Edition）
- JavaScript編程精解（原書第2版）