数据迁移方法

单元目标了解INSERT语句和它的限制EXPORT,IMPORT和LOAD的用法Load可能产生的几种表/表空间状态LoadPendingSetIntegrityPendingBackupPending(db/ts)SETINTEGRITY命令使用db2move和db2look

用法db2dart导数据在线表迁移（Admin_Move_Table存储过程）数据迁移方法同平台，不同机器备份/恢复不同平台数据库间Export/import/load/db2move等Loadfromcursor同数据库不同表Insert…selectExport/import/load库不可连接db2dart数据导入导出工具一览DEL格式文本格式每行代表一条记录字段之间用分隔符分隔，如,;!等某字段字符串用符号包围，如‘’，“”等非DEL的ACSII格式文本格式每行代表一条记录字段是定长表示，不足用空格补齐PC/IXF格式PC/IXF格式是IBM特有的二进制格式用于在异构平台间导入导出数据可包含表的类型和定义db2move支持的格式INSERT语句INSERT语句:有overhead，需要通过引擎，验证RI，Check，唯一性约束，执行trigger插入操作发生时，需要记日志在处理大数据量方面，INSERT可能不是最快的,也不是最好的办法insertintoartists(artno,name,classification)values(100,'Patti&CartWheels','S')Insertintoemptempselect*fromemployeeIMPORT命令语法(Basic)IMPORTFROMOFfilenamefiletypeLOBSFROMlob-path,MODIFIEDBYfiletype-modCOMMITCOUNTRESTARTCOUNTn/nMESSAGESmessage-fileINSERTINSERT_UPDATEREPLACEREPLACE_CREATEINTOtable-name(insert-column,CREATEINTOtable-name(insert-column))|tblspace-specs|,tblspace-specs|INtablespace-nameINDEXINtablespace-name|INDEXINtablespace-nameLONGINtablespace-nameALLOWWRITEACCESSALLOWNOACCESSAutomatic...IMPORT大对象处理CLOBIMPORTmodifiedby修饰符ColdelChardelIMPORT和LOAD对比IMPORTLOAD导入大数据时慢可通过IXF格式创建表所有行都记日志会触发触发器，约束等处理大数据量时速度快Load前，表和索引必须存在已有的数据仍然可读记很少的日志;canmakecopy不会触发trigger，RI和check约束在setintegrity阶段验证；会验证唯一性约束Load过程的4个阶段LOAD命令语法(Basic)MODIFIEDBYfiletype-modLOADINSERTREPLACERESTARTTERMINATEINTOtable-name(insert-column,)FOREXCEPTIONtable-nameFROMASCDELIXFCURSOROFfilepipedevicecursorname,|statisticsoptions||copyoptions|ALLOWNOACCESSALLOWREADACCESSUSEtablespace-nameSAVECOUNTnROWCOUNTnWARNINGCOUNTnMESSAGESmsg-file...|setintegritypendingoptions|LOCKWITHFORCECLIENTLOAD典型场景创建异常表(ExceptionTables)前N个字段与原表字段相同没有约束和触发器定义第n+1个字段，TIMESTAMP类型第n+2个字段，CLOB(32KB)类型CREATETABLET1EXCLIKET1ALTERTABLET1EXCADDCOLUMNTSTIMESTAMPADDCOLUMNMSGCLOB(32K)CREATETABLET1EXCAS(SELECTT1.*,CURRENTTIMESTAMPASTS,CLOB('',32767)ASMSGFROMT1)DEFINITIONONLY离线/在线加载(OfflineversusOnlineLoad)ALLOWNOACCESSALLOWREADACCESS表状态(Tablestates)(Loadpending,SetIntegrityPending)LOADQUERYTABLE<table-name>Tablestate:NormalSetIntegrityPendingLoadinProgressLoadPendingReorgPendingReadAccessOnlyUnavailableNotLoadRestartableUnknownTablecanbeinseveralstatesatsametimeTablestate:SetIntegrityPendingLoadinProgressReadAccessOnlyLoadPending状态:

从LOAD失败中恢复RestarttheLoad:检查消息文件使用restart选项Load操作自动从上一个一致点继续加载LOAD…RESTARTReplacethewholetable:LOAD...REPLACETerminatetheLoad:如果是LOAD...INSERT,返回到加载前的数据状态如果是LOAD...REPLACE,表会清空建议做好备份不要删除LOAD过程中产生的临时文件对于归档日志模式的数据库，有三种Load选项：COPYNO(default)Duringload,BackuppendingandLoadinprogressAfterload,BackupPending

COPYYESLoadhasmadeCopynotBackuppendingNONRECOVERABLENocopymadeandnobackuprequiredBackupPending状态:COPY选项UNIXDatabasealias.Type.Instancename.Nodename.Catnodename.Timestamp.numberWindowsType:0=FullBackup3=TableSpaceBackup4=CopyfromTableLoadDatabasealias.Type.Instancename.Nodename.Catnodename.Timestamp.numberSetIntegrityPending表状态Load关闭约束检查:将表置于SetIntegrityPending状态如果父表处于SetIntegrityPending状态，那么子表也可能处于该状态LOADINSERT,ALLOWREADACCESSLoadedtableinSetIntegrityPendingwithreadaccessLOADINSERT,ALLOWNOACCESSLoadedtableinSetIntegrityPendingwithnoaccessLOADREPLACE,SETINTEGRITYPENDINGCASCADEDEFERREDLoadedtableinSetIntegrityPendingwithnoaccessLOADREPLACE,SETINTEGRITYPENDINGCASCADEIMMEDIATELoadedtableanddescendantforeignkeytablesareinSetIntegrityPendingwithnoaccessSETINTEGRITY命令语法exception-clauseSETINTEGRITYFORFORtable-nametable-nameOFFIMMEDIATECHECKEDALL|exception-clause|IMMEDIATEUNCHECKEDFOREIGNKEYCHECK,,,FOREXCEPTIONINtable-nameUSEtable-name||,...SetIntegrityPendingstate1、如果愿意接受load完的数据有违反这2类约束的垃圾数据存在：setintegrityfortforeignkeyimmediateuncheckedsetintegrityfortcheckimmediateunchecked此时表状态为normal，但是以后新事务中的违反约束是不能成功的。SetIntegrityPendingstate(2)2、如果不能接受load完的数据有违反这2类约束的垃圾数据存在：setintegrityfortimmediatechecked当想保存异常数据时，还是使用异常表，后边加上forexceptionintuset_ex5种约束：1、notnull，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丢掉的记录具体有哪些，建异常表保存起来。createtablet_exliketaltertablet_exaddcolumntimetimestamploadfromt.ixfofixfinsertintotforexceptiont_exLOAD约束处理总结B)当load遇到4、5时：load不管数据是否满足约束，都会正常装入！但是load只要发现表存在check或FK定义，立即会把表置成setintegritypending状态当db2把表、表空间或是数据库置于pending状态时，都是为了保护数据，对应都有解除pending的手段。用setintegrity命令解除Setintegritypending状态LOAD步骤总结创建表和索引创建异常表排序数据备份表空间/DB（如果使用了Replace)Load....forException...Savecount...Warningcount...验证xx.msg文件和dumpfile文件验证异常表对于归档模式的数据库，使用copyno选项时，需要备份表空间执行setintegrityfor…（当处于setintegritypending状态时）更新统计信息（如果必要的话）db2move工具概述在不同数据库间迁移数据（相同或异构平台）当有大量表需要迁移时很有用可以在以下几种模式下运行:Export:

将表数据导出到IXF文件中Import:

将IXF文件类型数据导入到数据库表中Load:Theinputfilesspecifiedinthedb2move.lstfileareloadedintothetablesusingtheLOADutilitydb2move工具语法table-definerstable-namesschema-namestablespace-namesfilenameimport-optionload-optioncopy-optionlobpathsuseridpasswordtctnsntstfiocolupawlodb2move,dbnameactiondb2move实例导出SAMPLE数据库的所有表数据:db2movesampleexport

导出所有userid1或us%rid2用户建的，并且表名字是tabname1或%tabname2的表:db2movesampleexport-tcuserid1,us*rid2-tntbname1,*tbname2

导入数据到sample数据库中，并指定LOBS文件存放的位置:db2movesampleimport-lD:\LOBPATH1,C:\LOBPATH2

装入数据到sample数据库中，并指定LOBS文件存放的位置:db2movesampleload-l/home/userid/lobpath,/tmpdb2look工具概述导出对象定义，如建表、索引、视图、存储过程、函数等对象定义db2look–d<db_name>-e–ldb2look.ddl导出表的统计信息db2look–d<db_name>-z<schema>-t<tabname>-mdb2look语法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-ddepartment-uwalid-e-odb2look.sql

导出用户walid创建，schema名为ianhe的对象:db2l