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的过程中删除
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:惜分飞 来源: 惜分飞
- 标签: 2数据迁移 DB load
- 发布时间:2012-04-22 15:17:51
- [66] Oracle MTS模式下 进程地址与会话信
- [66] Go Reflect 性能
- [65] 如何拿下简短的域名
- [59] android 开发入门
- [59] 图书馆的世界纪录
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [58] 【社会化设计】自我(self)部分――欢迎区
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 界面设计速成
- [46] 读书笔记-壹百度:百度十年千倍的29条法则