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. into table_name指定表导入数据方式:inset(追加),insert_update(有主键,主键匹配更新,否则增加),replace(删除表数据,然后插入)
4.commitcount 和数据库提交相关,默认是automatic:db2内部自动计算什么时候提交
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.