DB2数据迁移之load

最新推荐文章于 2022-04-14 15:40:03 发布

DB2zone

最新推荐文章于 2022-04-14 15:40:03 发布

阅读量1.9k

点赞数

文章标签： db2 access character command schema table

一.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