db2 导入导出工具

一、概述

DB2提供了三个用于数据移动的程序：
export：使用sql select语句将数据从数据库表中提取到一个文件中
import：将输入文件中的数据导入到表中
load：将输入文件中的数据导入到表中的另外一种高效方法

二、说明

1、文件格式

1、文件格式ASC
非定界的或固定长度的ASCII(ASC)
--这种文件类型包含固定长度的ASCII数据，这些数据与列数据相对应
--每个ASC文件都是一个ASCII字符流，其中包含按行和列排列的数据值
--数据库中的行以行分界符分隔开来，这里假定行分界符就是换行符
--适用于数据的导入操作，不能用做导出操作
"
1    ab  11
2  abcd  22
3     c  33"

2、文件格式DEL
定界的ASCII(DEL)
--最为常用的一种文件格式
--可用于数据的导入和导出操作
--包含ASCII数据。并使用特殊的字符分界符来分隔列值
--数据流中的行则以作为行分界符的换行字符分隔开来，默认为换行

"0000","tom","1111","banana",19950101

3、文件格式PC/IXF
PC版本的集成交换格式Integrated Exchange Format(PC/IXF):
--导出的文件中包含数据库表的结构化描述
--可用于导入数据，还可以用于创建目标数据库中之前不存在的表
--可用于数据的导入和导出
--导出文件是二进制的

4、文件格式WSF
工作表格式(WSF):
--存储在工作表（一般用于Lotus中）中解释
--可用于导入和导出

2、export

1、export可以使用sql select语句将数据从数据库表提取到文件中
数据可以被导出到DEL、IXF或WSF文件中。不能导出到ASC中
在export中需要包括messages子句，以捕获导出过程中遇到的错误、警告等有用的消息
调用export实用程序，用户需要拥有sysadm或dbadm权限，或者拥有export命令中所访问的表或视图上的control或select特权
connect to sample
export to "C:\export.txt" of DEL messages "C:\info.txt" select * from employee;

2、文件类型修饰符：
一般情况下，数据文件存储的格式是DEL格式的文件
默认情况下，列值之间以逗号（,）隔开，字符串以双引号（""）括起来
问题：如果数据中已经包含逗号和双引号，怎嘛办
import或load不可能确定那些符号是实际的数据，那些符号是分界符。为了定制export如何操作，可以使用modified by子句，并指定对于特殊字符
如何使用文件类型修饰符进行修改
"0000","tom","1111","banana",19950101
export to file_name of file_type modified by file_type_modifiers messages message_file select_statement

3、常用的文件类型修饰符
chardelx
--指定x作为新的单字符字符串分界符。默认值是一个双引号（""）
coldelx
--指定x作为新的单字符列分界符，默认值是一个逗号（,）
codepage=x
--指定x（一个ASCII字符串）作为输出数据的新的码页（code page）。在导出操作期间，字符数据被从应用程序码页转换成这种码页
timestampformat="x"
--x是源表中时间戳的格式

字符串以感叹号（!）括起来
列之间以@符号分隔开
将字符串转换成码页1208
export to myfile.del of DEL modified by chardel! coldel@ codepage=1028
timestampformat="yyyy.mm.dd hh:mm tt"
messages msg.out select * from employee

4、导出大型对象
当导出包含有大对象列的表时，默认情况下只能导出LOB数据的前32KB
大对象导出的这一部分与其他列数据存储放在同一个文件中
解决办法：为了完整的导出LOB数据，并将他们与其他列数据分别存储在不同的文件中，必须使用LOB选项
在DB2中，可以选择将多个LOB值连接起来并导出到同一个输出文件中，或者将每个LOB值导出到一个单独的文件中

3、import

1、import可以将输入文件中的数据导入到表中
输入文件的文件类型可以是ASC、DEL、IXF或WSF
导入目标可以是一个表或者一个视图，但是，不能导入到系统表、临时表和物化查询表
需要使用messages子句，用于指定记录错误、警告和信息的消息文件
导入数据，用户必须拥有sysadm或dbadm权限，或者目标表或数据库上的底层特权（select、insert、control或createtab），这取决于使用什么
  导入选项
  
2、import选项
在导入数据时，可以指定以什么样的方式导入到目标表中：
--insert
将导入的数据插入到表中。
目标表必须已经存在
--insert_update
将数据插入表中，或者更新表中具有匹配主键的行
目标表必须已经存在，并且定义了一个主键
--replace
删除所有已有的数据，并将导入的数据插入到一个已有的目标表中
--replace_create
如果目标表已经存在，则删除已有的数据，并插入新的数据，就像replace选项那样
如果目标表还没有定义，那么首先创建这个表以及它的相关索引，然后再导入数据
输入文件必须是PC/IXF格式的文件
如果目标表是被一个外键引用的一个父表，那么就不能使用replace_create
--create
首先创建目标表和相关的索引，然后将数据导入到新表中
该选项惟一支持的文件格式是PC/IXF
还可以指定新表所在的表空间

3、import的原理与思考
import虽然看起来是批量导入数据，但是实际上底层是采用一般的insert语句来插入数据，所以会涉及到活动的日志记录、索引的更新、参照完整性
  检查和表约束检查
默认情况下，import只在操作结束时提交一次，如果将大量的行一次性导入或插入到表中，那么需要有足够的事务记录用于回滚和恢复，所以需要定时的
  周期性的进行commit，以防日志写满
同时，通过定期的提交插入，还可以减少导入操作期间出现失败时丢失的行数。
commitcount选项规定在导入多少行数据后强制执行commit
automatic选项允许import自己决定何时需要执行提交，以避免日志写满或者避免锁升级
最有效的导入数据的方式：load

4、load

1、load可以实现批量的数据导入
本质是将格式化的数据页直接写入到数据库中，所以load比import在导入数据时更为有效
但是有些操作（例如参照约束或表约束检查和触发器的调用），load操作是不能遵守或者触发的
用户必须拥有sysadm/dbadm或load权限，并且在表上具有insert和/或delete特权
在使用load时，载入目标必须已经存在，装载目标可以是一个表、一个物化查询表或一个表的别名
使用messages选项可以捕获装载期间遇到的错误、警告和包含有用信息的消息

2、load支持的输入格式
load支持的源输入的格式可以是DEL、ASC、PC/IXF或CURSOR
游标是一个select语句返回的结果集
使用CURSOR导入的示例：
declare mycursor CURSOR for select col1,col2,col3 from tab1;
load from mycursor of CURSOR insert into newtable;

3、import与load的对比

4、load的模式
--insert：将输入数据添加到一个表中，而不更改已有的表数据
--replace：删除表中所有已有的数据，然后用输入数据填充这个表
--terminate：终止装载操作，并回滚到它开始时所在的时间点
--restart：重新开始之前被中断的装载命令，它将自动从最近的一致点继续

5、load的阶段
一个完整的装载过程包括四个不同的阶段
--装载阶段
  将数据装载到表中
  收集索引键和表统计信息
  记录一致点
  将无效的数据放在转储（dump）文件中，并在消息文件中记录消息，当数据行不符合表定义时，这些数据就被当做无效数据，并且被拒绝（不装载到表中）
  可以使用dumpfile修饰符来指定用于记录被拒绝行的文件的文件名和位置
--构建阶段
  利用装载阶段收集到的键创建索引
--删除节点
  删除违反惟一键的行，并将他们放在异常表中
  注意，这里只有违反惟一键的行被当作无效数据，但是不会检查其他约束
  load将在这个阶段删除不符合要求的行，使用一个异常表来存储被删除的行，以便装载操作完成后决定如何处理他们
  如果没有指定异常表，则不符合要求的行就完全被删除，而没有被跟踪
--索引复制阶段
  如果指定了allow read access和use tablespace选项，那么这个阶段会将索引数据从系统临时表空间中复制到这些索引应该处在的表空间中
  
6、异常表
一种需要用户定义的表，用于存储在删除阶段违反惟一性约束而被删除的行
异常表的创建原则：
--1、前N列与原表定义一样
--2、不能在异常表上加任何的约束条件
--3、N+1列为timestamp
--4、N+2列为CLOB（32KB）
--5、用户必须有异常表上插入权限

7、copy选项
在load数据时，可以通过copy指定恢复选项
--copy no (default)
--copy yes
--nonrecoverable
执行可恢复的装入（M）
  不生成输入数据的副本，这将使表空间处于备份暂挂状态(D)
  保存输入数据的副本，在完成装入之后，该表将是可用的(S)
     复制目标 目录
     目录名
执行不可恢复的装入(U)

8、装载示例
输入文件      输出文件
//数据        //table   //exception table  //dump文件
1,"A",1       1,"A",1   4,"D1",41          7,"G",x
2,"B",2       2,"B",2   4,"D2",42
3,"C",3       3,"C",3
4,"D",4       4,"D",4
4,"D1",41     5,"E",5
4,"D2",42     6,"F",6
5,"E",5
6,"F",6
7,"G",x
步骤一：测试表
create table administrator.testload (id bigint not null,name varchar(10) not null,age bigint not null,
constraint cc1456625294737 unique (ID))
步骤二：创建异常表：
create table testload_exp like testload
alter table testload_exp add column ts timestamp add column msg clob(32K)
步骤三：产生测试数据：输入文件
步骤四：执行导入
connect to sample
load from "C:\testload.txt" of DEL modified by dumpfile="C:\dump.txt" method p(1,2,3) messages "C:\message.txt" 
insert into administrator.testload1(id,name,age) for exception testload_exp copy yes to "C:\load" indexing mode
autoselect
步骤五：查询异常表及错误数据
testload_exp
4,"D1",41
4,"D2",42
dump.txt.load.000
7,"G",x
查询message文件，查看装入过程的信息，以及是否有报错
message.txt
读取行数 =9
跳过行数 =0
装入行数 =8
拒绝行数 =1
删除行数 =2
落实行数 =9

9、与load相关的命令-load query
db2 load query table testload