1、DB2 数据移动 (第5讲),上海*技术有限责任公司Mr jim()2014-12,培训大纲,数据移动格式导出（EXPORT）工具导入（IMPORT）工具导入（LOAD）工具db2move 工具db2look 工具.,DB2数据迁移,数据库B,数据库A,数据文件,EXPORT,IMPORT,LOAD,单表操作,DB2MOVE,多表操作,DB2LOOK,数据库A,脚本文件,客户端 B,客户端 A,数据库操作,数据文件,数据库A,数据移动格式,定界ASCII文件格式 非定界ASCII文件格式 PC/IXF格式: 集成交换格式 WSF工作表文件格式: 主要用于LOTUS软件， WSF是一种专有的二进
2、制文件格式。 游标,数据移动格式,数据库表,DEL文件：可视,IXF文件：不可视,PC/IXF文件格式,PC/IXF集成交换格式，包括表定义和数据ASC,DEL,WSF在跨平台传输数据时可能会导致数据丢失.跨平台建议使用PC/IXF文件格式,游标,游标是一个SELECT语句返回的结果集。这种格式仅限于LOAD使用，可以提高LOAD的性能。,培训大纲,数据移动格式导出（EXPORT）工具导入（IMPORT）工具导入（LOAD）工具db2move 工具db2look 工具.,导出工具,EXPORT Export用于将数据从数据表中导出几种格式的文件中。其实，它执行了一个SQL 的SELECT操作。
3、 支持的格式： IXF:是一种专用的二进制格式，除数据外还包含表的结构信息 DEL:使用定界符的文本格式 WSF:lotus支持的工作表格式DB2命令行,如:$db2 connect to sample$db2 export to /home/db2inst1/test.ixf of ixf select * from test,EXPORT,EXPORT本质是把一条SQL语句的结果集导出到一个文件中。EXPORT面向的是SQL而不单纯是表。 EXPORT支持的导出格式是DEL、WSF、IXF格式。 EXPORT运行前必须连接或隐式连接到数据库。 EXPORT会运行COMMIT，因此在运行前需
4、要发出COMMIT或ROLLBACK语句来完成所有事务并释放锁定。,EXPORT的命令,EXPORT TO filename OF filetype MODIFIED BY filetype-mod filetype-mod. MESSAGES message-file select-statment,EXPORT的命令,TO filename 用来指定将导出的数据放在文件中 OF filetype 指定导出文件的类型，如IXF、DEL或WSF等。 MODIFIED BY filetype-mod指定文件修饰符参数，比如CHARDELx 用于指定字符串定界符，COLDELx 用于指定字段定界符
5、，x是定界符号。默认的字符串定界符为双引号(“”)默认的字段定界符为逗号(,) TIMESTAMPFORMAT用于指定日期字段的时间表达格式 MESSAGES message-file消息输出文件，用来保存导出过程中发生的错误或警告信息 select-statment 利用SELECT语句指定要导出的数据,EXPORT的示例,db2 export to stuff.ixf of ixf select * from staff where dept = 20以IXF输出格式将staff表中关于部门为20的职员信息导出到stuff.ixf db2 export to myfile.del of d
6、el modified by chardel coldel; decpt, select * from staff以单引号作为字符串定界符，分号作为列定界符，逗号作为小数点，将数据导出到DEL文件。同时数据导回数据库时应使用同一约束,测试数据 db2 = select * from t1 EMPNO NAME SEQNO - - -10 你好s 130 xu xin 350 bbbbbb 680 li si 84 条记录已选择。 export to t11.del of del modified by codepage=1386 chardel“ coldel, messages t1.msg
7、 select * from t1;导出的文件内容为： 10,“你好s “,1 30,“xu xin “,3 50,“bbbbbb “,6 80,“li si “,8,Export 语句案例测试(1),db2 = create table t2(name varchar(20), birthday date) db2 = insert into t2 values(Tom,1989-1-1) db2 = insert into t2 values(Jim,1983-10-1)db2 = select * from t2 NAME BIRTHDAY - - Tom 1989-01-01 Jim
8、1983-10-012 条记录已选择。export to t11.del of del messages t1.msg select * from t2;导出的文件内容为： “Tom“,19890101 “Jim“,19831001注意目前没有找到对date类型的格式化接口。默认导出格式为YYYYMMDD,Export 语句案例测试(2),db2 = create table t3(name varchar(20), ts timestamp) db2 = insert into t3 values(Tom,1989-1-1) db2 = insert into t3 values(Jack,
9、1999-1-1)db2 = select * from t3 NAME TS - - Tom 1989-01-01-00.00.00.000000 Jack 1999-01-01-00.00.00.0000002 条记录已选择。,Export 语句案例测试(6),export to t11.del of del messages t1.msg select * from t3;导出的文件内容为： “Tom“,“1989-01-01-00.00.00.000000“ “Jack“,“1999-01-01-00.00.00.000000“export to t11.del of del modi
10、fied by timestampformat=“yyyy.mm.dd hh:mm tt“ messages t1.msg select * from t3;导出的文件内容为： “Tom“,“1989.01.01 12:00 AM“ “Jack“,“1999.01.01 12:00 AM“,Export 语句案例测试(8),培训大纲,数据移动格式导出（EXPORT）工具导入（IMPORT）工具导入（LOAD）工具db2move 工具db2look 工具.,导入（IMPORT）工具,Import用于将数据文件导入到数据表中。支持ASC,DEL,WSF,IXF 主要方式:,导入（IMPORT）工具
11、,例:$db2 Import from test.ixf of ixf REPLACE_CREATE into test1 例:$db2 Create table test2 like act$db2 Import from /home/db2inst1/test.del of delINSERT into test2,导入（IMPORT）工具,IMPORT会使用SQL INSERT语句向表 IMPORT实质是执行INSERTUPDATEDELETE SQL语句，因此会触发触发器、执行日志记录、执行约束检查和索引构建。 IMPORT运行前必须连接或隐式连接到数据库。 IMPORT会运行COMM
12、IT或ROLLBACK，因此在运行前需要发出COMMIT或ROLLBACK语句来完成所有事务并释放锁定。 Commitcount(或automatic)指定每次提交的行数，以限制日志使用量和提高效率 默认向目标表加X锁，除非指定”allow write access” 支持的格式：del,asc,wsf，ixf 与ixf格式的文件结合可以创建表的结构,IMPORT的命令,IMPORT FROM filename OF filetype ALLOW NO/WRITE ACCESS MODIFIED BY filetype-mod filetype-mod. MESSAGES message-fi
13、leCOMMITCOUNT n/AUTOMATIC insert_create-statment,IMPORT的命令,FROM filename 指定数据导入文件的名称 OF filetype 指定数据导入文件的类型，支持ASC DELWSFIXF MODIFIED BY filetype-mod指定文件修饰符参数 MESSAGES message-file消息输出文件 ALLOW NO ACCESS/ALLOW WRITE ACCESS脱机导入/联机导入 COMMITCOUNT n指每n条记录提交一次，也可设成AUTOMATIC，由DB自动设置COMMITCOUNT。,IMPORT的命令,i
14、nsert_create-statment 导入方式：1）、已经存在目标表方式：INSERT INSERT_UPDATE REPLACE 2）、不存在目标表方式：REPLACE_CREATECREATE只能匹配IXF格式。,IMPORT的命令,Import示例import from filename.ixf of ixf commitcount 50000 insert into tabnameimport from filename.ixf of ixf create into tabnameimport from artexprt.ixf of ixf messages art.msg c
15、reate into artists (column_list)in index in long in ,2018/12/31,25,IMPORT的命令,培训大纲,数据移动格式导出（EXPORT）工具导入（IMPORT）工具导入（LOAD）工具db2move 工具db2look 工具.,导入（LOAD）工具,Load不能创建表及索引,故不能使用Create方式。 主要方式:,Load可以装入的数据格式包括: IXF也就是DB2 export程序推荐的那种数据格式，是二进制的。 DEL就是分界符的ASCIl文件，DB2默认的是用逗号(,)作为字段列的分隔符,用双引号(“”)作为字符串的分隔符；
16、ASC非定界符的ASCII文件，load时需要指定列的起止位置； CURSOR游标。,导入（LOAD）工具,导入（LOAD）工具,不使用日志 数据加载效率高 使用异常表 可使用db2 list utilities show detail查看进度 支持的数据源：ixf,del,asc,cursor cursor方式提高一个数据库内数据移动的效率 空文件可以用于快速清空一个表,2018/12/31,29,Load 的过程分为四个阶段：装入,构建,删除,索引复制。load阶段是将源文件parser成物理数据存储的格式,直接装入到页中,而不通过db2引擎。load阶段会检查表定义,违背定义的数据不会装
17、入到表中. build阶段建立索引（如果装入表有索引的话）,会检查唯一性约束,违背了唯一性的数据会在delete阶段删除.index copy阶段将index数据从指定的临时表空间拷贝到初始的表空间里.index copy只适应于allow read access场景.load的4个阶段日志会记录在messages文件里.,导入（LOAD）工具,参数解释 messages 是追加写文件dumpfile 是覆盖写文件exception 是追加写表 可以用modified by 修饰符来指定：coldelx用来指定字段间分隔符chardelx用来指定字符串分隔符codepage用来指定编码。 db
18、2 = load from t1.del of del modified by coldel, chardel codepage=1386 dumpfile=t1.dmp messages t1.msg insert into t1 for exception t1_exp,导入（LOAD）工具,导入（LOAD）工具,设置为归档日志模式的数据库的注意事项 Copy no 默认方式 执行load后，目标表所在的表空间将被置为backup pending状态，只有对其执行备份后，表空间才能被访问。 目标表可以读，但不能进行增/删/改。而且load命令一旦发起，表空间立即处于backup pendi
19、ng状态，即使终止load操作，也不能脱离此状态。 Copy yes 选项会在load结束时，自动对表所属的表空间做一次备份，load结束后，表空间状态为正常。但由于要备份，所需时间要长些Nonrecoverable 目标表空间不会被置为backup pending。 影响使用数据库的备份执行恢复和前滚。 速度快 Nonrecoverable 将表标记为不可恢复，如果以后需要恢复表空间并且回滚到nonrecoverable load选项之后的某个时间点，这个表是不可恢复的，所有与该表相关的日志会被忽略，你只能删除并重新创建表。因此，这个方法一般用在表可以被重建的场景中。,2018/12/31,
20、32,导入（LOAD）工具,查看表的状态：load query table schema.tabNormal Check Pending set integrityLoad in ProgressLoad Pending Read Access Only Unavailable Not Load RestartableUnknown,2018/12/31,33,导入（LOAD）工具,Check pending Load工具在执行过程中关闭约束检查 如果父表处于check pending，那么依赖的表也会处于check pending SET INTEGRITY 用于将处于“check pend
21、ing”状态的表恢复至正常状态 示例：SET INTEGRITY FOR SALES ALLOW READ ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN SALES USE SALES_EXCEPTIONS,2018/12/31,34,db2 = create table t2(col1 char(10), col2 char(10) db2 = alter table t2 add constraint check1 CHECK (col2 in (A,B,C) db2 = load from f:/t2.del of del insert into t
22、2 db2 = select * from t2 COL1 COL2 - - SQL0668N 不允许对表 “HANDSOME.T2“ 执行操作，原因码为 “1“。 SQLSTATE=57016db2 = set integrity for handsome.t2 immediate checked DB21034E 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在SQL 处理期间，它返回：SQL3603N 通过 SET INTEGRITY语句来执行完整性处理时发现了完整性违例，此违例涉及到约束、唯一索引、已生成的列或者基于 XML 列的索引。由 “HANDSOME.T
23、2.CHECK1“ 标识了相关联的对象。SQLSTATE=23514,导入（LOAD）工具,db2 = create table t2_exp like t2 DB20000I SQL 命令成功完成。 db2 = set integrity for handsome.t2 immediate checked for exception in t2 use t2_exp SQL3602W 检查数据处理找到约束违例，并将它们移至异常表。 SQLSTATE=01603 db2 = select * from t2_exp COL1 COL2 - - CCC CCC DDD BBB2 条记录已选择。,
24、导入（LOAD）工具,以下语句可用来检查表的状态、是否允许访问及表的主外键约束、检查约束等。 db2 = select tabname,status,access_mode, substr(const_checked,1,1) as fk_checked , substr(const_checked,2,1) as cc_checked from syscat.tables where status = C,导入（LOAD）工具,导入（LOAD）工具,DEL文件分隔符的指定 Load工具可以使用modified by coldelx指定特定分隔符。 1386编码下，”|”作为分隔符会发生处理故
25、障 DEL文件格式 注意DEL文件的编码格式是否与目标操作系统环境相吻合。 可以借助一些工具进行转换 代码页转换问题 使用load工具可以使用modified by codepage=1386来指定代码页。 Ixf格式的文件一般会自动转换代码页,2018/12/31,38,导入（LOAD）工具,编码问题的典型案例 需要将编码为1386(GBK)的以”|”作为分隔符的del格式文件内容，导入到1208(UTF-8)编码的数据库中。 处理办法-任选一个 将分隔符替换为逗号分割的文件 使用load的modified by codepage=1386 或设置db2set CODEPAGE=1386，然
26、后使用import导入 或将原始文件的编码转换为UTF-8，设置db2set CODEPAGE=1208,使用load的modified by coldel|导入,2018/12/31,39,发生截断时，抛出“SQL3125W 因为数据比目标数据库列长，截断后的数据还是会写入目标表。 类型不匹配的，抛出类似如下的异常：如果目标字段没有NOT NULL约束，目标字段的值为nullSQL3119W 不能将字段值转换成 INTEGER 值。装入了空值。否则：SQL3120W 不能将字段值转换成 INTEGER值，但目标列不可为空。未装入该行。并将该记录写入dumpfile 对应的输出文件中。,导入（
27、LOAD）工具,导入（LOAD）工具,示例db2 load from stafftab.ixf of ixf modified by anyorder messages staff.msgs tempfiles path /u/myuser replace into staff copy yes to /dbbak/tmp/ DATA BUFFER 10240 CPU_PARALLELISM 8DECLARE mycurs CURSOR DATABASE dbsource FOR SELECT TWO,ONE,THREE FROM abc.table1 LOAD FROM mycurs OF
28、cursor INSERT INTO abc.table2create table t1exc as(SELECT T1.*,CURRENT TIMESTAMP AS TS,CLOB(, 32767) AS MSGFROM T1)definition onlydb2 load from filename.del of del insert into tabname messages msg.1 for exception t1exc,2018/12/31,41,培训大纲,数据移动格式导出（EXPORT）工具导入（IMPORT）工具导入（LOAD）工具db2move 工具db2look 工具,d
29、b2move,用于将数据库所有的表导出、导入（迁移库,尤其是不同操作系统间，不同版本数据库间）。 支持的action：export,import,load,copy。 自动的导出和导入一批表。 导入(import,load)自动读取db2move.lst文件。 Copy模式下可以用于将源数据库指定模式下的所有对象复制到目标数据库（ADMIN_COPY_SCHEMA 可以用于在数据库内部复制不同模式的对象）,2018/12/31,43,db2move,语法： db2move dbname action -tc table-definers -tn table-names -sn schema-n
30、ames -ts tablespace-names -tf filename -io import-option -lo load-option -co copy-option -u userid -p password -aw,2018/12/31,44,db2move,-io import-optioncreate,insert,insert_update,replace,replace_create(def) -lo load-optioninsert(def),replace -tf export-option-filenamelist exported table name,db2m
31、ove,Export，Import和 Load每次都只对一个表进行操作。但是您若使用它们来写一段脚本，就可对一个数据库中的所有的表进行操作。 db2move可以更方便地完成同样的工作。 db2move工具只兼容IXF格式的文件。,db2move 工具,语法格式:$db2move Action: Exoprt,Import,Load,Copy $db2move sample export 1,db2move会自动连接至目标数据库实例。2,自动生成的IXF文件会保存在当前命令行目录。,db2move,示例：db2move sample export db2move sample export -t
32、c userid1,us*rid2 -tn tbname1,*tbname2db2move sample COPY -sn BAR -co target_db target schema_map “(BAR,FOO)“ -u userid -p password,2018/12/31,48,培训大纲,数据移动格式导出（EXPORT）工具导入（IMPORT）工具导入（LOAD）工具db2move 工具db2look 工具,db2look 工具,db2look工具则可以将DDL语句、数据库统计状态、表空间参数导出到一个脚本文件中，这个文件可以用于不同系统的数据库。命令为:db2look -d 参数
33、可以输入db2look h查看。 通过db2 -tvf 来导入数据库。,db2look 参数说明,参数说明: -d DBname :数据库名称,这是必须的. -e :抽取数据库对象的DDL, -z schema :指定模式.如果和-a同时使用,-z被忽略. -t Tname1 Tname2 . TnameN -tw Tname :模糊查询表的名称.通配符-代表任意字符,%代表任意好多个字符及字符串.使用-tw参数时,-t参数会被忽略.,db2look 工具,参数说明: -o :输出到文件的名称. -a :所有的用户和模式-m :生成对表的列表和索引统计的update语句-l :定义表空间的，缓冲池，数据库分区脚本-x :生成授权的DDL. -xd:表视图上的权限语句 -f :数据库和“数据库管理器”配置参数 -td :指定定界符,默认的为分号;.使用时-td后加空格 如:-td . -i :userid.当远程登陆时使用. -w: password 远程登陆的用户密码.,例子: (1) 执行$db2look -d T0 -a -e -x -o t1.ddl 不需要数据库连接,输出文件在/test盘符下. (2) 建立一个新的数据库T2。 执行 $db2 connect to T1$db2 -tvf t1.ddl,db2look 工具,
