LOAD工具使DB2各种暂挂问题解决方案整理

为了加快数据的装载速度，许多DBA们喜欢使用load工具进行数据的迁移和装载，但是load工具带来便利性的同时也为我们带来一些问题，今天就着重介绍一下当我们再使用Load工具时遇到问题后的解决办法。
众所周知，load过程分为4个阶段：load/build/delete/index copy。load阶段是将源文件parser成物理数据存储的格式，直接装入到页中，而不通过db2引擎，load阶段会检查表定义，违背定义的数据不会装入到表中。build阶段建议索引（如果装入表有索引的话），会检查唯一性约束，违背了唯一性的数据会在delete阶段删除。index copy阶段将index数据从指定的临时表空间拷贝到初始的表空间里，index copy只适应于allow read access场景。load的4个阶段会记录在messages文件里。而db2 load如果不加nonrecoverable可能导致table space变为backup pending等状态。
本文主要介绍 DB2 LOAD 命令所提供的 COPY NO/COPY YES/NONRECOVERABLE 选项，以及与之相关的注册表变量的使用方法等。
由于 LOAD 最小化了日志的记录，因启用了前滚恢复的数据库在恢复在线备份时需要归档日志的特性，对于这种数据库的 LOAD 操作，为避免执行 LOAD 操作后，表在使用 ROLLFORWARD 命令前滚归档日志的过程中因缺少日志而被置为非正常状态，DB2 为 LOAD 命令提供了如下选项：
・COPY NO（缺省）
・COPY YES
・NONREVERABLE
1. 进行一次数据库的全备份：
首先对已启用前滚恢复模式的 SAMPLE 数据库进行一次全备份：
E:TEST>db2 backup db sample
备份成功。此备份映像的时间戳记是：20051230174105
这时看到在当前目录下产生了一个 SAMPLE.0 的子目录，表明产生的是一个数据库全备份。下面将对这些现象逐个予以举例说明：
2. 关于 COPY NO的备份暂挂：
在 LOAD 操作结束时，将表所在的表空间置于“备份暂挂”状态，此时虽然其中的表可以进行 SELECT 操作，但不能进行 UPDATE 和 DELETE 操作。为使该表状态恢复正常，除去备份暂挂状态，必须手动对其表空间执行一个 BACKUP 命令。由于该选项为缺省选项，如果 LOAD 命令中未指明，则默认为使用该选项，如：
E:TEST>db2 connect to sample
E:TEST>db2 load from staff.del of del insert into staff
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0020
详细解释：
备份暂挂：
E:TEST>db2 select count(*) from staff
1
-----------
70
1 条记录已选择。
E:TEST>db2 update staff set id=335 where id=340
DB21034E 该命令被当作 SQL 语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0290N 不允许存取表空间。 SQLSTATE=55039
在手动对 USERSPACE1 表空间进行一次备份操作后，表空间状态将正常，再次尝试更新操作就会成功：
E:TEST>db2 backup db sample tablespace (userspace1)
备份成功。此备份映像的时间戳记是：20051230184841
命令完成后可以在当前目录下看到产生了一个 SAMPLE.3 的子目录，表明产生的是一个表空间级的备份。
E:TEST>db2 connect to sample
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0000
详细解释：
正常
3. 关于 COPY NO的复原暂挂：
而所产生这份关于表空间的备份可在数据库因前滚操作将表空间置为“复原暂挂”状态时用于将表空间状态恢复为正常，并恢复 LOAD 操作对该表的修改。如当前滚数据库超过LOAD时间点后，表空间将被置为复原暂挂状态：
E:TEST>db2 restore db sample taken at 20051230174105
DB20000I RESTORE DATABASE 命令成功完成。
E:TEST>db2 rollforward db sample to end of logs and stop
SQL1271W 已恢复数据库 "SAMPLE"，但在节点 "0"上有一个或多个表空间脱机
E:TEST>db2 connect to sample
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0100
详细解释：
复原暂挂
此时可利用这一表空间级的备份进行恢复操作：
E:TEST>db2 restore db sample tablespace (userspace1) taken at 20051230184841
DB20000I RESTORE DATABASE 命令成功完成。
E:TEST>db2 connect to sample
E:TEST>db2 list tablespaces  
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0080
详细解释：
前滚暂挂：
E:TEST>db2 rollforward db sample to end of logs and stop tablespace (userspace1)
前滚状态
输入数据库别名 = sample
节点数已返回状态 = 1
节点号 = 0
前滚状态 = 未暂挂
下一个要读取的日志文件 =
已处理的日志文件 = -
上次落实的事务 = 2005-12-30-10.47.10.000000
DB20000I ROLLFORWARD 命令成功完成。
E:TEST>db2 connect to sample
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0000
详细解释：
正常
E:TEST>db2 update staff set id=335 where id=340
DB20000I SQL 命令成功完成。
可见表空间状态已正常，表也可执行更新操作了。
4. 关于 COPY YES：
在 LOAD 操作结束时，DB2 自动对表所在的表空间进行一次备份操作，因而 LOAD 结束后，表所在的表空间不会再处于“备份暂挂”状态，而为“正常”状态。但由于要进行备份操作，所以这种 LOAD 操作的时间会较没有备份的长。如：
E:TEST>db2 connect to sample
E:TEST>db2 load from staff.del of del insert into staff copy yes to .
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0000
详细解释：
正常
E:TEST>db2 select count(*) from staff
1
-----------
105
1 条记录已选择。
此时可在当前目录下看到一个 SAMPLE.4 的子目录，表明产生的是一个由 LOAD 操作生成的备份，而这份备份将在数据库进行前滚恢复操作时用于重新创建 LOAD 操作对数据库的修改。如：
E:TEST>db2 restore db sample taken at 20051230174105
DB20000I RESTORE DATABASE 命令成功完成。
E:TEST>db2 rollforward db sample to end of logs and stop
前滚状态
输入数据库别名 = sample
节点数已返回状态 = 1
节点号 = 0
前滚状态 = 未暂挂
下一个要读取的日志文件 =
已处理的日志文件 = S0000002.LOG - S0000003.LOG
上次落实的事务 = 2005-12-30-11.48.26.000000
DB20000I ROLLFORWARD 命令成功完成。
E:TEST>db2 connect to sample
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0000
详细解释：
正常
E:TEST>db2 select count(*) from staff
1
-----------
105
1 条记录已选择。
这表明在 SAMPLE.4 下的备份被用于了前滚恢复操作，而重新创建了 LOAD 操作对数据库插入的记录。
5. 关于 NONRECOVERABLE：
该选项会将 LOAD 操作标志为不可恢复，即数据库不能通过后续的前滚操作而被恢复。LOAD 操作结束后，数据库既不会处于“备份暂挂”状态，也不会产生任何的备份。
E:TEST>db2 connect to sample
E:TEST>db2 load from staff.del of del insert into staff nonrecoverable
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0000
详细解释：
正常
虽然 LOAD 之后表空间和表的状态都正常，但如果今后需要执行前滚命令恢复数据库时，前滚操作将跳过 LOAD 事务的处理，而将 LOAD 的表标记为无效，是对表的任何操作都不能进行。如：
E:TEST>db2 restore db sample taken at 20051230174105
DB20000I RESTORE DATABASE 命令成功完成。
E:TEST>db2 rollforward db sample to end of logs and stop
前滚状态
输入数据库别名 = sample
节点数已返回状态 = 1
节点号 = 0
前滚状态 = 未暂挂
下一个要读取的日志文件 =
已处理的日志文件 = S0000002.LOG - S0000003.LOG
上次落实的事务 = 2005-12-30-12.19.55.000000
DB20000I ROLLFORWARD 命令成功完成。
E:>db2 connect to sample
E:TEST>db2 list tablespaces
表空间标识 = 2
名称 = USERSPACE1
类型 = 系统管理空间
内容 = 任何数据
状态 = 0x0000
详细解释：
正常
E:>db2 select * from staff
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
SQL1477N 不能存取表 "LIWENLI.STAFF"。 SQLSTATE=55019
这表明该表已不可操作，此时只有将表删除，重新构建，或使用 LOAD 操作时间点之后所做的数据库全备份或表空间备份来恢复该表。
5. 关于注册表变量 DB2_LOAD_COPY_NO_OVERRIDE 的介绍：
另外 DB2 还提供了一个注册表变量：DB2_LOAD_COPY_NO_OVERRIDE，可将 LOAD 的缺省选项 COPY NO 设置为 NONRECOVERABLE 或 COPY YES。具体使用方法举例为：
设置为 COPY YES 的方法:
E:TEST>db2set DB2_LOAD_COPY_NO_OVERRIDE="COPY YES TO E:TEST"
E:TEST>db2 terminate
E:TEST>db2set
DB2_LOAD_COPY_NO_OVERRIDE=COPY YES TO E:TEST
E:TEST>db2 load from staff.del of del insert into staff
SQL27966W DB2_LOAD_COPY_NO_OVERRIDE 注册表变量值 "COPY YES TO E:TEST" 将覆盖在 Load 中指定的 COPY NO 参数。
设置为 NONRECOVERABLE 的方法:
E:TEST>db2set DB2_LOAD_COPY_NO_OVERRIDE=NONRECOVERABLE
E:TEST>db2 terminate
E:TEST>db2set
DB2_LOAD_COPY_NO_OVERRIDE=NONRECOVERABLE
E:TEST>db2 load from staff.del of del insert into staff
SQL27966W DB2_LOAD_COPY_NO_OVERRIDE 注册表变量值 "NONRECOVERABLE" 将覆盖在Load 中指定的 COPY NO 参数。
通过上述对 LOAD 的 COPY NO，COPY YES 和 NONRECOVERABLE 参数，以及 DB2 注册表变量 DB2_LOAD_COPY_NO_OVERRIDE 的详细和举例说明，到此我们已经掌握了它们的功能和使用方法。由于 LOAD 操作几乎不记日志的特性，在对启用了前滚恢复的数据库实现 LOAD 操作时应注意从中选择适当的选项，以保证执行了 LOAD 操作的表的可用性.




DB2 LOAD 工具使用技巧集合

在 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 工具的目标表执行任何的访问。但有如下的特殊情况。

    如果针对目标表的查询是先执行的，那么不论指定了什么选项，LOAD 工具总是不能执行的。直到针对目标表的查询结束，LOAD 工具才能执行。
    如果针对目标表的查询是后执行的，也就是首先执行了 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 FilesIBMSQLLIBBIN>db2 "select * from t1"

 DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION
 -------- -------------- ------- ---------- -------------
 SQL0668N 不允许对表 "YUAN.T1" 执行操作，原因码为 "3" 。 SQLSTATE=57016
 C:Program FilesIBMSQLLIBBIN>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 快速的清空一张表

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

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

注：文件 empfile 为空

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

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

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

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

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