DB2-LOAD工具详解

LOAD工具详解

LOAD是DB2数据库中一种数据移动实用程序，LOAD的原理是将输入的数据源直接格式化成数据页存储到数据库中，在装载过程不会触发触发器，并且除了唯一性约束以外不执行引用约束检查或表约束检查，只记录少量日志或不记录日志。因此对于需要将大量的数据插入到表中的场景具有特别高的性能。LOAD支持理绝大多数数据类型，其中包括 XML、大对象 (LOB)和用户定义的类型 (UDT)。 

LOAD实用程序支持以下源数据

      1.del定界的ASCII文件

       2.ASC非定界的ASC文件

       3.IXF 数据库改版的集成交换格式文件

       4.源数据库表

 

LOAD装入操作分为以下几个阶段

（1）、分析阶段

目标表为列式存储的表才会有此阶段，行式存储表则没有。

 

（2）、装入阶段

     装入阶段将源数据解析成物理数据页的格式，直接装入到数据页中。必要时还收集索引键和表统计信息。

 

（3）、构建索引阶段

     根据在装入阶段收集的索引键创建表索引。

 

（4）、删除重复阶段

     在此阶段将违反了主键约束和唯一约束的行删除（主键约束包含了唯一约束），如果定义了异常表，这些删除的行将会插入到异常表中，在装载完成后可以查询异常来查看那些行违反了唯一性约束

 

LOAD命令及常用选项

load client from FILENAME of del modified by codepage=1386coldel; chardel’ dumpfile=PATH/FILENAME messages PATH/FILENAME insert into TABLEBAME for exception TABLENAME ALLOW READ ACCESS

选项说明

LOAD  说明此次操作为LOAD

client  此选项说明load在客户端而非数据库服务器上操作，如果在数据库服务器上操作load，则不要加该参数

from   源数据文件，需要指定文件的路径及文件名，如果加了client参数，该路劲必须为绝对路径。

of del  数据文件格式，del表示源文件为定长的ASC文件

modified by指定文件类型修饰符选项，在by后面到insert前面的参数都属于该选项的参数，这些参数可以组合使用，每个选项用空格隔开。以下对常用的选项进行说明

codepage=1386 指定输入的源数据文件的代码页，如果输入的源数据文件与目标数据库的代码页不一致，可以能会出现乱码。此选项指定输入的源数据文件代码页，load根据指定的代码页转换为目标数据库的代码页，如果导入的源数据量非常大时，转换过程会很消耗时间，因此建议在生成源数据文件时就指定代码页与数据库代码页一致，这样就可以节省代码页转换时间。

coldel;       指定插入数据的列分隔符为分号;。如果不指定该参数，默认列分隔符为逗号,。如果源文件中的列分隔符是其它分隔符，则通过该参数进行指定，coldel与分隔符中间不要留空格。

chardel      指定插入的源数据中字符串分隔符为单引号’ ‘。如果不指定该参数，默认的字符串分隔符为双引号” ”，如果源数据中字符串分隔符为其它分隔符，则通过该参数进行指定，chardel与分隔符中间不要留空格。

dumpfile=PATH/FILENAME  指定转存文件的目录及文件名，在装入阶段，如果源文件的某一行中有某些的数据类型不符合目标表中对应的列的数据类型时，则将该行数据保存在该转存文件中，在load操作完成后可以查看该文件，检查哪些行有不符合的数据类型，这个文件在装入阶段生成的。该文件保存在服务器中。

messages PATH/FILENAME  指定在load过程产生的信息文件，该文件在load结束后生成，可以查看该文件了解load操作过程中的相关信息。如果LOAD命令从client发起，该信息文件保存在客户端。

insert   表示将源数据插入到目标表中而不影响目标表中原有的数据，该选项不影响目标表原有的数据，适合于对目标表进行追加数据操作。除了insert外还有replace、restart、terminate。详细说明如下

       replace   表示先将目标表中数据清空，然后再将源数据插入到目标表中，该选项会破坏目标表原有数据，谨慎使用

       terminate 表示终止load操作，并将数据恢复到load开始时的状态。如果在load.. replace出现load pending，采用load..terminate会清空表数据。

       restart    表示重启被中断的load操作，restart会使用之前load时产生的临时文件，并从最近一个点开始重新load，因此不要手动删除load产生的临时文件，这些临时文件会在load完成后自动删除，这些临时文件会在当前工作目录中生成，也可以通过tempfile path选项指定，这些临时文件大小与源数据文件大小相同。

       说明：insert、replace在刚刚开始进行load进行指定，两者选一个。terminate、restart在load过程中因为中断或者失败导致表处于load pending状态，为了处理这些异常而进行的后续处理。

into TABLENAME 需要将源数据插入的目标表

for exception TABLENAME 指定异常表，该表主要保存在删除阶段因为违反唯一性约束而插入失败的行数据，该表必须在load之前创建好，表结构前N列定义与目标表相同列定义要一致，只是在表最后增加两列，一列用于记录行被插入的时间戳，一列用于记录插入失败原因信息的clob列。如果不指定异常表被删除的行会丢失。

ALLOW READ ACCESS 指定LOAD表处于只读状态。默认情况LOAD期间目标表会处于不可读状态，需等LOAD完成后表处于正常状态才可对表进行DML操作。若在LOAD命令加上该参数，则其它应用可读取目标表中LOAD之前已存在的数据，正在插入的数据仍不可读。此参数不能与replace同时使用，否则会报SQL3340N代码为1错误。

 

 

Normal State

正常状态

是创建表后该表的初始状态，它指示当前没有（异常）状态影响表。

Read Access Only

只读访问

如果指定了 ALLOW READ ACCESS 选项，那么表将处于“只读访问”状态。
在调用 LOAD 命令前存在的表数据在装入操作运行期间可供只读访问。
如果指定了 ALLOW READ ACCESS 选项并且装入操作失败，那么在装入操作前存在的表数据在故障发生后将继续可供只读访问。

Load in Progress

正在装入

表状态指示正在表上进行装入。在装入操作成功完成后，装入实用程序将除去此瞬时状态。
但是，如果装入操作失败或被中断，那么表状态将更改为“装入暂挂”。

Load Pending

装入暂挂

指示装入操作失败或被中断。可以执行下列其中一个步骤来除去“装入暂挂”状态：
1.找出故障原因。例如，如果装入实用程序耗尽了磁盘空间，那么对表空间添加容器。然后，重新启动装入操作。
2.终止装入操作。

3.对装入操作失败时所处理的那个表运行 load REPLACE操作。
4.使用最新的表空间或数据库备份，通过 RESTORE DATABASE 命令恢复所装入的表的表空间，然后执行进一步的恢复操作。

Set Integrity Pending

设置完整性暂挂

状态指示已装入的表有未经验证的约束。当装入实用程序开始对带有约束的表执行装入操作时，它就会使该表处于此状态。
使用 SET INTEGRITY 语句以使该表脱离“设置完整性暂挂”状态。命令如下：

db2 SET INTEGRITY FOR TABLENAME IMMEDIATE CHECKED

Set Integrity Pending

不可用

对不可恢复的装入操作执行前滚将使表处于“不可用”状态。处于此状态时，表不可用；必须删除该表或通过备份复原表。

 

LOAD操作期间和之后的表状态

 

 

 

LOAD监控及表状态监控

load属于DB2的一个实用程序，可以通过db2 LIST UTILITIES  SHOWDETAIL

来监控load的进程。

    对于表状态的监控则用通过名：db2 loadquery table TABLENAME来监控，其中TABLENAME为LOAD操作的目标表名。

详细监控情况如下

  LOAD监控

 

[db2inst1@localhost ~]$ db2 LIST UTILITIES SHOW DETAIL

 

ID                              = 1

Type                            = LOAD

Database Name                   = DB2TEST

Member Number                   = 0

Description                     = [LOADID: 18.2017-08-22-18.05.13.898141.0 (2;276)] [*LOCAL.db2inst1.170822095115] OFFLINE LOAD DEL AUTOMATIC INDEXING INSERT COPY NO DMS    .MT_RUNTIMES

Start Time                      = 2017-08-22 18:05:13.942863

State                           = Executing

Invocation Type                 = User

Progress Monitoring:

   Phase Number                 = 1

      Description               = SETUP

      Total Work                = 0 bytes

      Completed Work            = 0 bytes

      Start Time                = 2017-08-22 18:05:13.942887

 

   Phase Number [Current]       = 2

      Description               = LOAD

      Total Work                = 12985786 rows

      Completed Work            = 662834 rows

      Start Time                = 2017-08-22 18:05:14.087481

 

   Phase Number                 = 3

      Description               = BUILD

      Total Work                = 1 indexes

      Completed Work            = 0 indexes

      Start Time                = Not Started

可以看到在‘PhaseNumber’后面有[Current]标识的，说明正在进行该操作。

 ‘Total Work’表示需要插入的数据总行数，‘Completed Work’表示已完成的行数。

 

LOAD期间表状态监控

[db2inst1@localhost db2dump]$ db2 load query table DMS.MT_RUNTIMES

SQL3501W The table space(s) in which the table resides will not be placed in

backup pending state since forward recovery is disabled for the database.

 

SQL3109N The utility is beginning to load data from file "/dev/full".

 

SQL3500W The utility is beginning the "LOAD" phase at time "2017-08-22

13:24:13.657701".

 

SQL3519W Begin Load Consistency Point. Input record count = "0".

 

SQL3520W Load Consistency Point was successful.

 

SQL3532I The Load utility is currently in the "LOAD" phase.

 

 

Number of rows read        = 0

Number of rows skipped     = 0

Number of rows loaded      = 0

Number of rows rejected    = 0

Number of rows deleted     = 0

Number of rows committed   = 0

Number of warnings         = 0

 

Tablestate:

  Load in Progress

可以看到‘SQL3532I The Load utility iscurrently in the "LOAD" phase’，表示LOAD操作处于‘LOAD’装入阶段。

 

 

LOAD操作常见问题

一、在LOAD过程中，在删除违反唯一性约束阶段会记录事务日志，如果目标表中有某些列创建了唯一性约束，而插入的源数据中该列存在大量重复值，那在LOAD操作的删除阶段会产生大量的事务日志，此时有可能会导致事务日志满操作

   处理：1、修改源数据文件中这些列的值，让这些列值符合唯一性

        2、如果需要将源数据中这些重复值插入目标表中，则先将目标表中这些列的唯一约束删除，然后在执行LOAD操作。

        3、增加事务日志大小，以满足此次LOAD操作需求

 

二、执行LOAD命令的用户对源数据文件无权限，此时会报SQL3508N错

处理：将源文件的读权限授予执行load命令的用户，在重新执行load命令