migrate from win to linux(简单版)

本文讲述最简单的从windows到linux的db2迁移
1.跳过实例、数据库等参数设置
2.数据库版本版本基本相同
3.使用简单的sample数据库

马上有个重要的项目就是从windows到AIX，做点小准备，后续的工作还有很多。

原数据库：
OS:windows xp
DB2: db2 9.7
database name:sample

目标数据库：
OS:RHEL 5
DB2: db2 9.7
database name:sample

首先导出原数据库数据和数据库结构ddl

--建立测试表
C:\DOCUME~1\ADMINI~1\db2move>db2 describe table aaa

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale N
ulls
------------------------------- --------- ------------------- ---------- ----- -
-----
AAA                             SYSIBM    INTEGER                      4     0 Y
es

  1 record(s) selected.

C:\DOCUME~1\ADMINI~1\db2move>db2 "select * from aaa"

AAA
-----------
        123

  1 record(s) selected.

--使用db2move导出数据
C:\DOCUME~1\ADMINI~1\db2move>db2move sample export -u inst00 -p ibm2blue

Application code page not determined, using ANSI codepage 1252

*****  DB2MOVE  *****

Action:  EXPORT

Start time:  Tue May 24 03:42:45 2011


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


EXPORT:      1 rows from table "ADMINISTRATOR"."AAA"
EXPORT:     18 rows from table "INST00  "."ACT"
EXPORT:      0 rows from table "INST00  "."CATALOG"
EXPORT:      5 rows from table "INST00  "."CL_SCHED"
EXPORT:      6 rows from table "INST00  "."CUSTOMER"
EXPORT:     14 rows from table "INST00  "."DEPARTMENT"
EXPORT:     42 rows from table "INST00  "."EMPLOYEE"
EXPORT:  10000 rows from table "INST00  "."EMPMDC"
EXPORT:     73 rows from table "INST00  "."EMPPROJACT"
EXPORT:      8 rows from table "INST00  "."EMP_PHOTO"
EXPORT:      8 rows from table "INST00  "."EMP_RESUME"
EXPORT:    157 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT:      0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT:      4 rows from table "INST00  "."INVENTORY"
EXPORT:      3 rows from table "INST00  "."IN_TRAY"
EXPORT:      8 rows from table "INST00  "."ORG"
EXPORT:      5 rows from table "SYSTOOLS"."POLICY"
EXPORT:      4 rows from table "INST00  "."PRODUCT"
EXPORT:      2 rows from table "INST00  "."PRODUCTSUPPLIER"
EXPORT:     65 rows from table "INST00  "."PROJACT"
EXPORT:     20 rows from table "INST00  "."PROJECT"
EXPORT:      6 rows from table "INST00  "."PURCHASEORDER"
EXPORT:     41 rows from table "INST00  "."SALES"
EXPORT:     35 rows from table "INST00  "."STAFF"
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文件当中
C:\DOCUME~1\ADMINI~1\db2move>dir
 Volume in drive C has no label.
 Volume Serial Number is 58AE-B492

 Directory of C:\DOCUME~1\ADMINI~1\db2move

05/24/2011  03:42 AM    <DIR>          .
05/24/2011  03:42 AM    <DIR>          ..
05/24/2011  03:42 AM             1,158 db2move.lst
05/24/2011  03:42 AM             1,669 EXPORT.out
05/24/2011  03:42 AM             2,599 tab1.ixf
05/24/2011  03:42 AM               145 tab1.msg
05/24/2011  03:42 AM             5,926 tab10.ixf
05/24/2011  03:42 AM               146 tab10.msg
05/24/2011  03:42 AM           387,034 tab10a.001.lob
05/24/2011  03:42 AM             5,909 tab11.ixf
05/24/2011  03:42 AM               146 tab11.msg
05/24/2011  03:42 AM            15,396 tab11a.001.lob
05/24/2011  03:42 AM           105,147 tab12.ixf
05/24/2011  03:42 AM               148 tab12.msg
05/24/2011  03:42 AM            10,722 tab13.ixf
05/24/2011  03:42 AM               146 tab13.msg
05/24/2011  03:42 AM             5,583 tab14.ixf
05/24/2011  03:42 AM               146 tab14.msg
05/24/2011  03:42 AM             6,769 tab15.ixf
05/24/2011  03:42 AM               146 tab15.msg
05/24/2011  03:42 AM             6,602 tab16.ixf
05/24/2011  03:42 AM               146 tab16.msg
05/24/2011  03:42 AM             8,019 tab17.ixf
05/24/2011  03:42 AM               146 tab17.msg
05/24/2011  03:42 AM             5,986 tab17a.001.lob
05/24/2011  03:42 AM            12,034 tab18.ixf
05/24/2011  03:42 AM               358 tab18.msg
05/24/2011  03:42 AM             1,073 tab18a.001.xml
05/24/2011  03:42 AM             3,519 tab19.ixf
05/24/2011  03:42 AM               146 tab19.msg
05/24/2011  03:42 AM             7,208 tab2.ixf
05/24/2011  03:42 AM               146 tab2.msg
05/24/2011  03:42 AM             9,731 tab20.ixf
05/24/2011  03:42 AM               147 tab20.msg
05/24/2011  03:42 AM            12,710 tab21.ixf
05/24/2011  03:42 AM               147 tab21.msg
05/24/2011  03:42 AM            12,424 tab22.ixf
05/24/2011  03:42 AM               358 tab22.msg
05/24/2011  03:42 AM             2,464 tab22a.001.xml
05/24/2011  03:42 AM             8,079 tab23.ixf
05/24/2011  03:42 AM               147 tab23.msg
05/24/2011  03:42 AM             9,763 tab24.ixf
05/24/2011  03:42 AM               147 tab24.msg
05/24/2011  03:42 AM            10,312 tab25.ixf
05/24/2011  03:42 AM               147 tab25.msg
05/24/2011  03:42 AM             4,870 tab26.ixf
05/24/2011  03:42 AM               358 tab26.msg
05/24/2011  03:42 AM               562 tab26a.001.xml
05/24/2011  03:42 AM             4,717 tab3.ixf
05/24/2011  03:42 AM               357 tab3.msg
05/24/2011  03:42 AM             5,448 tab4.ixf
05/24/2011  03:42 AM               145 tab4.msg
05/24/2011  03:42 AM            10,480 tab5.ixf
05/24/2011  03:42 AM               357 tab5.msg
05/24/2011  03:42 AM             2,087 tab5a.001.xml
05/24/2011  03:42 AM            10,243 tab6.ixf
05/24/2011  03:42 AM               146 tab6.msg
05/24/2011  03:42 AM            21,424 tab7.ixf
05/24/2011  03:42 AM               146 tab7.msg
05/24/2011  03:42 AM           324,507 tab8.ixf
05/24/2011  03:42 AM               361 tab8.msg
05/24/2011  03:42 AM            11,130 tab9.ixf
05/24/2011  03:42 AM               146 tab9.msg
              61 File(s)      1,058,378 bytes
               2 Dir(s)  35,149,488,128 bytes free

--使用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之后，开始导入数据库结构和数据

--使用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.真正的迁移难点在于在新平台当中参数的设置、表空间、容器、日志等设置，稍后再做一个更全面的