db2多分区数据分布

Database partitions can be added to a DPF environment by using the following commands: db2start... ADD DBPARTITIONNUM ADD DBPARTITIONNUM The db2start... ADD DBPARTITIONNUM command starts DB2 and adds a new partition to an existing instance and database. 注意:那台机器增加数据库分区就登陆到那台机器上发布命令 db2inst1@db2Two:～> db2start DBPARTITIONNUM 4 ADD DBPARTITIONNUM HOSTNAME db2Two PORT 2 without tablespaces(可选 ，建议有 ) 04/04/2007 09:00:16 4 0 SQL6075W The Start Database Manager operation successfully added the node. The node is not active until all nodes are stopped and started again. SQL6075W The Start Database Manager operation successfully added the node. The node is not active until all nodes are stopped and started again.
root@db2One:～ root@db2One:～>su db2inst1 db2inst1@db2One:～ db2inst1@db2One:～>db2start db2inst1@db2One:～ db2inst1@db2One:～>db2 connect to itsodb db2inst1@db2One:～ db2inst1@db2One:～>db2 “create table student (id integer not null primary key ,name varchar(2) not null) distribute by hash (id) in userspace01 ” Note:((如果未指定主键且未定义分布键，那么分布键是该列表中的第 Note:((如果未指定主键且未定义分布键，那么分布键是该列表中的第 一个非长型列) 一个非长型列) db2inst1@db2One:～ db2inst1@db2One:～> db2 “select dbpartitionnum(id),count(*) from db2inst1.student group by dbpartitionnum(id) order by dbpartitionnum(id) （查看数据分布情况）
DB2知识讲座 DB2
wenku.baidu.com
培训内容
Adding database partitions Redistributing partition groups
Altering database partitions Dropping a database partitions
Services file During the instance creation, a number of ports, which are equal to the number of logical nodes that the instance is capable of supporting, are reserved in the services file. The ports that are reserved in the services file are used by the DB2 Fast Communication Manager. The reserved ports have the following format: Cat /etc/services | grep db2 /etc/services with for ports reserved for db2inst1 DB2_InstanceName 端口号 DB2_InstanceName_1 端口号 DB2_InstanceName_2 端口号 DB2_InstanceName_END 端口号 The only mandatory entries are the beginning (DB2_InstanceName) and ending (DB2_InstanceName_END) ports. The other entries are reserved in the services so that other applications do not use these ports
db2inst1@db2Two: ～ > export DB2NODE=4 db2inst1@db2Two: ～ > db2 terminate db2inst1@db2Two: ～ > db2 drop dbpartitionum verify (验证是否含有数 (验证是否含有数 据，如如有需要从新发布) 据，如如有需要从新发布) db2inst1@db2Two: ～ > export DB2NODE=0 db2inst1@db2Two: ～ > db2 terminate db2inst1@db2Two: ～ >db2 connect to itsodb db2inst1@db2Two: ～ > db2 "alter database partition group IBMDEFAULGROUP drop dbpartitionnum(4) “ db2inst1@db2Two: ～ > db2 “ redistribute database partition group IBMDEFAULTGROUP uniform” uniform” db2inst1@db2Two:～ db2inst1@db2Two:～ >export export=4 db2inst1@db2Two:～ db2inst1@db2Two:～ > db2 terminate db2inst1@db2Two:～ db2inst1@db2Two:～ > db2 drop dbpartitionum verify db2inst1@db2Two: ～ >db2stop drop dbpartitionnum 4
Upon successful completion of the command, the new db2nodes.cfg is as shown The db2nodes.cfg after successfully adding a databaspartition 0 db2One 0 1 db2One 1 2 db2Two 0 3 db2Two 1 4 db2Two 2 Note: 也可以先更改db2nodes.cfg. 把要增加的分区填进去 db2inst1@db2Two:～ db2inst1@db2Two:～> db2start dbparitionnum n db2inst1@db2Two:～ db2inst1@db2Two:～> add dbpartitionnum without tablespaces
db2inst1@db2Two:～ db2inst1@db2Two:～> export DB2NODE=0 (switching partitions) db2inst1@db2Two:～ db2inst1@db2Two:～>db2 terminate （终止db2后台进程使其有效） （终止db2后台进程使其有效） db2inst1@db2Two:～ db2inst1@db2Two:～>db2 connect to itsodb db2inst1@db2Two: ～> db2 “values (current dbpartitionnum)”(查 dbpartitionnum)” 看是否在0 看是否在0 这个分区上 ) db2inst1@db2Two:～ db2inst1@db2Two:～> db2 “ redistribute database partition group IBMDEFAULTGROUP uniform” (从新发布下数据) uniform” 从新发布下数据) db2inst1@udb2Two:～ db2inst1@udb2Two:～> db2 “select dbpartitionnum(id),count(*) from db2inst1.student group by dbpartitionnum(id) order by dbpartitionnum(id) （查看数据分布情况）
Add tablespaces
db2inst1@db2Two:～> db2 "alter tablespace tempspace01 add ('/db2temp/db2inst1/itsodb/NODE0004/tempspace01') on dbpartitionnum (4)“ db2inst1@db2Two:～> db2 "alter database partition group IBMDEFAULTGROUP add dbpartitionnum(4) without tablespaces“ 表在 ibmcatagroup和ibmtempgroup的不能被从新发布 。 redistribute 命令只能在目录分区执行 db2inst1@db2Two:～> db2 "alter tablespace userspace01 add ( file '/tablespaces/db2inst1/itsodb/NODE0004/userspace01.001’ 20M ) on dbpartitionnum (4)" DB20000I The SQL command completed successful