DB2数据迁移之export/import

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.