db2多分區(qū)數(shù)據(jù)分布課件

DB2知識(shí)講座1可編輯課件PPT培訓(xùn)內(nèi)容AddingdatabasepartitionsRedistributingpartitiongroupsAlteringdatabasepartitionsDroppingadatabasepartitions2可編輯課件PPTServicesfileDuringtheinstancecreation,anumberofports,whichareequaltothenumberoflogicalnodesthattheinstanceiscapableofsupporting,arereservedintheservicesfile.TheportsthatarereservedintheservicesfileareusedbytheDB2FastCommunicationManager.Thereservedportshavethefollowingformat:Cat/etc/services|grepdb2/etc/serviceswithforportsreservedfordb2inst1DB2_InstanceName端口號(hào)DB2_InstanceName_1端口號(hào)DB2_InstanceName_2端口號(hào)DB2_InstanceName_END端口號(hào)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:((如果未指定主鍵且未定義分布鍵，那么分布鍵是該列表中的第一個(gè)非長(zhǎng)型列)db2inst1@db2One:～>db2“selectdbpartitionnum(id),count(*)fromdb2inst1.studentgroupbydbpartitionnum(id)orderbydbpartitionnum(id)（查看數(shù)據(jù)分布情況）4可編輯課件PPTDatabasepartitionscanbeaddedtoaDPFenvironmentbyusingthefollowingcommands:db2start...ADDDBPARTITIONNUMADDDBPARTITIONNUMThedb2start...ADDDBPARTITIONNUMcommandstartsDB2andaddsanewpartitiontoanexistinginstanceanddatabase.db2inst1@db2Two:～>

db2startDBPARTITIONNUM4ADDDBPARTITIONNUMHOSTNAMEdb2TwoPORT2withouttablespaces(可選，建議有)04/04/200709:00:1640SQL6075WTheStartDatabaseManageroperationsuccessfullyaddedthenode.Thenodeisnotactiveuntilallnodesarestoppedandstartedagain.SQL6075WTheStartDatabaseManageroperationsuccessfullyaddedthenode.Thenodeisnotactiveuntilallnodesarestoppedandstartedagain.注意:那臺(tái)機(jī)器增加數(shù)據(jù)庫(kù)分區(qū)就登陸到那臺(tái)機(jī)器上發(fā)布命令5可編輯課件PPTUponsuccessfulcompletionofthecommand,thenewdb2nodes.cfgisasshownThedb2nodes.cfgaftersuccessfullyaddingadatabaspartition0db2One01db2One12db2Two03db2Two14db2Two2Note:也可以先更改db2nodes.cfg.把要增加的分區(qū)填進(jìn)去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的不能被從新發(fā)布。redistribute命令只能在目錄分區(qū)執(zhí)行8可編輯課件PPTdb2inst1@db2Two:～>exportDB2NODE=0(switchingpartitions)db2inst1@db2Two:～>db2terminate（終止db2后臺(tái)進(jìn)程使其有效）db2inst1@db2Two:～>db2connecttoitsodbdb2inst1@db2Two:～>db2“values(currentdbpartitionnum)”(查看是否在0這個(gè)分區(qū)上)db2inst1@db2Two:～>db2“redistributedatabasepartitiongroupIBMDEFAULTGROUPuniform”(從新發(fā)布下數(shù)據(jù))db2inst1@udb2Two:～>db2“selectdbpartitionnum(id),count(*)fromdb2inst1.studentgroupbydbpartitionnum(id)orderbydbpartitionnum(id)（查看數(shù)據(jù)分布情況）9可編輯課件PPTdb2inst1@db2Two:～

>exportDB2NODE=4db2inst1@db2Two:～>db2terminatedb2inst1@db2Two:～>db2dropdbpartitionumverify(驗(yàn)證是否含有數(shù)據(jù)，如如有需要從新發(fā)布)db2inst1@db2Two:～>exportDB2NODE=0db2inst1@db2Two:～>db2terminatedb2inst1@db2Two:～>db2connecttoitsodbdb2inst1@db2Two:～

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

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