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 条记录已选择。

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