DB2數(shù)據(jù)移動(第5講)

DB2數(shù)據(jù)移動

(第5講)

上海****技術(shù)有限責(zé)任公司Mrjim()2014-12培訓(xùn)大綱數(shù)據(jù)移動格式導(dǎo)出〔EXPORT〕工具導(dǎo)入〔IMPORT〕工具導(dǎo)入〔LOAD〕工具db2move工具db2look工具.DB2數(shù)據(jù)遷移數(shù)據(jù)庫B數(shù)據(jù)庫A數(shù)據(jù)文件EXPORTIMPORTLOAD單表操作DB2MOVE多表操作DB2LOOK數(shù)據(jù)庫A腳本文件客戶端B客戶端A數(shù)據(jù)庫操作數(shù)據(jù)文件數(shù)據(jù)庫A數(shù)據(jù)移動格式定界ASCII文件格式非定界ASCII文件格式PC/IXF格式:

集成交換格式WSF工作表文件格式:

主要用于LOTUS軟件，WSF是一種專有的二進(jìn)制文件格式。游標(biāo)數(shù)據(jù)移動格式數(shù)據(jù)庫表DEL文件：可視IXF文件：不可視PC/IXF文件格式PC/IXF集成交換格式，包括表定義和數(shù)據(jù)ASC,DEL,WSF在跨平臺傳輸數(shù)據(jù)時可能會導(dǎo)致數(shù)據(jù)喪失.跨平臺建議使用PC/IXF文件格式游標(biāo)游標(biāo)是一個SELECT語句返回的結(jié)果集。這種格式僅限于LOAD使用，可以提高LOAD的性能。培訓(xùn)大綱數(shù)據(jù)移動格式導(dǎo)出〔EXPORT〕工具導(dǎo)入〔IMPORT〕工具導(dǎo)入〔LOAD〕工具db2move工具db2look工具.導(dǎo)出工具

EXPORTExport用于將數(shù)據(jù)從數(shù)據(jù)表中導(dǎo)出幾種格式的文件中。其實(shí)，它執(zhí)行了一個SQL的SELECT操作。支持的格式：IXF:是一種專用的二進(jìn)制格式，除數(shù)據(jù)外還包含表的結(jié)構(gòu)信息DEL:使用定界符的文本格式WSF:lotus支持的工作表格式DB2命令行,如:

$db2connecttosample

$db2exportto/home/db2inst1/test.ixfofixfselect*fromtestEXPORTEXPORT本質(zhì)是把一條SQL語句的結(jié)果集導(dǎo)出到一個文件中。EXPORT面向的是SQL而不單純是表。EXPORT支持的導(dǎo)出格式是DEL、WSF、IXF格式。EXPORT運(yùn)行前必須連接或隱式連接到數(shù)據(jù)庫。EXPORT會運(yùn)行COMMIT，因此在運(yùn)行前需要發(fā)出COMMIT或ROLLBACK語句來完成所有事務(wù)并釋放鎖定。EXPORT的命令EXPORTTOfilenameOFfiletype[MODIFIEDBYfiletype-mod[{filetype-mod}...]][MESSAGESmessage-file]select-statmentEXPORT的命令TOfilename用來指定將導(dǎo)出的數(shù)據(jù)放在<filename>文件中OFfiletype指定導(dǎo)出文件的類型，如IXF、DEL或WSF等。MODIFIEDBYfiletype-mod指定文件修飾符參數(shù)，比方CHARDELx用于指定字符串定界符，COLDELx用于指定字段定界符，x是定界符號。默認(rèn)的字符串定界符為雙引號(“”)默認(rèn)的字段定界符為逗號(,)TIMESTAMPFORMAT用于指定日期字段的時間表達(dá)格式MESSAGESmessage-file消息輸出文件，用來保存導(dǎo)出過程中發(fā)生的錯誤或警告信息select-statment利用SELECT語句指定要導(dǎo)出的數(shù)據(jù)EXPORT的例如db2exporttostuff.ixfofixfselect*fromstaffwheredept=20以IXF輸出格式將staff表中關(guān)于部門為20的職員信息導(dǎo)出到stuff.ixfdb2exporttomyfile.delofdelmodifiedbychardel''coldel;decpt,select*fromstaff以單引號作為字符串定界符，分號作為列定界符，逗號作為小數(shù)點(diǎn)，將數(shù)據(jù)導(dǎo)出到DEL文件。同時數(shù)據(jù)導(dǎo)回數(shù)據(jù)庫時應(yīng)使用同一約束測試數(shù)據(jù)db2=>select*fromt1EMPNONAMESEQNO

10你好's130xuxin350bbbbbb680lisi84條記錄已選擇。exporttot11.delofdelmodifiedbycodepage=1386chardel""coldel,messagest1.msgselect*fromt1;導(dǎo)出的文件內(nèi)容為：10,"你好's",130,"xuxin",350,"bbbbbb",680,"lisi",8Export語句案例測試(1)db2=>createtablet2(namevarchar(20),birthdaydate)db2=>insertintot2values('Tom','1989-1-1')db2=>insertintot2values('Jim','1983-10-1')db2=>select*fromt2NAMEBIRTHDAY

Tom1989-01-01Jim1983-10-012條記錄已選擇。exporttot11.delofdelmessagest1.msgselect*fromt2;導(dǎo)出的文件內(nèi)容為："Tom",19890101"Jim",19831001注意目前沒有找到對date類型的格式化接口。默認(rèn)導(dǎo)出格式為’YYYYMMDD’Export語句案例測試(2)db2=>createtablet3(namevarchar(20),tstimestamp)db2=>insertintot3values('Tom','1989-1-1')db2=>insertintot3values('Jack','1999-1-1')db2=>select*fromt3NAMETS

2條記錄已選擇。Export語句案例測試(6)exporttot11.delofdelmessagest1.msgselect*fromt3;導(dǎo)出的文件內(nèi)容為："Tom","1989-01-01-00.00.00.000000""Jack","1999-01-01-00.00.00.000000"exporttot11.delofdelmodifiedbytimestampformat="yyyy.mm.ddhh:mmtt"messagest1.msgselect*fromt3;導(dǎo)出的文件內(nèi)容為："Tom","1989.01.0112:00AM""Jack","1999.01.0112:00AM"Export語句案例測試(8)培訓(xùn)大綱數(shù)據(jù)移動格式導(dǎo)出〔EXPORT〕工具導(dǎo)入〔IMPORT〕工具導(dǎo)入〔LOAD〕工具db2move工具db2look工具.導(dǎo)入〔IMPORT〕工具Import用于將數(shù)據(jù)文件導(dǎo)入到數(shù)據(jù)表中。支持ASC,DEL,WSF,IXF主要方式:方式描述目標(biāo)表原有數(shù)據(jù)支持格式Insert插入操作需要存在保留所有格式insert_update更新鍵值操作需要存在保留或更新所有格式Repalce刪除原有數(shù)據(jù),插入新數(shù)據(jù)需要存在刪除所有格式Replace_Create刪除原有數(shù)據(jù),插入新數(shù)據(jù)不存在則創(chuàng)建刪除IXFCreate導(dǎo)入數(shù)據(jù)至新表創(chuàng)建新表無IXF導(dǎo)入〔IMPORT〕工具例:

$db2Importfromtest.ixfofixf

REPLACE_CREATEintotest1例:

$db2Createtabletest2likeact

$db2Importfrom/home/db2inst1/test.delofdel

INSERTintotest2導(dǎo)入〔IMPORT〕工具IMPORT會使用SQLINSERT語句向表IMPORT實(shí)質(zhì)是執(zhí)行INSERT\UPDATE\DELETESQL語句，因此會觸發(fā)觸發(fā)器、執(zhí)行日志記錄、執(zhí)行約束檢查和索引構(gòu)建。IMPORT運(yùn)行前必須連接或隱式連接到數(shù)據(jù)庫。IMPORT會運(yùn)行COMMIT或ROLLBACK，因此在運(yùn)行前需要發(fā)出COMMIT或ROLLBACK語句來完成所有事務(wù)并釋放鎖定。Commitcount(或automatic)指定每次提交的行數(shù)，以限制日志使用量和提高效率默認(rèn)向目標(biāo)表加X鎖，除非指定”allowwriteaccess”支持的格式：del,asc,wsf，ixf與ixf格式的文件結(jié)合可以創(chuàng)立表的結(jié)構(gòu)IMPORT的命令I(lǐng)MPORTFROMfilenameOFfiletype[ALLOWNO/WRITEACCESS][MODIFIEDBYfiletype-mod[{filetype-mod}...]][MESSAGESmessage-file][COMMITCOUNTn/AUTOMATIC]insert_create-statmentIMPORT的命令FROMfilename指定數(shù)據(jù)導(dǎo)入文件的名稱OFfiletype指定數(shù)據(jù)導(dǎo)入文件的類型，支持ASC\DEL\WSF\IXFMODIFIEDBYfiletype-mod指定文件修飾符參數(shù)MESSAGESmessage-file消息輸出文件ALLOWNOACCESS/ALLOWWRITEACCESS脫機(jī)導(dǎo)入/聯(lián)機(jī)導(dǎo)入COMMITCOUNTn指每n條記錄提交一次，也可設(shè)成AUTOMATIC，由DB自動設(shè)置COMMITCOUNT。IMPORT的命令insert_create-statment導(dǎo)入方式：1〕、已經(jīng)存在目標(biāo)表方式：INSERT\INSERT_UPDATE\REPLACE2〕、不存在目標(biāo)表方式：REPLACE_CREATE\CREATE只能匹配IXF格式。IMPORT的命令I(lǐng)mport例如 importfromfilename.ixfofixfcommitcount50000insertintotabnameimportfromfilename.ixfofixfcreateintotabname importfromartexprt.ixfofixfmessagesart.msgcreateintoartists[(column_list)] in<tablespace> indexin<indextablespace> longin<largetablespace>2024/5/2325IMPORT的命令培訓(xùn)大綱數(shù)據(jù)移動格式導(dǎo)出〔EXPORT〕工具導(dǎo)入〔IMPORT〕工具導(dǎo)入〔LOAD〕工具db2move工具db2look工具.導(dǎo)入〔LOAD〕工具方式描述目標(biāo)表原有數(shù)據(jù)支持格式Insert插入操作需要存在保留Load支持格式Repalce刪除原有數(shù)據(jù),插入新數(shù)據(jù)需要存在刪除Load支持格式TERMINATE模式終止裝載操作，并回滾到它開始時所在的時間點(diǎn)。一個例外是，如果指定了REPLACE模式，那么表將被清空RESTART用于重新開始之前被中斷的裝載命令，它將自動從最近的一致點(diǎn)繼續(xù)。要使用這個模式，可以指定與前一個LOAD命令相同的選項(xiàng)，但是這一次加上RESTARTLoad不能創(chuàng)立表及索引,故不能使用Create方式。主要方式:Load可以裝入的數(shù)據(jù)格式包括:IXF——也就是DB2export程序推薦的那種數(shù)據(jù)格式，是二進(jìn)制的。DEL——就是分界符的ASCIl文件，DB2默認(rèn)的是用逗號(,)作為字段列的分隔符,用雙引號(“”)作為字符串的分隔符；ASC——非定界符的ASCII文件，load時需要指定列的起止位置；CURSOR——游標(biāo)。導(dǎo)入〔LOAD〕工具導(dǎo)入〔LOAD〕工具不使用日志數(shù)據(jù)加載效率高使用異常表可使用db2listutilitiesshowdetail查看進(jìn)度支持的數(shù)據(jù)源：ixf,del,asc,cursorcursor方式提高一個數(shù)據(jù)庫內(nèi)數(shù)據(jù)移動的效率空文件可以用于快速清空一個表2024/5/2329Load的過程分為四個階段：裝入,構(gòu)建,刪除,索引復(fù)制。load階段是將源文件parser成物理數(shù)據(jù)存儲的格式,直接裝入到頁中,而不通過db2引擎。load階段會檢查表定義,違背定義的數(shù)據(jù)不會裝入到表中.

build階段建立索引〔如果裝入表有索引的話〕,會檢查唯一性約束,違背了唯一性的數(shù)據(jù)會在delete階段刪除.indexcopy階段將index數(shù)據(jù)從指定的臨時表空間拷貝到初始的表空間里.indexcopy只適應(yīng)于allowreadaccess場景.load的4個階段日志會記錄在messages文件里.導(dǎo)入〔LOAD〕工具參數(shù)解釋messages是追加寫文件dumpfile是覆蓋寫文件exception是追加寫表可以用modifiedby修飾符來指定：coldelx用來指定字段間分隔符chardelx用來指定字符串分隔符codepage用來指定編碼。db2=>loadfromt1.delofdelmodifiedbycoldel,chardel’’codepage=1386dumpfile=t1.dmpmessagest1.msginsertintot1forexceptiont1_exp導(dǎo)入〔LOAD〕工具導(dǎo)入〔LOAD〕工具設(shè)置為歸檔日志模式的數(shù)據(jù)庫的本卷須知Copyno默認(rèn)方式執(zhí)行l(wèi)oad后，目標(biāo)表所在的表空間將被置為backuppending狀態(tài)，只有對其執(zhí)行備份后，表空間才能被訪問。目標(biāo)表可以讀，但不能進(jìn)行增/刪/改。而且load命令一旦發(fā)起，表空間立即處于backuppending狀態(tài)，即使終止load操作，也不能脫離此狀態(tài)。Copyyes選項(xiàng)會在load結(jié)束時，自動對表所屬的表空間做一次備份，load結(jié)束后，表空間狀態(tài)為正常。但由于要備份，所需時間要長些Nonrecoverable目標(biāo)表空間不會被置為backuppending。影響使用數(shù)據(jù)庫的備份執(zhí)行恢復(fù)和前滾。速度快Nonrecoverable將表標(biāo)記為不可恢復(fù)，如果以后需要恢復(fù)表空間并且回滾到nonrecoverableload選項(xiàng)之后的某個時間點(diǎn)，這個表是不可恢復(fù)的，所有與該表相關(guān)的日志會被忽略，你只能刪除并重新創(chuàng)立表。因此，這個方法一般用在表可以被重建的場景中。2024/5/2332導(dǎo)入〔LOAD〕工具查看表的狀態(tài)：loadquerytableschema.tab Normal CheckPending–setintegrity LoadinProgress LoadPending ReadAccessOnly Unavailable NotLoadRestartable Unknown2024/5/2333導(dǎo)入〔LOAD〕工具CheckpendingLoad工具在執(zhí)行過程中關(guān)閉約束檢查如果父表處于checkpending，那么依賴的表也會處于checkpendingSETINTEGRITY用于將處于“checkpending”狀態(tài)的表恢復(fù)至正常狀態(tài)例如： SETINTEGRITYFORSALESALLOWREADACCESSIMMEDIATECHECKEDFOREXCEPTIONINSALESUSESALES_EXCEPTIONS2024/5/2334db2=>createtablet2(col1char(10),col2char(10))db2=>altertablet2addconstraintcheck1CHECK(col2in('A','B','C'))db2=>loadfromf:/t2.delofdelinsertintot2db2=>select*fromt2COL1COL2

SQL0668N不允許對表"HANDSOME.T2"執(zhí)行操作，原因碼為"1"。SQLSTATE=57016db2=>setintegrityforhandsome.t2immediatecheckedDB21034E該命令被當(dāng)作SQL語句來處理，因?yàn)樗菬o效的“命令行處理器”命令。在SQL處理期間，它返回：SQL3603N通過SETINTEGRITY語句來執(zhí)行完整性處理時發(fā)現(xiàn)了完整性違例，此違例涉及到約束、唯一索引、已生成的列或者基于XML列的索引。由"HANDSOME.T2.CHECK1"標(biāo)識了相關(guān)聯(lián)的對象。SQLSTATE=23514導(dǎo)入〔LOAD〕工具db2=>createtablet2_expliket2DB20000ISQL命令成功完成。db2=>setintegrityforhandsome.t2immediatecheckedforexceptionint2uset2_expSQL3602W檢查數(shù)據(jù)處理找到約束違例，并將它們移至異常表。SQLSTATE=01603db2=>select*fromt2_expCOL1COL2

CCCCCCDDDBBB2條記錄已選擇。導(dǎo)入〔LOAD〕工具以下語句可用來檢查表的狀態(tài)、是否允許訪問及表的主外鍵約束、檢查約束等。db2=>selecttabname,status,access_mode,substr(const_checked,1,1)asfk_checked,substr(const_checked,2,1)ascc_checkedfromsyscat.tableswherestatus='C'導(dǎo)入〔LOAD〕工具導(dǎo)入〔LOAD〕工具

DEL文件分隔符的指定Load工具可以使用modifiedbycoldelx指定特定分隔符。1386編碼下，”|”作為分隔符會發(fā)生處理故障DEL文件格式注意DEL文件的編碼格式是否與目標(biāo)操作系統(tǒng)環(huán)境相吻合?？梢越柚恍┕ぞ哌M(jìn)行轉(zhuǎn)換代碼頁轉(zhuǎn)換問題使用load工具可以使用modifiedbycodepage=1386來指定代碼頁。Ixf格式的文件一般會自動轉(zhuǎn)換代碼頁2024/5/2338導(dǎo)入〔LOAD〕工具編碼問題的典型案例需要將編碼為1386(GBK)的以”|”作為分隔符的del格式文件內(nèi)容，導(dǎo)入到1208(UTF-8)編碼的數(shù)據(jù)庫中。處理方法-任選一個將分隔符替換為逗號分割的文件使用load的modifiedbycodepage=1386或設(shè)置db2setCODEPAGE=1386，然后使用import導(dǎo)入或?qū)⒃嘉募木幋a轉(zhuǎn)換為UTF-8，設(shè)置db2setCODEPAGE=1208,使用load的modifiedbycoldel|導(dǎo)入2024/5/2339發(fā)生截斷時，拋出“SQL3125W因?yàn)閿?shù)據(jù)比目標(biāo)數(shù)據(jù)庫列長，截斷后的數(shù)據(jù)還是會寫入目標(biāo)表。類型不匹配的，拋出類似如下的異常：如果目標(biāo)字段沒有NOTNULL約束，目標(biāo)字段的值為nullSQL3119W不能將字段值轉(zhuǎn)換成INTEGER值。裝入了空值。否那么：SQL3120W不能將字段值轉(zhuǎn)換成INTEGER值，但目標(biāo)列不可為空。未裝入該行。并將該記錄寫入dumpfile對應(yīng)的輸出文件中。導(dǎo)入〔LOAD〕工具導(dǎo)入〔LOAD〕工具例如 db2loadfromstafftab.ixfofixfmodifiedbyanyordermessagesstaff.msgstempfilespath/u/myuserreplaceintostaffcopyyesto/dbbak/tmp/DATABUFFER10240CPU_PARALLELISM8

DECLAREmycursCURSORDATABASEdbsourceFORSELECTTWO,ONE,THREEFROMabc.table1 LOADFROMmycursOFcursorINSERTINTOabc.table2 createtablet1excas(SELECTT1.*,CURRENTTIMESTAMPASTS,CLOB('',32767)ASMSGFROMT1)definitiononly db2loadfromfilename.delofdelinsertintotabnamemessagesmsg.1forexceptiont1exc2024/5/2341培訓(xùn)大綱數(shù)據(jù)移動格式導(dǎo)出〔EXPORT〕工具導(dǎo)入〔IMPORT〕工具導(dǎo)入〔LOAD〕工具db2move工具db2look工具db2move用于將數(shù)據(jù)庫所有的表導(dǎo)出、導(dǎo)入〔遷移庫,尤其是不同操作系統(tǒng)間，不同版本數(shù)據(jù)庫間〕。支持的action：export,import,load,copy。自動的導(dǎo)出和導(dǎo)入一批表。導(dǎo)入(import,load)自動讀取db2move.lst文件。Copy模式下可以用于將源數(shù)據(jù)庫指定模式下的所有對象復(fù)制到目標(biāo)數(shù)據(jù)庫〔ADMIN_COPY_SCHEMA可以用于在數(shù)據(jù)庫內(nèi)部復(fù)制不同模式的對象〕2024/5/2343db2move語法：db2movedbnameaction-tctable-definers-tntable-names-snschema-names-tstablespace-names-tffilename-ioimport-option-loload-option-cocopy-option-uuserid-ppassword-aw2024/5/2344db2move-ioimport-optioncreate,insert,insert_update,replace,

replace_create(def)-loload-option

insert(def),replace-tfexport-option-filenamelistexportedtablenamedb2moveExport，Import和Load每次都只對一個表進(jìn)行操作。但是您假設(shè)使用它們來寫一段腳本，就可對一個數(shù)據(jù)庫中的所有的表進(jìn)行操作。db2move可以更方便地完成同樣的工作。db2move工具只兼容IXF格式的文件。db2move工具語法格式:

$db2move<dbname><Action><Option>Action:Exoprt,Import,Load,Copy$db2moves