DB2数据移动(第5讲)

DB2数据移动

(第5讲)

上海****技术有限责任公司Mrjim()2014-12培训大纲数据移动格式导出〔EXPORT〕工具导入〔IMPORT〕工具导入〔LOAD〕工具db2move工具db2look工具.DB2数据迁移数据库B数据库A数据文件EXPORTIMPORTLOAD单表操作DB2MOVE多表操作DB2LOOK数据库A脚本文件客户端B客户端A数据库操作数据文件数据库A数据移动格式定界ASCII文件格式非定界ASCII文件格式PC/IXF格式:

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

主要用于LOTUS软件，WSF是一种专有的二进制文件格式。游标数据移动格式数据库表DEL文件：可视IXF文件：不可视PC/IXF文件格式PC/IXF集成交换格式，包括表定义和数据ASC,DEL,WSF在跨平台传输数据时可能会导致数据丧失.跨平台建议使用PC/IXF文件格式游标游标是一个SELECT语句返回的结果集。这种格式仅限于LOAD使用，可以提高LOAD的性能。培训大纲数据移动格式导出〔EXPORT〕工具导入〔IMPORT〕工具导入〔LOAD〕工具db2move工具db2look工具.导出工具

EXPORTExport用于将数据从数据表中导出几种格式的文件中。其实，它执行了一个SQL的SELECT操作。支持的格式：IXF:是一种专用的二进制格式，除数据外还包含表的结构信息DEL:使用定界符的文本格式WSF:lotus支持的工作表格式DB2命令行,如:

$db2connecttosample

$db2exportto/home/db2inst1/test.ixfofixfselect*fromtestEXPORTEXPORT本质是把一条SQL语句的结果集导出到一个文件中。EXPORT面向的是SQL而不单纯是表。EXPORT支持的导出格式是DEL、WSF、IXF格式。EXPORT运行前必须连接或隐式连接到数据库。EXPORT会运行COMMIT，因此在运行前需要发出COMMIT或ROLLBACK语句来完成所有事务并释放锁定。EXPORT的命令EXPORTTOfilenameOFfiletype[MODIFIEDBYfiletype-mod[{filetype-mod}...]][MESSAGESmessage-file]select-statmentEXPORT的命令TOfilename用来指定将导出的数据放在<filename>文件中OFfiletype指定导出文件的类型，如IXF、DEL或WSF等。MODIFIEDBYfiletype-mod指定文件修饰符参数，比方CHARDELx用于指定字符串定界符，COLDELx用于指定字段定界符，x是定界符号。默认的字符串定界符为双引号(“”)默认的字段定界符为逗号(,)TIMESTAMPFORMAT用于指定日期字段的时间表达格式MESSAGESmessage-file消息输出文件，用来保存导出过程中发生的错误或警告信息select-statment利用SELECT语句指定要导出的数据EXPORT的例如db2exporttostuff.ixfofixfselect*fromstaffwheredept=20以IXF输出格式将staff表中关于部门为20的职员信息导出到stuff.ixfdb2exporttomyfile.delofdelmodifiedbychardel''coldel;decpt,select*fromstaff以单引号作为字符串定界符，分号作为列定界符，逗号作为小数点，将数据导出到DEL文件。同时数据导回数据库时应使用同一约束测试数据db2=>select*fromt1EMPNONAMESEQNO

10你好's130xuxin350bbbbbb680lisi84条记录已选择。exporttot11.delofdelmodifiedbycodepage=1386chardel""coldel,messagest1.msgselect*fromt1;导出的文件内容为：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;导出的文件内容为："Tom",19890101"Jim",19831001注意目前没有找到对date类型的格式化接口。默认导出格式为’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;导出的文件内容为："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;导出的文件内容为："Tom","1989.01.0112:00AM""Jack","1999.01.0112:00AM"Export语句案例测试(8)培训大纲数据移动格式导出〔EXPORT〕工具导入〔IMPORT〕工具导入〔LOAD〕工具db2move工具db2look工具.导入〔IMPORT〕工具Import用于将数据文件导入到数据表中。支持ASC,DEL,WSF,IXF主要方式:方式描述目标表原有数据支持格式Insert插入操作需要存在保留所有格式insert_update更新键值操作需要存在保留或更新所有格式Repalce删除原有数据,插入新数据需要存在删除所有格式Replace_Create删除原有数据,插入新数据不存在则创建删除IXFCreate导入数据至新表创建新表无IXF导入〔IMPORT〕工具例:

$db2Importfromtest.ixfofixf

REPLACE_CREATEintotest1例:

$db2Createtabletest2likeact

$db2Importfrom/home/db2inst1/test.delofdel

INSERTintotest2导入〔IMPORT〕工具IMPORT会使用SQLINSERT语句向表IMPORT实质是执行INSERT\UPDATE\DELETESQL语句，因此会触发触发器、执行日志记录、执行约束检查和索引构建。IMPORT运行前必须连接或隐式连接到数据库。IMPORT会运行COMMIT或ROLLBACK，因此在运行前需要发出COMMIT或ROLLBACK语句来完成所有事务并释放锁定。Commitcount(或automatic)指定每次提交的行数，以限制日志使用量和提高效率默认向目标表加X锁，除非指定”allowwriteaccess”支持的格式：del,asc,wsf，ixf与ixf格式的文件结合可以创立表的结构IMPORT的命令IMPORTFROMfilenameOFfiletype[ALLOWNO/WRITEACCESS][MODIFIEDBYfiletype-mod[{filetype-mod}...]][MESSAGESmessage-file][COMMITCOUNTn/AUTOMATIC]insert_create-statmentIMPORT的命令FROMfilename指定数据导入文件的名称OFfiletype指定数据导入文件的类型，支持ASC\DEL\WSF\IXFMODIFIEDBYfiletype-mod指定文件修饰符参数MESSAGESmessage-file消息输出文件ALLOWNOACCESS/ALLOWWRITEACCESS脱机导入/联机导入COMMITCOUNTn指每n条记录提交一次，也可设成AUTOMATIC，由DB自动设置COMMITCOUNT。IMPORT的命令insert_create-statment导入方式：1〕、已经存在目标表方式：INSERT\INSERT_UPDATE\REPLACE2〕、不存在目标表方式：REPLACE_CREATE\CREATE只能匹配IXF格式。IMPORT的命令Import例如 importfromfilename.ixfofixfcommitcount50000insertintotabnameimportfromfilename.ixfofixfcreateintotabname importfromartexprt.ixfofixfmessagesart.msgcreateintoartists[(column_list)] in<tablespace> indexin<indextablespace> longin<largetablespace>2024/5/2325IMPORT的命令培训大纲数据移动格式导出〔EXPORT〕工具导入〔IMPORT〕工具导入〔LOAD〕工具db2move工具db2look工具.导入〔LOAD〕工具方式描述目标表原有数据支持格式Insert插入操作需要存在保留Load支持格式Repalce删除原有数据,插入新数据需要存在删除Load支持格式TERMINATE模式终止装载操作，并回滚到它开始时所在的时间点。一个例外是，如果指定了REPLACE模式，那么表将被清空RESTART用于重新开始之前被中断的装载命令，它将自动从最近的一致点继续。要使用这个模式，可以指定与前一个LOAD命令相同的选项，但是这一次加上RESTARTLoad不能创立表及索引,故不能使用Create方式。主要方式:Load可以装入的数据格式包括:IXF——也就是DB2export程序推荐的那种数据格式，是二进制的。DEL——就是分界符的ASCIl文件，DB2默认的是用逗号(,)作为字段列的分隔符,用双引号(“”)作为字符串的分隔符；ASC——非定界符的ASCII文件，load时需要指定列的起止位置；CURSOR——游标。导入〔LOAD〕工具导入〔LOAD〕工具不使用日志数据加载效率高使用异常表可使用db2listutilitiesshowdetail查看进度支持的数据源：ixf,del,asc,cursorcursor方式提高一个数据库内数据移动的效率空文件可以用于快速清空一个表2024/5/2329Load的过程分为四个阶段：装入,构建,删除,索引复制。load阶段是将源文件parser成物理数据存储的格式,直接装入到页中,而不通过db2引擎。load阶段会检查表定义,违背定义的数据不会装入到表中.

build阶段建立索引〔如果装入表有索引的话〕,会检查唯一性约束,违背了唯一性的数据会在delete阶段删除.indexcopy阶段将index数据从指定的临时表空间拷贝到初始的表空间里.indexcopy只适应于allowreadaccess场景.load的4个阶段日志会记录在messages文件里.导入〔LOAD〕工具参数解释messages是追加写文件dumpfile是覆盖写文件exception是追加写表可以用modifiedby修饰符来指定：coldelx用来指定字段间分隔符chardelx用来指定字符串分隔符codepage用来指定编码。db2=>loadfromt1.delofdelmodifiedbycoldel,chardel’’codepage=1386dumpfile=t1.dmpmessagest1.msginsertintot1forexceptiont1_exp导入〔LOAD〕工具导入〔LOAD〕工具设置为归档日志模式的数据库的本卷须知Copyno默认方式执行load后，目标表所在的表空间将被置为backuppending状态，只有对其执行备份后，表空间才能被访问。目标表可以读，但不能进行增/删/改。而且load命令一旦发起，表空间立即处于backuppending状态，即使终止load操作，也不能脱离此状态。Copyyes选项会在load结束时，自动对表所属的表空间做一次备份，load结束后，表空间状态为正常。但由于要备份，所需时间要长些Nonrecoverable目标表空间不会被置为backuppending。影响使用数据库的备份执行恢复和前滚。速度快Nonrecoverable将表标记为不可恢复，如果以后需要恢复表空间并且回滚到nonrecoverableload选项之后的某个时间点，这个表是不可恢复的，所有与该表相关的日志会被忽略，你只能删除并重新创立表。因此，这个方法一般用在表可以被重建的场景中。2024/5/2332导入〔LOAD〕工具查看表的状态：loadquerytableschema.tab Normal CheckPending–setintegrity LoadinProgress LoadPending ReadAccessOnly Unavailable NotLoadRestartable Unknown2024/5/2333导入〔LOAD〕工具CheckpendingLoad工具在执行过程中关闭约束检查如果父表处于checkpending，那么依赖的表也会处于checkpendingSETINTEGRITY用于将处于“checkpending”状态的表恢复至正常状态例如： SETINTEGRITYFORSALESALLOWREADACCESSIMMEDIATECHECKEDFOREXCEPTIONINSALESUSESALES_EXCEPTIONS2024/5/2334db2=>createtablet2(col1char(10),col2char(10))db2=>altertablet2addconstraintcheck1CHECK(col2in('A','B','C'))db2=>loadfromf:/t2.delofdelinsertintot2db2=>select*fromt2COL1COL2

SQL0668N不允许对表"HANDSOME.T2"执行操作，原因码为"1"。SQLSTATE=57016db2=>setintegrityforhandsome.t2immediatecheckedDB21034E该命令被当作SQL语句来处理，因为它是无效的“命令行处理器”命令。在SQL处理期间，它返回：SQL3603N通过SETINTEGRITY语句来执行完整性处理时发现了完整性违例，此违例涉及到约束、唯一索引、已生成的列或者基于XML列的索引。由"HANDSOME.T2.CHECK1"标识了相关联的对象。SQLSTATE=23514导入〔LOAD〕工具db2=>createtablet2_expliket2DB20000ISQL命令成功完成。db2=>setintegrityforhandsome.t2immediatecheckedforexceptionint2uset2_expSQL3602W检查数据处理找到约束违例，并将它们移至异常表。SQLSTATE=01603db2=>select*fromt2_expCOL1COL2

CCCCCCDDDBBB2条记录已选择。导入〔LOAD〕工具以下语句可用来检查表的状态、是否允许访问及表的主外键约束、检查约束等。db2=>selecttabname,status,access_mode,substr(const_checked,1,1)asfk_checked,substr(const_checked,2,1)ascc_checkedfromsyscat.tableswherestatus='C'导入〔LOAD〕工具导入〔LOAD〕工具

DEL文件分隔符的指定Load工具可以使用modifiedbycoldelx指定特定分隔符。1386编码下，”|”作为分隔符会发生处理故障DEL文件格式注意DEL文件的编码格式是否与目标操作系统环境相吻合。可以借助一些工具进行转换代码页转换问题使用load工具可以使用modifiedbycodepage=1386来指定代码页。Ixf格式的文件一般会自动转换代码页2024/5/2338导入〔LOAD〕工具编码问题的典型案例需要将编码为1386(GBK)的以”|”作为分隔符的del格式文件内容，导入到1208(UTF-8)编码的数据库中。处理方法-任选一个将分隔符替换为逗号分割的文件使用load的modifiedbycodepage=1386或设置db2setCODEPAGE=1386，然后使用import导入或将原始文件的编码转换为UTF-8，设置db2setCODEPAGE=1208,使用load的modifiedbycoldel|导入2024/5/2339发生截断时，抛出“SQL3125W因为数据比目标数据库列长，截断后的数据还是会写入目标表。类型不匹配的，抛出类似如下的异常：如果目标字段没有NOTNULL约束，目标字段的值为nullSQL3119W不能将字段值转换成INTEGER值。装入了空值。否那么：SQL3120W不能将字段值转换成INTEGER值，但目标列不可为空。未装入该行。并将该记录写入dumpfile对应的输出文件中。导入〔LOAD〕工具导入〔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培训大纲数据移动格式导出〔EXPORT〕工具导入〔IMPORT〕工具导入〔LOAD〕工具db2move工具db2look工具db2move用于将数据库所有的表导出、导入〔迁移库,尤其是不同操作系统间，不同版本数据库间〕。支持的action：export,import,load,copy。自动的导出和导入一批表。导入(import,load)自动读取db2move.lst文件。Copy模式下可以用于将源数据库指定模式下的所有对象复制到目标数据库〔ADMIN_COPY_SCHEMA可以用于在数据库内部复制不同模式的对象〕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每次都只对一个表进行操作。但是您假设使用它们来写一段脚本，就可对一个数据库中的所有的表进行操作。db2move可以更方便地完成同样的工作。db2move工具只兼容IXF格式的文件。db2move工具语法格式:

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