1、單元目標單元目標了解INSERT語句和它的限制EXPORT,IMPORT和LOAD的用法Load可能產(chǎn)生的幾種表/表空間狀態(tài) Load Pending Set Integrity Pending Backup Pending(db/ts)SET INTEGRITY 命令使用db2move和和db2look 用法db2dart導數(shù)據(jù)在線表遷移（Admin_Move_Table存儲過程）數(shù)據(jù)遷移方法數(shù)據(jù)遷移方法同平臺，不同機器 備份/恢復不同平臺數(shù)據(jù)庫間 Export/import/load/db2move等 Load from cursor同數(shù)據(jù)庫不同表 Insert select Export

2、/import/load庫不可連接 db2dart數(shù)據(jù)導入導出工具一覽數(shù)據(jù)導入導出工具一覽DEL格式格式文本格式每行代表一條記錄字段之間用分隔符分隔，如,;!等某字段字符串用符號包圍，如，“”等非非DEL的的ACSII格式格式文本格式每行代表一條記錄字段是定長表示，不足用空格補齊PC/IXF格式格式PC/IXF格式是IBM特有的二進制格式用于在異構平臺間導入導出數(shù)據(jù)可包含表的類型和定義db2move支持的格式INSERT 語句語句INSERT語句: 有overhead，需要通過引擎，驗證RI，Check，唯一性約束，執(zhí)行trigger 插入操作發(fā)生時，需要記日志在處理大數(shù)據(jù)量方面，INSERT

3、可能不是最快的,也不是最好的辦法insert into artists (artno, name, classification) values (100, Patti & Cart Wheels, S)Insert into emptemp select * from employeeIMPORT 命令語法命令語法(Basic)IMPORT FROMOFfilenamefiletypeLOBS FROMlob-path,MODIFIED BYfiletype-modCOMMITCOUNTRESTARTCOUNTn/nMESSAGESmessage-fileINSERTINSERT_UPDAT

4、EREPLACEREPLACE_CREATEINTOtable-name(insert-column,CREATEINTOtable-name(insert-column)| tblspace-specs |,tblspace-specs|IN tablespace-nameINDEX IN tablespace-name|INDEX IN tablespace-nameLONG IN tablespace-nameALLOW WRITE ACCESSALLOW NO ACCESSAutomatic. . .IMPORT 例子例子將文件數(shù)據(jù)導入到表中db2 connect to musicdb

5、db2 import from artexprt of ixf messages artmsg into artistsdb2 connect to musicdbdb2 import from artexprt of ixf messages artmsg insert into artistscreateinsertinsert_updatereplacereplace_createartexprtartistsIMPORTmusicdbDB2IMPORT 大對象處理大對象處理CLOBIMPORT modified by修飾符修飾符ColdelChardelIMPORT 和和 LOAD 對

6、比對比IMPORTLOAD導入大數(shù)據(jù)時慢可通過IXF格式創(chuàng)建表所有行都記日志會觸發(fā)觸發(fā)器，約束等處理大數(shù)據(jù)量時速度快Load前，表和索引必須存在已有的數(shù)據(jù)仍然可讀記很少的日志; can make copy不會觸發(fā)trigger，RI和check約束在set integrity階段驗證；會驗證唯一性約束Load 過程的過程的4個階段個階段1.LOADLoad data into tablesCollect index keys / sortConsistency points at SAVECOUNTInvalid data rows in dump file; messages in mess

7、age fileIndexes created or updated2.BUILDDB2 Data3.DELETEUnique Key Violations placed in Exception TableMessages generated for unique key violationsDeletes Unique Key Violation Rows4.INDEX COPYCopy indexes from temp table space to index table spaceLOAD 命令語法命令語法(Basic)MODIFIED BYfiletype-modLOADINSER

8、TREPLACERESTARTTERMINATEINTOtable-name(insert-column,)FOR EXCEPTIONtable-nameFROMASCDELIXFCURSOROFfilepipedevicecursorname,| statistics options |copy options|ALLOW NO ACCESSALLOW READ ACCESSUSE tablespace-nameSAVECOUNT nROWCOUNT nWARNINGCOUNT nMESSAGESmsg-file . . .| set integrity pending options |L

9、OCK WITH FORCECLIENTLOAD 典型場景典型場景30 3cal.par10 1Primary KeyTablecal.parexp30 4 timestamp msg.not null, numeric columnException Tablepar.msgs.msg.Rows not LoadedOUTPUT10 RIDUNIQUE INDEXdb2 load from calpar.del of del modified by dumpfile=/dump.fil warningcount 100 messages par.msgs insert into cal.pa

10、r for exception cal.parexpdb2 load query table cal.par20 -30 430 340 X50 650 780 8INPUTcalpar.del10 1 50 680 8 50 7 timestamp msg 20 msg40 msg30 RID50 RID80 RIDcreate tables/indexesobtain delimited input file in sorted formatcreate exception tableexamine par.msgs fileexamine cal.parexp exception tab

11、ledump.fil.00020 -40 X創(chuàng)建異常表創(chuàng)建異常表(Exception Tables)前N個字段與原表字段相同沒有約束和觸發(fā)器定義第 n + 1 個字段，TIMESTAMP 類型第 n + 2 個字段，CLOB (32 KB)類型CREATE TABLE T1EXC LIKE T1ALTER TABLE T1EXC ADD COLUMN TS TIMESTAMP ADD COLUMN MSG CLOB(32K)CREATE TABLE T1EXC AS (SELECT T1.*, CURRENT TIMESTAMP AS TS, CLOB(, 32767) AS MSG FROM

12、 T1) DEFINITION ONLY離線離線/在線加載在線加載 (Offline versus Online Load)ALLOW NO ACCESSALLOW READ ACCESSTimeLock requestedLock grantedLoad allows no accessread/writeread/writeLoad commitTimeLoad commitread/writereadreadLoad allows read accessSuper exclusivelock requestedSuper exclusive lock grantedDrain reque

13、stedDrain grantedr/w表狀態(tài)表狀態(tài)(Table states)(Load pending, Set Integrity Pending)LOAD QUERY TABLE Tablestate:Normal Set Integrity Pending Load in ProgressLoad PendingReorg Pending Read Access Only Unavailable Not Load RestartableUnknownTable can be in several states at same timeTablestate:Set Integrity

14、PendingLoad in ProgressRead Access OnlyLoad Pending狀態(tài)狀態(tài): 從從LOAD 失敗中恢復失敗中恢復Restart the Load: 檢查消息文件 使用restart選項 Load操作自動從上一個一致點繼續(xù)加載 LOAD RESTARTReplace the whole table: LOAD . REPLACETerminate the Load: 如果是LOAD . INSERT, 返回到加載前的數(shù)據(jù)狀態(tài) 如果是LOAD . REPLACE, 表會清空 建議做好備份不要刪除LOAD過程中產(chǎn)生的臨時文件對于歸檔日志模式的數(shù)據(jù)庫，有三種Load

15、選項： COPY NO (default) During load, Backup pending and Load in progress After load, Backup Pending COPY YES Load has made Copy not Backup pending NONRECOVERABLE No copy made and no backup requiredBackup Pending狀態(tài)狀態(tài): COPY選項選項UNIXDatabasealias.Type.Instancename.Nodename.Catnodename.Timestamp.numberWind

16、owsType:0=Full Backup3=Table Space Backup4 =Copy from Table LoadDatabasealias.Type.Instancename.Nodename.Catnodename.Timestamp.numberSet Integrity Pending表狀態(tài)表狀態(tài)Load 關閉約束檢查: 將表置于Set Integrity Pending 狀態(tài) 如果父表處于Set Integrity Pending狀態(tài)，那么子表也可能處于該狀態(tài) LOAD INSERT, ALLOW READ ACCESS Loaded table in Set Inte

17、grity Pending with read access LOAD INSERT, ALLOW NO ACCESS Loaded table in Set Integrity Pending with no access LOAD REPLACE, SET INTEGRITY PENDING CASCADE DEFERRED Loaded table in Set Integrity Pending with no access LOAD REPLACE, SET INTEGRITY PENDING CASCADE IMMEDIATE Loaded table and descendant

18、 foreign key tables are in Set Integrity Pending with no accessSET INTEGRITY 命令語法命令語法exception-clauseSET INTEGRITYFORFORtable-nametable-nameOFFIMMEDIATE CHECKEDALL| exception-clause |IMMEDIATE UNCHECKEDFOREIGN KEYCHECK,FOR EXCEPTIONINtable-nameUSEtable-name|,. . .Set Integrity Pending state1、如果愿意接受l

19、oad完的數(shù)據(jù)有違反這2類約束的垃圾數(shù)據(jù)存在：set integrity for t foreign key immediate uncheckedset integrity for t check immediate unchecked此時表狀態(tài)為normal，但是以后新事務中的違反約束是不能成功的。Set Integrity Pending state (2)2、如果不能接受load完的數(shù)據(jù)有違反這2類約束的垃圾數(shù)據(jù)存在：set integrity for t immediate checked當想保存異常數(shù)據(jù)時，還是使用異常表，后邊加上for exception in t use t_e

20、x5種約束：1、not null，2、unique，3、PK，4、check，5、FKLoad數(shù)據(jù)時，只檢查只檢查1，2，3，而不檢查，而不檢查4，5A）當load遇到1、2、3時：load會丟掉不理！只裝入滿足這三種約束的記錄，此時你會看到load結束時Number of rows deleted不等于0，如果想知道load丟掉的記錄具體有哪些，建異常表保存起來。create table t_ex like talter table t_ex add column time timestampload from t.ixf of ixf insert into t for exception

21、 t_exLOAD約束處理總結約束處理總結B) 當load遇到4、5時：load不管數(shù)據(jù)是否滿足約束，都會正常裝入！但是load只要發(fā)現(xiàn)表存在check或FK定義，立即會把表置成set integrity pending狀態(tài)當db2把表、表空間或是數(shù)據(jù)庫置于pending狀態(tài)時，都是為了保護數(shù)據(jù)，對應都有解除pending的手段。用set integrity命令解除Set integrity pending狀態(tài)LOAD 步驟總結步驟總結創(chuàng)建表和索引創(chuàng)建異常表排序數(shù)據(jù)備份表空間/DB（如果使用了Replace)Load . for Exception . Savecount . Warningc

22、ount.驗證xx.msg文件和dumpfile文件驗證異常表對于歸檔模式的數(shù)據(jù)庫，使用copy no選項時，需要備份表空間執(zhí)行set integrity for （當處于set integrity pending狀態(tài)時）更新統(tǒng)計信息（如果必要的話）db2move 工具概述工具概述在不同數(shù)據(jù)庫間遷移數(shù)據(jù)（相同或異構平臺）當有大量表需要遷移時很有用可以在以下幾種模式下運行: Export: 將表數(shù)據(jù)導出到 IXF 文件中 Import: 將 IXF文件類型數(shù)據(jù)導入到數(shù)據(jù)庫表中 Load: The input files specified in the db2move.lst file are

23、loaded into the tables using the LOAD utilitydb2move 工具語法工具語法table-definerstable-namesschema-namestablespace-namesfilenameimport-optionload-optioncopy-optionlobpathsuseridpasswordtctnsntstfiocolupawlodb2move,dbnameactiondb2move 實例實例導出SAMPLE數(shù)據(jù)庫的所有表數(shù)據(jù): db2move sample export 導出所有userid1或us%rid2用戶建的，并且表

24、名字是tabname1或%tabname2的表: db2move sample export -tc userid1,us*rid2 -tn tbname1,*tbname2 導入數(shù)據(jù)到sample數(shù)據(jù)庫中，并指定LOBS文件存放的位置: db2move sample import -l D:LOBPATH1,C:LOBPATH2 裝入數(shù)據(jù)到sample數(shù)據(jù)庫中，并指定LOBS文件存放的位置: db2move sample load -l /home/userid/lobpath,/tmp db2look 工具概述工具概述導出對象定義，如建表、索引、視圖、存儲過程、函數(shù)等對象定義 db2look d -e l db2look.ddl導出表的統(tǒng)計信息 db2look d -z -t -m db2look 語法語法Creator-edb2look-dDBname-uschema-z-tTnameTname-tw-vVname-ct-dp-hFname-o-a-m-c-rdelimiter-td-x-l-xd-fpassword-w-noview-iuseridWnameSname-wrapper-nofed-server-xsdirname-xdirdb2look 實