DB2 LOAD 工具使用技巧集合

转贴自  http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0902yuancg/index.html?ca=drs-cn-0223#7.%E4%BD%BF%E7%94%A8LOAD%E8%A7%82%E5%AF%9F%E8%A1%A8%E7%9A%84%E7%8A%B6%E6%80%81%7Coutline

 

在 DB2 数据库的日常使用中，很重要的一项工作就是移动数据，那么在此时就经常会用到 LOAD 工具。DB2 的 LOAD 工具的功能非常强大，而且在很多方面与其他工具比较起来有着突出的优点，这使得 LOAD 工具在 DB2 数据移动方面有着不可替代的作用，尤其是在需要处理大规模数据的情况下表现尤其抢眼。下面结合本人的实践经验介绍一下在使用 LOAD 工具的过程中比较有帮助的一些技巧。

影响 LOAD 性能的选项

LOAD 工具与其他数据移动工具比较起来的一大优势就是提供卓越的性能，这主要是由于 LOAD 对数据的加载时采取数据页级别的处理，这绕过了数据库管理系统的多个处理层次，因此可以极大的提高性能。除了 LOAD 工具本身的这一特点之外，我们还可以通过合理的设置 LOAD 的一些选项来进一步提高其性能。下面列出一些影响 LOAD 性能的选项及其合理设置的建议。

CPU_PARALLELISM n

此 选项用于指定一个 LOAD 同时使用 n 个 CPU 来并发的处理 LOAD，在 LOAD 处理的数据量较大并且操作系统的负载不高的情况下，可以通过此参数指定多个 CPU 并发的执行构建表过程中的解析、转换、格式化等内容来提高效率。如果同时启动了多个 LOAD 工具要注意所有 LOAD 工具指定的此参数最好不要超过操作系统中逻辑 CPU 的总数（在此指定的 CPU 是 LCPU- 即逻辑 CPU）。

此选项不设置，则 DB2 会根据当前操作系统中 CPU 的数量自动的分配 CPU 数量。

DATA BUFFER buffersize

此 选项用于指定 LOAD 工具能够使用的数据缓存的最大值，单位是 4k 。我们可以想象，在处理的数据量很大，且在不超过操作系统空闲物理内存的情况下，我们为 LOAD 分配越多的数据缓存那么 LOAD 的性能将会越好。但是此值设置受到数据库的参数 UTIL_HEAP_SZ 限制。 由于使用 UTIL_HEAP_SZ 的工具有多个，所以为某个 LOAD 分配的数据缓存一般建议不要超过 UTIL_HEAP_SZ 的 50%，在实际设置时，最好根据 UTIL_HEAP_SZ 的实际情况来确定，这可以通过观察数据库快照中工具堆使用的大小和高水位大小来判断。

另外，此选项并非单纯的设置的越大越好，因为在数据缓存设置的最够大以后即使再增加其大小也不会有利于性能的提升，因为性能的瓶颈已经不在是缓存了。所以需要在实际生产中做多次的测试以找到一个最适合的值。

DISK_PARALLELISM n

此选项用于指定 LOAD 工具可以利用向表空间中多个容器执行并发 I/O 的能力来提高性能。根据表空间中容器的数量做适当设置即可。

Modified by ANYORDER

此文件修饰符，可以使用输入文件中预设好的排序结果来提高性能。如果输入的文件来自于 export 工具中使用一定排序谓词到处的数据，那么性能会得到较大提升（可以提升几倍到几十倍）。且此文件修饰符可以用于各种输入的文件格式。

Modified by FASTPARSE

此文件修饰符通过降低对输入数据的检查来提升性能，如果输入的数据与目标表的结构、类型上并无差别，那么可以通过设置此选项来提升性能。此文件修饰符只能用于 ASC 或 DEL 格式的文件。

Modified by NOROWWARNINGS

如果预期在 LOAD 过程会有大量的 warning 出现，那么可以使用此文件修饰符来提升性能。

SAVECOUNT

此 选项可以降低 LOAD 在设置一致性恢复点上面的负载，在处理大数据量的情况下可以使用此选项来提升性能，根据处理数据量的情况来合理的设置此值。如需要加载的数据为 1000 万行，那么可以将此值设置为 10000 。此文件修饰符不能与 anrorder 文件修饰符同时使用。

USER tablespace

当需要 LOAD 的表中存在大量索引需要构建时，且表或索引本身所在的表空间不不是很大的情况下，使用此选项制定一个系统临时表空间，来提高其数据构建的性能。

 

回页首

使用游标（cursor）来提高 LOAD 性能

LOAD 工具在加载数据的时候，输入的数据除了可以是 ixf、asc 和 del 类型的文件之外，还可以是游标（curosr）。如果需要将一个表中的数据保存到另外一张表中，且对应的列的类型是一致的，那么我们就可以使用游标的方式 来实现数据的移动。游标方式之所以能提高数据加载的效率是因为数据不需要以文件的方式保存到磁盘上，而是直接从源数据表到达目标数据表中，这样就可以节省 了两次 I/O 过程（导出的数据以文件保存到磁盘的过程和加载数据时从磁盘上读取数据文件的过程），从而大大提高数据处理的效率。

示例如下：

创建 sql 文件 test.sql, 其内容如下：

清单 1. SQL 语句 —— test.sql

declare cursor mycur for select * from tab1name; load from mycur of cursor insert into tab2name;

 

注：在游标声明中的 SQL 语句根据应用的需要自主确定其内容，load 的选项也可以根据各种需求自主确定。

在 DB2 环境下执行如下命令：

清单 2. 执行 SQL 语句

db2 connect to dbname db2 – tvf test.sql

回页首

代码页的转换

在不同的数据库间移动数据经常会出现代码页不一致的情况，这就需要根据不同的情况分别处理。如果保存数据的文件是 asc 或 del 格式，那么需要使用文件修饰符：codepage，此选项指定数据的源数据库的代码页的值。

清单 3. 示例 1 —— DEL 文件

LOAD from test of del modified by codepage=819 insert into tabname

 

如果保存数据的文件是 ixf 格式，那么不需要 load 工具使用任何选项，且在数据加载的过程中会自动的完成代码页的转换。如果在执行代码页转换的工程中造成数据格式的变化而导致了加载无法成功，则使用 forcein 的文件修饰符，实例如下：

清单 4. 示例 2 —— IXF 文件

LOAD from test of ixf modified by forcein insert into tabname

 

注：当加载的数据在表结构完全相同的情况下还出现了一些非空字符或字符被截断错误，可以选择使用 ixf 格式并指定 forcein 文件修饰符来避免这种问题。

 

回页首

错误行的定位

在 DB2 V8 及 DB2 V9.1 环境下，执行 load 加载数据的过程中可能会出现某些行的部分列的数据不符合目标表类型或格式的要求，从而导致其被截断或被拒绝，那么找到源数据库中的故障行和故障列分析其故 障原因就比较重要。但是在这种故障情况下输出的错误信息可能如下：

表单 5. 执行错误信息

SQL3125W 因为数据比目标数据库列长，所以第 "F4-6" 行第 "5" 列中的字符数据被截断。 SQL3110N 实用程序已完成处理。从输入文件读了 "13" 行。 SQL3519W 开始装入一致点。输入记录数 = "13" 。 SQL3520W “装入一致点”成功。 SQL3515W 在时间 "2009-01-04 11:18:53.301919"，实用程序已经完成了 "LOAD" 。 SQL3107W 消息文件中至少有一条警告消息。

 

其中故障的行的标识为“ F4-6 ”，这表示 id 为 4 的 CPU 处理的第 6 行的数据出现了故障，那么通过这个信息我们是无法从源文件中找到故障行的，我们只能通过总的行号标识来找到故障行，所以在这种情况下只能通过单 CPU 的处理来找到故障行。

这就需要将 load 工具的 CPU_PARALLELISM 选项设置为 1，保证 load 在加载数据的过程中始终只适用一颗 CPU，那么此时行标识中 CPU 部分始终会是“ F0 ”，而行部分则就是总的行数了。

在 DB2 V9.5 中会同时输出 SQL3227W 的消息来帮助定位总的行数，避免了上述问题， 示例如下。

表单 6. 执行错误详细信息

SQL3125W 因为数据比目标数据库列长，所以第 "F4-6" 行第 "5" 列中的字符数据被截断。 SQL3227W 记录标记 "F4-6" 指的是用户记录号 "16" 。 SQL3110N 实用程序已完成处理。从输入文件读了 "53" 行。 SQL3519W 开始装入一致点。输入记录数 = "53" 。 SQL3520W “装入一致点”成功。 SQL3515W 在时间 "2009-01-04 11:18:53.301919"，实用程序已经完成了 "LOAD" 。 SQL3107W 消息文件中至少有一条警告消息。

回页首

无法与查询并行执行的处理

在执行 LOAD 的同时，一般情况下不能对 LOAD 工具的目标表执行任何的访问。但有如下的特殊情况。

1. 如果针对目标表的查询是先执行的，那么不论指定了什么选项，LOAD 工具总是不能执行的。直到针对目标表的查询结束，LOAD 工具才能执行。
2. 如果针对目标表的查询是后执行的，也就是首先执行了 LOAD 工具，在 LOAD 工具的运行期间再执行查询，那么只要 LOAD 工具指定了 allow read access 选项，查询语句就可以后续的执行了。

所以在无法准确的确定 LOAD 工具与查询语句的执行顺序的情况下，而且又需要并发的执行查询，那么 LOAD 工具不是一个好的工具，最好的方式是使用 import 工具替代 LOAD 工具，此时并行查询可以随时启动。

 

回页首

观察正在执行的 LOAD 状态

当一个或多个 LOAD 工具正在执行的情况下，如果需要详细的观察这些工具的执行情况，那么使用命令“ list utilities ”可以实现。示例如下。

清单 7. LOAD 命令执行状态

>db2 list utilities show detail 标识 = 5 类型 = LOAD 数据库名称 = SAMPLE 分区号 = 0 描述 = OFFLINE LOAD DEL AUTOMATIC INDEXING INSERT COPY NO YUAN .T1 开始时间 = 2009-01-05 10:44:57.203583 状态 = 执行 调用类型 = 用户 进度监视： 阶段号 = 1 描述 = SETUP 总计工作 = 0 bytes 已完成的工作 = 0 bytes 开始时间 = 2009-01-05 10:44:57.203592 阶段号 [ 当前 ] = 2 描述 = LOAD 总计工作 = 2174631 rows 已完成的工作 = 1106547 rows 开始时间 = 2009-01-05 10:44:57.248663

 

上面的输出中只有两个阶段，如果有目标表有索引存在的话还会有第三个阶段 -BUILD 阶段。评估正在执行着的 LOAD 工具的剩余时间主要通过比较“总计工作”和“已完成的工作”这个两个值大小和比例。

 

回页首

使用 LOAD 观察表的状态

我 们知道，观察数据库的状态可以通过快照或 list db 命令，观察表空间的状态可以通过快照或 list tablespace 命令，而观察表的状态我们通常只知道快照可以反映表被访问的一些情况，而当表不可用的时候，需要获得表的故障状态时候却没有对应的 list 命令可以使用。

那么这种情况下，我们可以使用 LOAD QUERY 工具来实现。示例如下：

清单 8. 观察表的状态

C:/Program Files/IBM/SQLLIB/BIN>db2 "select * from t1" DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION -------- -------------- ------- ---------- ------------- SQL0668N 不允许对表 "YUAN.T1" 执行操作，原因码为 "3" 。 SQLSTATE=57016 C:/Program Files/IBM/SQLLIB/BIN>db2 load query table t1 SQL3501W 由于禁用数据库正向恢复，因此表所驻留的表空间将不被置于备份暂挂状态。 SQL3109N 实用程序正在开始从文件 "e:/t1.out" 装入数据。 SQL3500W 在时间 "2009-01-05 10:52:56.661277"，实用程序在开始 "LOAD" 。 SQL3519W 开始装入一致点。输入记录数 = "0" 。 SQL3520W “装入一致点”成功。 SQL3005N 处理被中断。 SQL3532I Load 实用程序当前正处于 "LOAD" 阶段。 读取行数 = 0 跳过行数 = 0 装入行数 = 0 拒绝行数 = 0 删除行数 = 0 落实行数 = 0 警告数 = 0 表状态： 装入暂挂

 

由此，我们可以判断，当前的表不能被访问，是由于处于“装入暂挂”的状态，那么我们可以通过 LOAD 工具将 LOAD 执行完 (restart) 或终止 (terminate) 将这个故障处理掉再访问这个表。

 

回页首

使用 LOAD 快速的清空一张表

在不需要一张表中的数据的时候，我需要将其清空。而做这个工作我们可以选择多种办法：

1. 删除表中数据：delete from tablename
2. 不记 log 删除表中数据： alter table tabname activate not logged initially;delete from tablename
3. 修改表不记录 log 方式：alter table tabname activate not logged with empty table
4. LOAD 的 replace 方式：load from empfile of del replace into tabname

注：文件 empfile 为空

“ 1 ”的方式可用性非常差，会消耗大量的时间和 log 空间，一般不使用。除非需要有选择的删除数据的时候。

“ 2 ”的方式可以使用，但是过于麻烦，尤其还要注意两条语句必须在同一事务中执行。

“ 3 ”的方式是一种较好的快速清空数据表的方式。

“ 4 ”的方式也是一种较好的快速清空数据表的方式，而且对于“ 3 ”来说，过程更加可控，因为 load 支持 restart 和 terminate 等故障控制选项。

所以推荐使用“ 4 ”的方式来快速的清空一张表。