DB2数据迁移之load

来源：互联网发布：javascript 上传文件编辑：程序博客网时间：2024/06/15 03:39

一.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.data10,"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 tablesTable/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                     ColumnColumn name                     schema    Data type name      Length     Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------ACTNO                           SYSIBM    SMALLINT                     2     0 NoACTKWD                          SYSIBM    CHARACTER                    6     0 NoACTDESC                         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                     ColumnColumn name                     schema    Data type name      Length     Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------ACTNO                           SYSIBM    SMALLINT                     2     0 NoACTKWD                          SYSIBM    CHARACTER                    6     0 NoACTDESC                         SYSIBM    VARCHAR                     20     0 NoTS                              SYSIBM    TIMESTAMP                   10     0 YesMSG                             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.dmpmessages xifenfei.msg insert into xifenfei_load for exception xifenfei_exp"Number of rows read         = 18Number of rows skipped      = 0Number of rows loaded       = 16Number of rows rejected     = 2Number of rows deleted      = 2Number of rows committed    = 18SQL3107W  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.00030,,"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