mysql跨平台迁移复制_DB2跨平台数据迁移-db2move导入数据时报错

--使用db2move导出数据

db2move sample export -u inst00 -p ibm2blue

"

EXPORT:     35 rows from table "INST00  "."STAFFG"

EXPORT:      2 rows from table "INST00  "."SUPPLIERS"

Disconnecting from database ... successful!

End time:  Tue May 24 03:42:48 2011

--看到每个表默认导出到一个ixf文件当中

--使用db2look导出ddl

C:\DOCUME~1\ADMINI~1\db2move>db2look -d sample -e -a -o db2look.sql

-- Generate statistics for all creators

-- Creating DDL for table(s)

-- Output is sent to file: db2look.sql

--查看db2look.sql的文件头

-- This CLP file was created using DB2LOOK Version "9.7"

-- Timestamp: 5/24/2011 3:15:52 AM

-- Database Name: SAMPLE

-- Database Manager Version: DB2/NT Version 9.7.0

-- Database Codepage: 1208

-- Database Collating Sequence is: IDENTITY

CONNECT TO SAMPLE;

---------------------------------

-- DDL Statements for Sequences

---------------------------------

CREATE SEQUENCE "INST00  "."SAMPSEQUENCE" AS BIGINT

MINVALUE 1 MAXVALUE 9223372036854775807

START WITH 1 INCREMENT BY 1

CACHE 20 NO CYCLE NO ORDER;

把以上所有相关文件传到linux之后，开始导入数据库结构和数据

1. --使用db2look.sql ddl文件导入数据库结构

2. [db2inst2@localhost tmp]$ db2 -tvf db2look.sql;

3.

4. DB21007E  End of file reached while reading the command.

5. --报出DB21007E错误，这个是本次实验当中唯一一个问题

6. --原因在于

7. [db2inst2@localhost tmp]$ file db2look.sql

8. db2look.sql: ASCII English text, with CRLF line terminators

9. --使用dos2unix进行格式转换

10. [db2inst2@localhost tmp]$ dos2unix db2look.sql

11. dos2unix: converting file db2look.sql to UNIX format ...

12. dos2unix: problems renaming './d2utmpMZpuAR' to 'db2look.sql'

13.           output file remains in './d2utmpMZpuAR'

14. dos2unix: problems converting file db2look.sql

15.

16. --再检查格式，ok

17. [db2inst2@localhost tmp]$ file d2utmpMZpuAR

18. d2utmpMZpuAR: ASCII English text

19.

20. --导入ddl

21. [db2inst2@localhost tmp]$ db2 -tvf ./d2utmpMZpuAR

22. CONNECT TO SAMPLE

23.

24.    Database Connection Information

25.

26.  Database server        = DB2/LINUX 9.7.2

27.  SQL authorization ID   = DB2INST2

28.  Local database alias   = SAMPLE

29.

30.

31. CREATE SEQUENCE "INST00  "."SAMPSEQUENCE" AS BIGINT MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 INCREMENT BY 1 CACHE 20 NO CYCLE NO ORDER

32. DB20000I  The SQL command completed successfully.

33.

34. --成功了，检查一下测试表

35. [db2inst2@localhost ~]$ db2 describe table administrator.aaa

36.

37.                                 Data type                     Column

38. Column name                     schema    Data type name      Length     Scale Nulls

39. ------------------------------- --------- ------------------- ---------- ----- ------

40. AAA                             SYSIBM    INTEGER                      4     0 Yes

41.

42.   1 record(s) selected.

43.

44.

45. --现在是没有数据的

46. [db2inst2@localhost ~]$ db2 "select * from administrator.aaa"

47.

48. AAA

49. -----------

50.

51.   0 record(s) selected.

52.

53. --使用db2move load导入ixf文件

54. [db2inst2@localhost db2move]$ db2move sample load

55.

56. Application code page not determined, using ANSI codepage 1208

57.

58. *****  DB2MOVE  *****

59.

60. Action:  LOAD

61.

62. Start time:  Sun Apr 24 08:07:57 2011

63.

64.

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

66.

67. Binding package automatically ... /home/db2inst2/sqllib/bnd/db2common.bnd ... successful!

68.

69. Binding package automatically ... /home/db2inst2/sqllib/bnd/db2move.bnd ... successful!

70.

71. * LOAD:  table "ADMINISTRATOR"."AAA"

72.   -Rows read:          1

73.   -Loaded:             1

74.   -Rejected:           0

75.   -Deleted:            0

76.   -Committed:          1

77.

78. --再检查测试表，有数据了

79. [db2inst2@localhost ~]$ db2 "select * from administrator.aaa"

80.

81. AAA

82. -----------

83.         123

84.

85.   1 record(s) selected.

86.

87. --到此为止，基本完成

--使用db2look.sql ddl文件导入数据库结构

[db2inst2@localhost tmp]$ db2 -tvf db2look.sql;

DB21007E  End of file reached while reading the command.

--报出DB21007E错误，这个是本次实验当中唯一一个问题

--原因在于

[db2inst2@localhost tmp]$ file db2look.sql

db2look.sql: ASCII English text, with CRLF line terminators

--使用dos2unix进行格式转换

[db2inst2@localhost tmp]$ dos2unix db2look.sql

dos2unix: converting file db2look.sql to UNIX format ...

dos2unix: problems renaming './d2utmpMZpuAR' to 'db2look.sql'

output file remains in './d2utmpMZpuAR'

dos2unix: problems converting file db2look.sql

--再检查格式，ok

[db2inst2@localhost tmp]$ file d2utmpMZpuAR

d2utmpMZpuAR: ASCII English text

--导入ddl

[db2inst2@localhost tmp]$ db2 -tvf ./d2utmpMZpuAR

CONNECT TO SAMPLE

Database Connection Information

Database server        = DB2/LINUX 9.7.2

SQL authorization ID   = DB2INST2

Local database alias   = SAMPLE

CREATE SEQUENCE "INST00  "."SAMPSEQUENCE" AS BIGINT MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 INCREMENT BY 1 CACHE 20 NO CYCLE NO ORDER

DB20000I  The SQL command completed successfully.

--成功了，检查一下测试表

[db2inst2@localhost ~]$ db2 describe table administrator.aaa

Data type                     Column

Column name                     schema    Data type name      Length     Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

AAA                             SYSIBM    INTEGER                      4     0 Yes

1 record(s) selected.

--现在是没有数据的

[db2inst2@localhost ~]$ db2 "select * from administrator.aaa"

AAA

-----------

0 record(s) selected.

--使用db2move load导入ixf文件

[db2inst2@localhost db2move]$ db2move sample load

Application code page not determined, using ANSI codepage 1208

*****  DB2MOVE  *****

Action:  LOAD

Start time:  Sun Apr 24 08:07:57 2011

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

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

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

* LOAD:  table "ADMINISTRATOR"."AAA"

-Rows read:          1

-Loaded:             1

-Rejected:           0

-Deleted:            0

-Committed:          1

--再检查测试表，有数据了

[db2inst2@localhost ~]$ db2 "select * from administrator.aaa"

AAA

-----------

123

1 record(s) selected.

--到此为止，基本完成

小结：

1.总的来说，这个是标准的跨平台迁移，相对问题不多，如file format这些小问题更多是OS层面的基本功

2.真正的迁移难点在于在新平台当中参数的设置、表空间、容器、日志等设置，稍后再做一个更全面的

我是在windows下面用db2look -d bdcc60 -e -a -x -i db2admin -w bdcc -o createdb.sql命令导出数据的表结构，

然后用db2move bdcc60 export -u db2admin -p bdcc命令导出表数据，

然后将导出的表结构和数据cp到linux /usr/db2data/0521的文件夹下面，

进入该文件夹后，运行db2 -tvf createdb.sql命令导入表结构，现在表结构导入成功了，

但是在运行db2move bdcc60 load时，出现如下提示：

[db2admin@localhost 0521]$ db2move bdcc60 load

Error opening report file.  Terminating ...

**Error occured while opening a file.

End time:  Fri May 23 09:25:27 2008