db2培训(7)数据迁移专题.ppt

《db2培训(7)数据迁移专题.ppt》由会员分享，可在线阅读，更多相关《db2培训(7)数据迁移专题.ppt（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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_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 例子例子将文件数据导入到表中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导入大数据时慢可通过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 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创建异常表创建异常表(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 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表状态表状态(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状态状态: 从从LOAD 失败中恢复失败中恢复Restart the Load: 检查消息文件 使用restart选项 Load操作自动从上一个一致点继续加载 LOAD RESTARTReplace the whole table: LOAD . REPLACETerminate the Load: 如果是LOAD . INSERT, 返回到加载前的数据状态 如果是LOAD . REPLACE, 表会清空 建议做好备份不要删除LOAD过程中产生的临时文件对于归档日志模式的数据库，有三种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状态状态: 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表状态表状态Load 关闭约束检查: 将表置于Set Integrity Pending 状态 如果父表处于Set Integrity Pending状态，那么子表也可能处于该状态 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完的数据有违反这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 t_e

20、x5种约束：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 exception

21、 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 . Warningc

22、ount.验证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 are

23、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 工具概述工具概述导出对象定义，如建表、索引、视图、存储过程、函数等对象定义 db2loo

25、k 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 实例实例导出用户walid创建的对象: db2look -d department -u walid -e -o db2l

26、ook.sql 导出用户walid创建，schema名为ianhe的对象: db2look -d department -u walid -z ianhe -e -o db2look.sql 产生用户walid创建的对象的统计信息: db2look -d department -u walid -m -o db2look.sql Generate both the DDL statements for the objects created by user walid and the UPDATE statements to replicate the statistics on the da

27、tabase objects created by the same user. The db2look output is sent to file db2look.sql: db2look -d department -u walid -e -m -o db2look.sql Db2dart导数据导数据当硬盘损坏，日志破坏而没有数据库备份的时候，db2dart是救命稻草db2dart /ddel 每张表数据导出在多个仓库系统故障中使用 Copyright IBM Corporation 2009表数据从一个表空间迁移到另外一个表空间表数据从一个表空间迁移到另外一个表空间典型做法是新建一个表空间，在此表空间创建与原表相同的表结构（假设为t2），然后将原表t1数据导出，再导入到t2表，完成测试后，将t1表改名或删除，将t2表改为t1。9.7版本引入了在线数据迁移方案，通过ADMIN_MOVE_TABLE存储过程将表数据从一个表空间迁移到另外一个表空间，迁移过程中保持对原表数据的持续访问。Student exercise