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)) ints_xifenfei" DB20000I  The SQL commandcompleted successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('www.xifenfei.com')" DB20000I  The SQL commandcompleted successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('XIFENFEI')" DB20000I  The SQL commandcompleted successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('xifenfei')" DB20000I  The SQL commandcompleted 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 ~]$ mkdirmove_s [db2inst1@xifenfei ~]$ cdmove_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 fortable(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 filetab19.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/NT9.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 条记录已选择。

http://www.xifenfei.com/2924.html