联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
export导出数据
[db2inst1@xifenfei ~]$ db2 connect to sample Database Connection Information Database server = DB2/LINUX 9.5.9 SQL authorization ID = DB2INST1 Local database alias = SAMPLE [db2inst1@xifenfei ~]$ db2 "SELECT * FROM DB2INST1.ACT" ACTNO ACTKWD ACTDESC ------ ------ -------------------- 10 MANAGE MANAGE/ADVISE 20 ECOST ESTIMATE COST 30 DEFINE DEFINE SPECS 40 LEADPR LEAD PROGRAM/DESIGN 50 SPECS WRITE 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 120 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 180 DOC DOCUMENT 18 record(s) selected. [db2inst1@xifenfei ~]$ db2 "export to sample.act of del messages xifenfei.log select * from DB2INST1.ACT" Number of rows exported: 18 [db2inst1@xifenfei ~]$ more sample.act 10,"MANAGE","MANAGE/ADVISE" 20,"ECOST ","ESTIMATE COST" 30,"DEFINE","DEFINE SPECS" 40,"LEADPR","LEAD PROGRAM/DESIGN" 50,"SPECS ","WRITE 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" 120,"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" 180,"DOC ","DOCUMENT"
import导入数据
[db2inst2@xifenfei ~]$ more create.act CREATE TABLE ACT_COPY ( ACTNO SMALLINT NOT NULL , ACTKWD CHAR(6) NOT NULL , ACTDESC VARCHAR(20) NOT NULL ) IN USERSPACE1; [db2inst2@xifenfei ~]$ db2 -tvf create.act CREATE TABLE ACT_COPY ( ACTNO SMALLINT NOT NULL , ACTKWD CHAR(6) NOT NULL , ACTDESC VARCHAR(20) NOT NULL ) IN USERSPACE1 DB20000I The SQL command completed successfully. [db2inst2@xifenfei ~]$ db2 list tables Table/View Schema Type Creation time ------------------------------- --------------- ----- -------------------------- ACT_COPY DB2INST2 T 2012-04-05-16.40.25.103571 1 record(s) selected. [db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del messages xifenfei.log insert into act_copy" Number of rows read = 18 Number of rows skipped = 0 Number of rows inserted = 18 Number of rows updated = 0 Number of rows rejected = 0 Number of rows committed = 18 [db2inst2@xifenfei ~]$ db2 "select * from act_copy" ACTNO ACTKWD ACTDESC ------ ------ -------------------- 10 MANAGE MANAGE/ADVISE 20 ECOST ESTIMATE COST 30 DEFINE DEFINE SPECS 40 LEADPR LEAD PROGRAM/DESIGN 50 SPECS WRITE 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 120 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 180 DOC DOCUMENT 18 record(s) selected.
补充说明
1.chardel 指定字符串分隔符,默认是””
2.lobs to path 指定lob目录,modified by lobsinfile 指定保存一个文件;modified by lobsinsepfiles 指定每个值保存一个文件
3.
4.commitcount
5.restartcount/skipcount N 表示跳过前N条记录,从N+1开始继续导入
6.rowcount N 表示插入条数
[db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del restartcount 10 rowcount 6 messages xifenfei.log insert into act_copy" Number of rows read = 16 Number of rows skipped = 10 Number of rows inserted = 6 Number of rows updated = 0 Number of rows rejected = 0 Number of rows committed = 16
7.插入指定列举例
[db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del method P(1,3) restartcount 10 rowcount 2 messages xifenfei.log replace into act_copy(ACTNO,ACTDESC)" SQL0668N Operation not allowed for reason code "7" on table "DB2INST2.ACT_COPY". SQLSTATE=57016 [db2inst2@xifenfei ~]$ db2 reorg table act_copy DB20000I The REORG command completed successfully. [db2inst2@xifenfei ~]$ db2 "import from /home/db2inst1/sample.act of del method P(1,3) restartcount 10 rowcount 2 messages xifenfei.log replace into act_copy(ACTNO,ACTDESC)" Number of rows read = 12 Number of rows skipped = 10 Number of rows inserted = 2 Number of rows updated = 0 Number of rows rejected = 0 Number of rows committed = 12 [db2inst2@xifenfei ~]$ db2 "select * from act_copy" ACTNO ACTKWD ACTDESC ------ ------ -------------------- 110 - DEVELOP COURSES 120 - PERS AND STAFFING 2 record(s) selected.