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.