DB2数据迁移之db2lock/db2move

1.模拟带有identity表

[db2inst1@xifenfei ~]$ db2 "create table t_xff(xid smallint not null generated always as identity 
> (start with 1,increment by 1),x_name varchar(200)) in ts_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from t_ff"
SQL0204N  "DB2INST1.T_FF" is an undefined name.  SQLSTATE=42704
[db2inst1@xifenfei ~]$ db2 "select * from t_xff"

XID    X_NAME           
------ -------------
     1 www.xifenfei.com 
     2 XIFENFEI         
     3 xifenfei         

  3 record(s) selected.

2.导出表结构

[db2inst1@xifenfei ~]$ mkdir move_s
[db2inst1@xifenfei ~]$ cd move_s/
[db2inst1@xifenfei move_s]$ db2look -d sample -e -l -o db2_sample.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- Output is sent to file: db2_sample.ddl

3.导出数据

[db2inst1@xifenfei move_s]$ db2move sample export

Application code page not determined, using ANSI codepage 1208

*****  DB2MOVE  *****

Action:  EXPORT

Start time:  Thu Apr  5 20:21:28 2012


Connecting to database SAMPLE ... successful!  Server : DB2 Common Server V9.5.9

Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!

Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!

EXPORT:     18 rows from table "DB2INST1"."ACT"
EXPORT:      5 rows from table "DB2INST1"."CL_SCHED"
EXPORT:     14 rows from table "DB2INST1"."DEPARTMENT"
EXPORT:     42 rows from table "DB2INST1"."EMPLOYEE"
EXPORT:  10000 rows from table "DB2INST1"."EMPMDC"
EXPORT:     73 rows from table "DB2INST1"."EMPPROJACT"
EXPORT:      8 rows from table "DB2INST1"."EMP_PHOTO"
EXPORT:      8 rows from table "DB2INST1"."EMP_RESUME"
EXPORT:    145 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT:      0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT:      3 rows from table "DB2INST1"."IN_TRAY"
EXPORT:      8 rows from table "DB2INST1"."ORG"
EXPORT:      5 rows from table "SYSTOOLS"."POLICY"
EXPORT:     65 rows from table "DB2INST1"."PROJACT"
EXPORT:     20 rows from table "DB2INST1"."PROJECT"
EXPORT:     41 rows from table "DB2INST1"."SALES"
EXPORT:     35 rows from table "DB2INST1"."STAFF"
EXPORT:     35 rows from table "DB2INST1"."STAFFG"
EXPORT:      3 rows from table "DB2INST1"."T_XFF"

Disconnecting from database ... successful!

End time:  Thu Apr  5 20:21:32 2012

4.目标端创建数据库

C:\Windows\system32>db2 "create db db_XFF pagesize 8 k"
DB20000I  CREATE DATABASE命令成功完成。

5.目标端创建对象

C:\Windows\system32>DB2 -tvf D:\move_s\db2_sample.ddl -l d:\xifenfei.log

--检查xifenfei.log文件,发现错误,手工修复

6.导入数据文件

D:\move_s>db2move db_xff load

*****  DB2MOVE  *****

Action:  LOAD

Start time:  Sun Apr 15 23:00:17 2012


Connecting to database DB_XFF ... successful!  Server : DB2 Common Server V9.5.0

Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2COMMON.BND ... successful!

Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2MOVE.BND ... successful!

* LOAD:  table "DB2INST1"."ACT"                
  -Rows read:         18
  -Loaded:            18
  -Rejected:           0
  -Deleted:            0
  -Committed:         18

* LOAD:  table "DB2INST1"."CL_SCHED"           
  -Rows read:          5
  -Loaded:             5
  -Rejected:           0
  -Deleted:            0
  -Committed:          5

--中间很多记录省略

--发现identity表导入失败，需要手工处理
* LOAD:  table "DB2INST1"."T_XFF"              
***  WARNING 3107.  Check message file tab19.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  消息文件中至少有一条警告消息。


  -Rows read:          3
  -Loaded:             0
  -Rejected:           3
  -Deleted:            0
  -Committed:          3


Disconnecting from database ... successful!

End time:  Sun Apr 15 23:00:26 2012

7.重新导入identity表

D:\move_s>db2 connect to db_xff

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = XIFENFEI
 本地数据库别名       = DB_XFF
D:\move_s>DB2 "load from tab19.ixf of ixf modified by identityoverride insert into db2inst1.t_xff"
SQL3501W  由于禁用数据库正向恢复，因此表所驻留的表空间将不被置于备份暂挂状态。

SQL3551W  表至少包含实用程序将覆盖的一个 GENERATED ALWAYS 列。

SQL3109N  实用程序正在开始从文件 "D:\move_s\tab19.ixf" 装入数据。

SQL3500W  在时间 "2012-04-15 23:06:52.393775"，实用程序在开始 "LOAD"。

SQL3150N  PC/IXF 文件中的 H 记录具有产品 "DB2    02.00"，日期 "20120405"和时间 "202132"。

SQL3153N  PC/IXF 文件中的 T 记录具有名称 "tab19.ixf"，限定符 "" 和源 ""。

SQL3519W  开始装入一致点。输入记录数 = "0"。

SQL3520W  “装入一致点”成功。

SQL3110N  实用程序已完成处理。从输入文件读了 "3" 行。

SQL3519W  开始装入一致点。输入记录数 = "3"。

SQL3520W  “装入一致点”成功。

SQL3515W  在时间 "2012-04-15 23:06:52.451619"，实用程序已经完成了 "LOAD"。


读取行数         = 3
跳过行数         = 0
装入行数         = 3
拒绝行数         = 0
删除行数         = 0
落实行数         = 3

D:\move_s>db2 "select * from db2inst1.t_xff"

XID    X_NAME


------ ------------------------------
     1 www.xifenfei.com
     2 XIFENFEI
     3 xifenfei

  3 条记录已选择。