DB2移动数据实用工具的使用

本篇文章介绍DB2数据库数据移动的三个实用工具：export、import、load，这是由DB2本身自带的数据导入导出工具。请注意，这三个实用工具大部分情况下类似于SQL应用，对于执行命令所需的权限和对数据库锁的情况可以认为由命令中的SQL语句决定。

首先介绍DB2数据库数据移动中支持的文件格式：

1. DEL：定界的ASCII码文件

所谓定界，即字符、列、行通过固定的字符来指定边界。默认情况下，字符的定界符为双引号，列的定界符为逗号，行定界符在Unix系统下为0X0A，Windows系统下为0X0A0D。DEL文件中行的长度并没有限制，但是单个字符的长度限制为32KB。当字段中的值包含和行定界符一致的字符时就会有冲突，这时可以使用后文介绍的修改定界符优先级选项来解决。当字段中值包含列定界符时可以通过重复该字符来表示。另外，不建议使用DEL文件来导出二进制数据。

2.ASC：定长的ASCII码文件

即每个字段长度固定的文件。DB2支持两种ASC文件的变种：①固定记录长度；②可变记录长度。在固定记录长度文件中，每个字段的长度是固定的，在可变记录长度文件中，可以指定每个字段的长度。ASC文件行的长度限制为32KB。

3.PC/IXF：PC集成交换格式

二进制文件，主要用于Unix系统和Windows、OS/2系统之间传递数据。文件中可以包含表的结构，可以通过import的create选项来创建新表。

4.WSF：工作表单格式

是一种专有的二进制文件格式，用来在DB2、Lotus 1-2-3和Symphony产品之间交换数据

以下是DB2实用工具和文件格式的支持关系。可以看到，导入工具（Import）支持全部四种文件格式，导出工具（Export）不支持ASC格式，装入工具（Load）不支持WSF文件格式。

注，图片摘自《DB2基础教程》，在Import前的“导出”有误，应为“导入”。

接下来介绍实用工具：

一、EXPORT

导出工具的数据来源是select语句，通过用户熟悉的select语句，用户可以导出任何需要的数据。导出工具需要select权限，并且使用可重复读隔离级别来和数据库绑定。

export的基本语法如下：

EXPORT TO <output file> OF <file type> <select statement>

output file：数据将要导出的文件，当文件已存在时，会直接覆盖文件。

file type：可以选择上文介绍过的三种文件格式DEL、IXF、WSF。当选择IXF格式时，会将表的结构一起导出。

select statement：sql查询语句。

以下演示导出步骤：

首先使用一下语句建表export_test1 ：

create table export_test1 (
    id int not null primary key,
    c1 varchar(50)
)

① 连接数据库

② 查询数据

在第二条记录中，我们故意在字符串中插入一个双引号

③ 导出命令

④ 查看导出文件

可以看到，第二天记录中的双引号变成了两个。也就是说如果字符和分隔符冲突，则输出两个相同的字符来表示。

接下来介绍export命令的高级选项：

1. 指定分隔符

COLDELx：列分隔符

CHARDELx：字符分隔符

Decpt x：十进制数的小数点

其中x代表将要使用的分隔符。分隔符必须彼此不一样，并且回车、换行、空格、二进制0是不可以当做分隔符的。该参数跟在MODIFIED BY后，而MODIFIED BY在<file type> 和<select statement>之间，并且可以跟许多参数，多个参数间使用逗号分隔。以下是使用示例：

①执行命令

在该命令中我们指定分号为列分隔符，竖线为字符分隔符。

②查看文件

2. 大字段

导出大字段类型使用lobsinfile选项，该选项跟在modified by后边。通过该选项，可以将大字段导出到指定的文件中，而在原本的导出文件中大字段的位置由文件名称和数据在大字段文件中的索引组成。

该选项有两个可选选项：

lobs to<diretory list> 

lobfile<list of base name>

这两个选项分别指定大字段文件的目录和基础文件名称。列表中多个数据使用逗号分隔，目录名必须以路径分隔符结尾。如果没有指定文件名称，则以db2exp为基础名称。以下演示大数据字段的导出：

使用以下语句为表export_test1 添加大字段的列，并且为其赋值，这里再添加三条数据。

alter table export_test1 add column c2 clob;

①执行导出命令：

注意如果指定了目录，则要提前建好目录，否则导出失败。

②查看文件：

可以看到lobdir1和lobdir2目录下分别有一个文件，文件名称由导出命令指定的基础文件名加上一个三位的数字组成。这个数字可以由001一直增加到999。以下为文件内容：

可以看到数据分散在指定的文件中。

二、IMPORT

前边我们已经学习了怎么将数据导出，下面我们来学习怎么将数据导入数据库。注意，默认情况下，当整个过程执行完后才会执行一次提交动作。

1.import基本语法：

IMPORT FROM <file name> OF <file type> <mode> INTO <table name>

table name可以是已经存在的表名、视图名称，当使用IXF文件新建表时，table name为新表名。默认情况下文件中的列和表的列依次对应，如果需要指定对应关系，可以在table name后加入列名列表。

mode有以下五种选择：

INSERT：将数据行插入表中。

INSERT_UPDATE：根据主键查找，如果已经存在，则更新记录，不存在则插入。需要主键支持。

REPLACE：插入数据前清空表。注意，清空表的操作不能被取消

REPLACE_CREATE：类似与REPLACE，如果表不存在，则试图建立目标表。只有在利用SELECT * FROM <table name>所导出的IXF格式文件中才支持这个选项。

CREATE：创建表，并插入数据。

2.高级选项

① 默认值

当导入文件缺失了列，对于使用了with default选项的字段会有两种表现。当导入命令没有指定表列名时，这时不管是否使用了with default选项，缺失的列都填充null值。如果希望使用默认值，需要使用usedefaults选项。当导入命令有指定的表列名时，缺失的列如果有with default选项则使用默认值，如果不希望使用默认值，则使用nodefaults选项。usedefaults和nodefaults选项跟在modified by后。

② commitcount<n>和restartcount<n>选项

import命令默认只有在全部数据导入完成后才执行提交动作，可以通过使用commit选项控制每隔多少行提交一次。

restartcount可以在某次失败后指定导入需要跳过的行数。

③ 改变分隔符和改变分隔符优先级

主要的分隔符有：列分隔符（COLDELx）、字符分隔符（CHARDELx）、十进制数的小数点（Decpt x），而文件中的格式可能各异，所以可以使用和在导出同意的方式指定分隔符。另外，有时候导入的字符串中包含换行符，默认情况下会被认为只是换行的行为，可以通过使用DELPRIORITYCHAR来提升字符分隔符优先级而读入包含换行符的行。分界符的优先级为：记录分隔符>字符串分隔符>列分隔符。也可以通过选项指定日期、时间、时间戳的格式。

④ 对于恒等列和自增列的处理

DB2可以通过两种方式生成自增的列：

1、generated always as identity primary key

2、generated by default as identity primary key

对于方式1，该列只能通过DB2自动指定。而方式2即可以通过DB2自动生成，也可以指定值，并且指定的值可以重复。对于这样的列，import有四个对于的选项。以下是四个选项和恒等列、自增列同时使用的情况：

 by default（允许表中有重复值）always identitymissing认为文件中没有对应的那一列 认为文件中没有对应的那一列generatedmissing使用了文件的id值拒绝identityignore 忽略文件中定义的这一列忽略文件中定义的这一列 generatedignore使用了文件的id值  拒绝⑤lob字段

导入大字段值，使用和export相同的选项，即modified by lobsfile。指定路径名称，使用lobs from，路径名称后要加分隔符。

3 使用import需要注意的地方：

① 自增列

② 字符串中有换行

③ 大字段

④ 参照列

⑤ 数据太多使用commitcount选项

另外，以下是笔者总结的一些特性：

--文件超出长度，会被截断

--insert_update必须要有主键

--已有主键 insert模式下被拒，insert_update模式下更新

--自增主键，不能使用insert_update更新

--其中某一行不符合插入的标准，不影响下一行的插入

三、LOAD

load和import相比有很大的性能优势，所以它是导入数据的一个常用工具。使用load至少需要load和insert权限。load装入第一个阶段有三个过程：格式化数据 ----》 在页缓存中打包 ----》导入表空间容器中。因为它与数据库紧密集成，是把数据直接格式化转入表空间中，并且不记录日志，所以性能很高。但是它也有一些缺点：使用时会锁表空间，不记录日志，不会触发触发器。load的三个阶段分别是装入、构建、删除。第二个阶段只有在有索引的时候才会存在，第三个阶段在数据不符合唯一性约束的时候才会出现。load的基本语法如下：

LOAD FROM <input file> OF <file type> <mode> INTO <table name>

mode有四种选项：

insert：插入表

replace：先删除表，再插入

restart和terminate：load命令中断的时候是可以选择从上次断开的地方继续开始或者中断上次操作。使用这两个选项时命令其他部分要和上次命令一致。

以下是使用示例：

与前滚恢复相关的恢复策略：

                      ① 装入不进行复制，其后紧随备份：默认情况下开启了日志归档的数据库在执行装入操作后表空间会处于备份暂挂状态，这时候只能执行装入操作。当执行了备份后，表空间恢复到正常状态。在执行前滚恢复时，系统会处于复原挂起状态，指定执行了备份复原后才能继续前滚恢复。

                      ② 具有复制映像的装入：使用 copy yes to <target>[,<target>]该装入模式会建立增量备份映像。它里面所包含的改变将会在前滚恢复操作期间，在处理装入提交日志记录的时候自动重新应用。因为在向前滚动恢复的时候需要装入副本映像，所以应该对其进行归档（没看懂，是自动的被应用还是先要手动归档）

                      ③ 不可恢复装入：使用nonrecoverable选项。当做前滚恢复时，表会被标志位不可用，需要在恢复完成后删除表并重新装入

使用装入，要注意异常表以及选择合适的策略以备做前滚恢复。其他的注意事项类似与imoprt命令。