(6)数据迁移专题

1、单元目标单元目标了解INSERT语句和它的限制EXPORT,IMPORT和LOAD的用法Load可能产生的几种表/表空间状态 Load Pending Set Integrity Pending Backup Pending(db/ts)SET INTEGRITY 命令使用db2move和和db2look 用法db2dart导数据在线表迁移（Admin_Move_Table存储过程）数据迁移方法数据迁移方法同平台，不同机器 备份/恢复不同平台数据库间 Export/import/load/db2move等 Load from cursor同数据库不同表 Insert select Export

2、/import/load库不可连接 db2dart数据导入导出工具一览数据导入导出工具一览DEL格式格式文本格式每行代表一条记录字段之间用分隔符分隔，如,;!等某字段字符串用符号包围，如，“”等非非DEL的的ACSII格式格式文本格式每行代表一条记录字段是定长表示，不足用空格补齐PC/IXF格式格式PC/IXF格式是IBM特有的二进制格式用于在异构平台间导入导出数据可包含表的类型和定义db2move支持的格式INSERT 语句语句INSERT语句: 有overhead，需要通过引擎，验证RI，Check，唯一性约束，执行trigger 插入操作发生时，需要记日志在处理大数据量方面，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_U

4、PDATEREPLACEREPLACE_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 例子例子将文件数据导入到表中db2 connect to mus

5、icdbdb2 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 和和 LO

6、AD 对比对比IMPORTLOAD导入大数据时慢可通过IXF格式创建表所有行都记日志会触发触发器，约束等处理大数据量时速度快Load前，表和索引必须存在已有的数据仍然可读记很少的日志; can make copy不会触发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

7、message 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-modLOADI

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

9、s |LOCK 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 ca

10、l.par 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

11、 tabledump.fil.00020 -40 X创建异常表创建异常表(Exception Tables)前N个字段与原表字段相同没有约束和触发器定义第 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

12、FROM 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 r

13、equestedDrain grantedr/w表状态表状态(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 Integr

14、ity PendingLoad in ProgressRead Access OnlyLoad Pending状态状态: 从从LOAD 失败中恢复失败中恢复Restart the Load: 检查消息文件 使用restart选项 Load操作自动从上一个一致点继续加载 LOAD RESTARTReplace the whole table: LOAD . REPLACETerminate the Load: 如果是LOAD . INSERT, 返回到加载前的数据状态 如果是LOAD . REPLACE, 表会清空 建议做好备份不要删除LOAD过程中产生的临时文件对于归档日志模式的数据库，有三种

15、Load选项： 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状态状态: COPY选项选项UNIXDatabasealias.Type.Instancename.Nodename.Catnodename.Timestamp.number

16、WindowsType:0=Full Backup3=Table Space Backup4 =Copy from Table LoadDatabasealias.Type.Instancename.Nodename.Catnodename.Timestamp.numberSet Integrity Pending表状态表状态Load 关闭约束检查: 将表置于Set Integrity Pending 状态 如果父表处于Set Integrity Pending状态，那么子表也可能处于该状态 LOAD INSERT, ALLOW READ ACCESS Loaded table in Set

17、Integrity 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 descen

18、dant 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、如果愿

19、意接受load完的数据有违反这2类约束的垃圾数据存在：set integrity for t foreign key immediate uncheckedset integrity for t check immediate unchecked此时表状态为normal，但是以后新事务中的违反约束是不能成功的。Set Integrity Pending state (2)2、如果不能接受load完的数据有违反这2类约束的垃圾数据存在：set integrity for t immediate checked当想保存异常数据时，还是使用异常表，后边加上for exception in t use

20、 t_ex5种约束：1、not null，2、unique，3、PK，4、check，5、FKLoad数据时，只检查只检查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 excep

21、tion t_exLOAD约束处理总结约束处理总结B) 当load遇到4、5时：load不管数据是否满足约束，都会正常装入！但是load只要发现表存在check或FK定义，立即会把表置成set integrity pending状态当db2把表、表空间或是数据库置于pending状态时，都是为了保护数据，对应都有解除pending的手段。用set integrity命令解除Set integrity pending状态LOAD 步骤总结步骤总结创建表和索引创建异常表排序数据备份表空间/DB（如果使用了Replace)Load . for Exception . Savecount . Warn

22、ingcount.验证xx.msg文件和dumpfile文件验证异常表对于归档模式的数据库，使用copy no选项时，需要备份表空间执行set integrity for （当处于set integrity pending状态时）更新统计信息（如果必要的话）db2move 工具概述工具概述在不同数据库间迁移数据（相同或异构平台）当有大量表需要迁移时很有用可以在以下几种模式下运行: Export: 将表数据导出到 IXF 文件中 Import: 将 IXF文件类型数据导入到数据库表中 Load: The input files specified in the db2move.lst file

23、are loaded into the tables using the LOAD utilitydb2move 工具语法工具语法table-definerstable-namesschema-namestablespace-namesfilenameimport-optionload-optioncopy-optionlobpathsuseridpasswordtctnsntstfiocolupawlodb2move,dbnameactiondb2move 实例实例导出SAMPLE数据库的所有表数据: db2move sample export 导出所有userid1或us%rid2用户建的

24、，并且表名字是tabname1或%tabname2的表: db2move sample export -tc userid1,us*rid2 -tn tbname1,*tbname2 导入数据到sample数据库中，并指定LOBS文件存放的位置: db2move sample import -l D:LOBPATH1,C:LOBPATH2 装入数据到sample数据库中，并指定LOBS文件存放的位置: db2move sample load -l /home/userid/lobpath,/tmp db2look 工具概述工具概述导出对象定义，如建表、索引、视图、存储过程、函数等对象定义 db2look d -e l db2look.ddl导出表的统计信息 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