DB2数据迁移之load

发表于 2012 年 4 月 15 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：DB2数据迁移之load

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一.load原理性知识
1.为什么要使用LOAD
load不需要写日志(或很少日志),不做检查约束和参照完整性约束,不触发Trigger,锁的时间比较短,因此特别适合大数据量的导入.

2.load过程分为4个阶段
load/build/delete/index copy.
load阶段是将源文件parser成物理数据存储的格式,直接装入到页中,而不通过db2引擎,load阶段会检查表定义,违背定义的数据不会装入到表中.
build阶段建议索引（如果装入表有索引的话）,会检查唯一性约束,违背了唯一性的数据会在delete阶段删除.
index copy阶段将index数据从指定的临时表空间拷贝到初始的表空间里.
index copy只适应于allow read access场景.load的4个阶段会记录在messages文件里.

3.load的offline和online
缺省情况下,load过程不允许其他应用访问表,即allow no access,或叫offline load（离线加载）.Allow read access,或叫online load(在线加载）,只有在load …insert into的时候才允许使用,其他应用读到的数据是加载前的数据,load … replace into会将数据先删除,再load,只能是离线加载.

4.load表的状态
load可能出现的几种状态,某一时刻可能会同时处于几种状态.只有当表是normal状态时,表才能进行正常的增删改查操作.
normal: 正常状态
set integrity pending: 如果目标表有check约束或reference约束,那么Load后此表处于set integrity pending,表明表有约束还未检查,稍后解释.
load in progress:load正在数据加载过程中.
load pending:数据提交前出现了故障,需要通过load..terminate,load..replace或load..restart解除暂挂状态
read access only:目标表数据是可以读的,当load时指定了allow read access,那表就会处于read access only状态
unavailable:表可能被删除了或从backup中恢复了.
unknown:通过load..query命令无法得知表的状态.

二.load试验测试
需要导入数据

[db2inst2@xifenfei ~]$ more /tmp/xifenfei.data 
10,"MANAGE","MANAGE/ADVISE"
20,"ECOST ","ESTIMATE COST"
30,,"DEFINE SPECS"
40,"LEADPR","LEAD PROGRAM/DESIGN"
50,"SPECS ",
60,"LOGIC ","DESCRIBE LOGIC"
70,"CODE  ","CODE PROGRAMS"
80,"TEST  ","TEST PROGRAMS"
90,"ADMQS ","ADM QUERY SYSTEM"
100,"TEACH ","TEACH CLASSES"
110,"COURSE","DEVELOP COURSES"
60,"STAFF ","PERS AND STAFFING"
130,"OPERAT","OPER COMPUTER SYS"
140,"MAINT ","MAINT SOFTWARE SYS"
150,"ADMSYS","ADM OPERATING SYS"
160,"ADMDB ","ADM DATA BASES"
170,"ADMDC ","ADM DATA COMM"
80,"DOC   ","DOCUMENT"

一共18条记录

创建目标表

[db2inst2@xifenfei ~]$ db2 "CREATE TABLE XIFENFEI_LOAD(ACTNO SMALLINT NOT NULL primary key,
> ACTKWD CHAR(6) NOT NULL,ACTDESC VARCHAR(20) NOT NULL)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
XIFENFEI_LOAD                   DB2INST2        T     2012-04-05-18.53.42.296503

  1 record(s) selected.

[db2inst2@xifenfei ~]$ db2 describe table xifenfei_load

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No    
ACTKWD                          SYSIBM    CHARACTER                    6     0 No    
ACTDESC                         SYSIBM    VARCHAR                     20     0 No    

  3 record(s) selected.

创建异常表

[db2inst2@xifenfei ~]$ db2 "create table xifenfei_exp like xifenfei_load"
DB20000I  The SQL command completed successfully.

[db2inst2@xifenfei ~]$ db2 "alter table xifenfei_exp add column ts timestamp
> add column msg clob(32k)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 describe table xifenfei_exp

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ACTNO                           SYSIBM    SMALLINT                     2     0 No    
ACTKWD                          SYSIBM    CHARACTER                    6     0 No    
ACTDESC                         SYSIBM    VARCHAR                     20     0 No    
TS                              SYSIBM    TIMESTAMP                   10     0 Yes   
MSG                             SYSIBM    CLOB                     32768     0 Yes   

  5 record(s) selected.

load导入数据

[db2inst2@xifenfei ~]$ db2 "load from /tmp/xifenfei.data of del modified by dumpfile=/tmp/xifenfei.dmp
messages xifenfei.msg insert into xifenfei_load for exception xifenfei_exp"

Number of rows read         = 18
Number of rows skipped      = 0
Number of rows loaded       = 16
Number of rows rejected     = 2
Number of rows deleted      = 2
Number of rows committed    = 18

SQL3107W  There is at least one warning message in the message file.

提示一共18条记录,成功了14条,拒绝2条,删除2条

查看表中记录

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_load"

ACTNO  ACTKWD ACTDESC             
------ ------ --------------------
    10 MANAGE MANAGE/ADVISE       
    20 ECOST  ESTIMATE COST       
    40 LEADPR LEAD PROGRAM/DESIGN 
    60 LOGIC  DESCRIBE LOGIC      
    70 CODE   CODE PROGRAMS       
    80 TEST   TEST PROGRAMS       
    90 ADMQS  ADM QUERY SYSTEM    
   100 TEACH  TEACH CLASSES       
   110 COURSE DEVELOP COURSES     
   130 OPERAT OPER COMPUTER SYS   
   140 MAINT  MAINT SOFTWARE SYS  
   150 ADMSYS ADM OPERATING SYS   
   160 ADMDB  ADM DATA BASES      
   170 ADMDC  ADM DATA COMM       

  14 record(s) selected.

果真14条记录,和文件相比缺少4条记录

查看dump file

[db2inst2@xifenfei ~]$ more /tmp/xienfei.dmp.load.000 
30,,"DEFINE SPECS"
50,"SPECS ",

发现两条违背表定义记录,导入过程中,直接被拒绝的两条

查看异常表

[db2inst2@xifenfei ~]$ db2 "select * from xifenfei_exp"

ACTNO  ACTKWD ACTDESC              TS                         MSG  
------ ------ --------------------  
    60 STAFF  PERS AND STAFFING    2012-04-05-19.02.19.984440 00001I0000500001 
    80 DOC    DOCUMENT             2012-04-05-19.02.19.984440 00001I0000500001

发现两条违背唯一性约束记录,构建index的过程中删除

此条目发表在 DB2 分类目录。将固定链接加入收藏夹。

《DB2数据迁移之load》有 1 条评论

  1. 惜分飞 说：
    2012 年 4 月 15 日21:23

    load日志显示了四个阶段

    [db2inst2@xifenfei ~]$ more xifenfei.msg 
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 
"/tmp/xifenfei.data".

SQL3500W  The utility is beginning the "LOAD" phase at time "04/05/2012 
19:02:20.093713".

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

SQL3520W  Load Consistency Point was successful.

SQL3116W  The field value in row "F0-3" and column "2" is missing, but the 
target column is not nullable.

SQL3185W  The previous error occurred while processing data from row "F0-3" of 
the input file.

SQL3116W  The field value in row "F0-5" and column "3" is missing, but the 
target column is not nullable.

SQL3185W  The previous error occurred while processing data from row "F0-5" of 
the input file.

SQL3227W  Record token "F0-3" refers to user record number "3".

SQL3227W  Record token "F0-5" refers to user record number "5".

SQL3110N  The utility has completed processing.  "18" rows were read from the 
input file.

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

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "04/05/2012 
19:02:20.207155".

SQL3500W  The utility is beginning the "BUILD" phase at time "04/05/2012 
19:02:20.211707".

SQL3213I  The indexing mode is "REBUILD".

SQL3515W  The utility has finished the "BUILD" phase at time "04/05/2012 
19:02:20.365549".

SQL3500W  The utility is beginning the "DELETE" phase at time "04/05/2012 
19:02:20.410288".

SQL3509W  The utility has deleted "2" rows from the table.

SQL3515W  The utility has finished the "DELETE" phase at time "04/05/2012 
19:02:20.434643".

SQL3107W  There is at least one warning message in the message file.


Number of rows read         = 18
Number of rows skipped      = 0
Number of rows loaded       = 16
Number of rows rejected     = 2
Number of rows deleted      = 2
Number of rows committed    = 18