db2多分区数据分布ppt课件

.,1,DB2知识讲座,.,2,培训内容,AddingdatabasepartitionsRedistributingpartitiongroupsAlteringdatabasepartitionsDroppingadatabasepartitions,3,.,ServicesfileDuringtheinstancecreation,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.Theotherentriesarereservedintheservicessothatotherapplicationsdonotusetheseports,4,.,rootdb2One:sudb2inst1db2inst1db2One:db2startdb2inst1db2One:db2connecttoitsodbdb2inst1db2One:db2“createtablestudent(idintegernotnullprimarykey,namevarchar(2)notnull)distributebyhash(id)inuserspace01”Note:(如果未指定主键且未定义分布键，那么分布键是该列表中的第一个非长型列)db2inst1db2One:db2“selectdbpartitionnum(id),count(*)fromdb2inst1.studentgroupbydbpartitionnum(id)orderbydbpartitionnum(id)（查看数据分布情况）,5,.,DatabasepartitionscanbeaddedtoaDPFenvironmentbyusingthefollowingcommands:db2start.ADDDBPARTITIONNUMADDDBPARTITIONNUMThedb2start.ADDDBPARTITIONNUMcommandstartsDB2andaddsanewpartitiontoanexistinginstanceanddatabase.,db2inst1db2Two:db2startDBPARTITIONNUM4ADDDBPARTITIONNUMHOSTNAMEdb2TwoPORT2withouttablespaces(可选，建议有)04/04/200709:00:1640SQL6075WTheStartDatabaseManageroperationsuccessfullyaddedthenode.Thenodeisnotactiveuntilallnodesarestoppedandstartedagain.SQL6075WTheStartDatabaseManageroperationsuccessfullyaddedthenode.Thenodeisnotactiveuntilallnodesarestoppedandstartedagain.,注意:那台机器增加数据库分区就登陆到那台机器上发布命令,6,.,Uponsuccessfulcompletionofthecommand,thenewdb2nodes.cfgisasshownThedb2nodes.cfgaftersuccessfullyaddingadatabaspartition0db2One01db2One12db2Two03db2Two14db2Two2,Note:也可以先更改db2nodes.cfg.把要增加的分区填进去db2inst1db2Two:db2startdbparitionnumndb2inst1db2Two:adddbpartitionnumwithouttablespaces,7,.,8,.,Addtablespaces,db2inst1db2Two:db2altertablespacetempspace01add(/db2temp/db2inst1/itsodb/NODE0004/tempspace01)ondbpartitionnum(4)“db2inst1db2Two:db2alterdatabasepartitiongroupIBMDEFAULTGROUPadddbpartitionnum(4)withouttablespaces“db2inst1db2Two:db2altertablespaceuserspace01add(file/tablespaces/db2inst1/itsodb/NODE0004/userspace01.00120M)ondbpartitionnum(4)DB20000ITheSQLcommandcompletedsuccessful,表在ibmcatagroup和ibmtempgroup的不能被从新发布。redistribute命令只能在目录分区执行,9,.,db2inst1db2Two:exportDB2NODE=0(switchingpartitions)db2inst1db2Two:db2terminate（终止db2后台进程使其有效）db2inst1db2Two:db2connecttoitsodbdb2inst1db2Two:db2“values(currentdbpartitionnum)”(查看是否在0这个分区上)db2inst1db2Two:db2“redistributedatabasepartitiongroupIBMDEFAULTGROUPuniform”(从新发布下数据)db2inst1udb2Two:db2“selectdbpartitionnum(id),count(*)fromdb2inst1.studentgroupbydbpartitionnum(id)orderbydbpartitionnum(id)（查看数据分布情况）,10,.,db2inst1db2Two:exportDB2NODE=4db2inst1db2Two:db2terminatedb2inst1db2Two:db2dropdbpartitionumverify(验证是否含有数据，如如有需要从新发布)db2inst1db2Two:exportDB2NODE=0db2inst1db2Two:db2terminatedb2inst1db2Two:db2connecttoitsodbdb2inst1db2Two:db2alterdatabasepartitiongroupIBMDEFAULGROUPdropdbpartitionnum(4)“db2inst1db2Two:db2“redistributedatabasepartitiongroupIBMDEFAULTGROUPunifo