db2多分区数据分布课件

DB2知识讲座1可编辑课件PPT培训内容AddingdatabasepartitionsRedistributingpartitiongroupsAlteringdatabasepartitionsDroppingadatabasepartitions2可编辑课件PPTServicesfileDuringtheinstancecreation,anumberofports,whichareequaltothenumberoflogicalnodesthattheinstanceiscapableofsupporting,arereservedintheservicesfile.TheportsthatarereservedintheservicesfileareusedbytheDB2FastCommunicationManager.Thereservedportshavethefollowingformat:Cat/etc/services|grepdb2/etc/serviceswithforportsreservedfordb2inst1DB2_InstanceName端口号DB2_InstanceName_1端口号DB2_InstanceName_2端口号DB2_InstanceName_END端口号Theonlymandatoryentriesarethebeginning(DB2_InstanceName)andending(DB2_InstanceName_END)ports.Theotherentriesarereservedintheservicessothatotherapplicationsdonotusetheseports3可编辑课件PPTroot@db2One:～>sudb2inst1db2inst1@db2One:～>db2startdb2inst1@db2One:～>db2connecttoitsodbdb2inst1@db2One:～>db2“createtablestudent(idintegernotnullprimarykey,namevarchar(2)notnull)distributebyhash(id)inuserspace01”Note:((如果未指定主键且未定义分布键，那么分布键是该列表中的第一个非长型列)db2inst1@db2One:～>db2“selectdbpartitionnum(id),count(*)fromdb2inst1.studentgroupbydbpartitionnum(id)orderbydbpartitionnum(id)（查看数据分布情况）4可编辑课件PPTDatabasepartitionscanbeaddedtoaDPFenvironmentbyusingthefollowingcommands:db2start...ADDDBPARTITIONNUMADDDBPARTITIONNUMThedb2start...ADDDBPARTITIONNUMcommandstartsDB2andaddsanewpartitiontoanexistinginstanceanddatabase.db2inst1@db2Two:～>

db2startDBPARTITIONNUM4ADDDBPARTITIONNUMHOSTNAMEdb2TwoPORT2withouttablespaces(可选，建议有)04/04/200709:00:1640SQL6075WTheStartDatabaseManageroperationsuccessfullyaddedthenode.Thenodeisnotactiveuntilallnodesarestoppedandstartedagain.SQL6075WTheStartDatabaseManageroperationsuccessfullyaddedthenode.Thenodeisnotactiveuntilallnodesarestoppedandstartedagain.注意:那台机器增加数据库分区就登陆到那台机器上发布命令5可编辑课件PPTUponsuccessfulcompletionofthecommand,thenewdb2nodes.cfgisasshownThedb2nodes.cfgaftersuccessfullyaddingadatabaspartition0db2One01db2One12db2Two03db2Two14db2Two2Note:也可以先更改db2nodes.cfg.把要增加的分区填进去db2inst1@db2Two:～>db2startdbparitionnumndb2inst1@db2Two:～>adddbpartitionnumwithouttablespaces

6可编辑课件PPT7可编辑课件PPTAddtablespaces

db2inst1@db2Two:～>db2"altertablespacetempspace01add('/db2temp/db2inst1/itsodb/NODE0004/tempspace01')ondbpartitionnum(4)“db2inst1@db2Two:～>db2"alterdatabasepartitiongroupIBMDEFAULTGROUPadddbpartitionnum(4)withouttablespaces“db2inst1@db2Two:～>db2"alter

tablespaceuserspace01add(file'/tablespaces/db2inst1/itsodb/NODE0004/userspace01.001’20M)ondbpartitionnum(4)"DB20000ITheSQLcommandcompletedsuccessful表在ibmcatagroup和ibmtempgroup的不能被从新发布。redistribute命令只能在目录分区执行8可编辑课件PPTdb2inst1@db2Two:～>exportDB2NODE=0(switchingpartitions)db2inst1@db2Two:～>db2terminate（终止db2后台进程使其有效）db2inst1@db2Two:～>db2connecttoitsodbdb2inst1@db2Two:～>db2“values(currentdbpartitionnum)”(查看是否在0这个分区上)db2inst1@db2Two:～>db2“redistributedatabasepartitiongroupIBMDEFAULTGROUPuniform”(从新发布下数据)db2inst1@udb2Two:～>db2“selectdbpartitionnum(id),count(*)fromdb2inst1.studentgroupbydbpartitionnum(id)orderbydbpartitionnum(id)（查看数据分布情况）9可编辑课件PPTdb2inst1@db2Two:～

>exportDB2NODE=4db2inst1@db2Two:～>db2terminatedb2inst1@db2Two:～>db2dropdbpartitionumverify(验证是否含有数据，如如有需要从新发布)db2inst1@db2Two:～>exportDB2NODE=0db2inst1@db2Two:～>db2terminatedb2inst1@db2Two:～>db2connecttoitsodbdb2inst1@db2Two:～

>db2"alterdatabasepartitiongroupIBMDEFAULGROUPdropdbpartitionnum(4)“db2inst1@db2Two:～>db2“redistributedatabasepartitiongroup

IBMDEFAULTGROUPuniform”db2inst1@db2Two:～>exportexport=4db2inst1@db2Two:～>db2terminatedb2inst1@db2Two: