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 ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------AAA SYSIBM INTEGER 4 0 Yes1 record(s) selected.C:\DOCUME~1\ADMINI~1\db2move>db2 "select * from aaa"AAA----------- 1231 record(s) selected.--使用db2move导出数据C:\DOCUME~1\ADMINI~1\db2move>db2move sample export -u inst00 -p ibm2blueApplication code page not determined, using ANSI codepage 1252*****DB2MOVE*****Action:EXPORTStart time:Tue May 24 03:42:45 2011Connecting to database SAMPLE ... successful!Server : DB2 Common Server V9.7.0EXPORT: 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\db2move05/24/201103:42 AM <DIR> .05/24/201103:42 AM <DIR> ..05/24/201103:42 AM 1,158 db2move.lst05/24/201103:42 AM 1,669 EXPORT.out05/24/201103:42 AM 2,599 tab1.ixf05/24/201103:42 AM 145 tab1.msg05/24/201103:42 AM 5,926 tab10.ixf05/24/201103:42 AM 146 tab10.msg05/24/201103:42 AM 387,034 tab10a.001.lob05/24/201103:42 AM 5,909 tab11.ixf05/24/201103:42 AM 146 tab11.msg05/24/201103:42 AM 15,396 tab11a.001.lob05/24/201103:42 AM 105,147 tab12.ixf05/24/201103:42 AM 148 tab12.msg05/24/201103:42 AM 10,722 tab13.ixf05/24/201103:42 AM 146 tab13.msg05/24/201103:42 AM 5,583 tab14.ixf05/24/201103:42 AM 146 tab14.msg05/24/201103:42 AM 6,769 tab15.ixf05/24/201103:42 AM 146 tab15.msg05/24/201103:42 AM 6,602 tab16.ixf05/24/201103:42 AM 146 tab16.msg05/24/201103:42 AM 8,019 tab17.ixf05/24/201103:42 AM 146 tab17.msg05/24/201103:42 AM 5,986 tab17a.001.lob05/24/201103:42 AM 12,034 tab18.ixf05/24/201103:42 AM 358 tab18.msg05/24/201103:42 AM 1,073 tab18a.001.xml05/24/201103:42 AM 3,519 tab19.ixf05/24/201103:42 AM 146 tab19.msg05/24/201103:42 AM 7,208 tab2.ixf05/24/201103:42 AM 146 tab2.msg05/24/201103:42 AM 9,731 tab20.ixf05/24/201103:42 AM 147 tab20.msg05/24/201103:42 AM 12,710 tab21.ixf05/24/201103:42 AM 147 tab21.msg05/24/201103:42 AM 12,424 tab22.ixf05/24/201103:42 AM 358 tab22.msg05/24/201103:42 AM 2,464 tab22a.001.xml05/24/201103:42 AM 8,079 tab23.ixf05/24/201103:42 AM 147 tab23.msg05/24/201103:42 AM 9,763 tab24.ixf05/24/201103:42 AM 147 tab24.msg05/24/201103:42 AM 10,312 tab25.ixf05/24/201103:42 AM 147 tab25.msg05/24/201103:42 AM 4,870 tab26.ixf05/24/201103:42 AM 358 tab26.msg05/24/201103:42 AM 562 tab26a.001.xml05/24/201103:42 AM 4,717 tab3.ixf05/24/201103:42 AM 357 tab3.msg05/24/201103:42 AM 5,448 tab4.ixf05/24/201103:42 AM 145 tab4.msg05/24/201103:42 AM 10,480 tab5.ixf05/24/201103:42 AM 357 tab5.msg05/24/201103:42 AM 2,087 tab5a.001.xml05/24/201103:42 AM 10,243 tab6.ixf05/24/201103:42 AM 146 tab6.msg05/24/201103:42 AM 21,424 tab7.ixf05/24/201103:42 AM 146 tab7.msg05/24/201103:42 AM 324,507 tab8.ixf05/24/201103:42 AM 361 tab8.msg05/24/201103:42 AM 11,130 tab9.ixf05/24/201103:42 AM 146 tab9.msg 61 File(s) 1,058,378 bytes 2 Dir(s)35,149,488,128 bytes free--使用db2look导出ddlC:\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: IDENTITYCONNECT TO SAMPLE;----------------------------------- DDL Statements for Sequences---------------------------------CREATE SEQUENCE "INST00"."SAMPSEQUENCE" AS BIGINTMINVALUE 1 MAXVALUE 9223372036854775807START WITH 1 INCREMENT BY 1CACHE 20 NO CYCLE NO ORDER;
把以上所有相关文件传到linux之后，开始导入数据库结构和数据
--使用db2look.sql ddl文件导入数据库结构$ db2 -tvf db2look.sql;DB21007EEnd of file reached while reading the command.--报出DB21007E错误，这个是本次实验当中唯一一个问题--原因在于$ file db2look.sql db2look.sql: ASCII English text, with CRLF line terminators--使用dos2unix进行格式转换$ 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$ file d2utmpMZpuARd2utmpMZpuAR: ASCII English text--导入ddl$ db2 -tvf ./d2utmpMZpuARCONNECT TO SAMPLE Database Connection Information Database server = DB2/LINUX 9.7.2 SQL authorization ID = DB2INST2 Local database alias = SAMPLECREATE SEQUENCE "INST00"."SAMPSEQUENCE" AS BIGINT MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 INCREMENT BY 1 CACHE 20 NO CYCLE NO ORDERDB20000IThe SQL command completed successfully.--成功了，检查一下测试表$ db2 describe table administrator.aaa Data type ColumnColumn name schema Data type name Length Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------AAA SYSIBM INTEGER 4 0 Yes 1 record(s) selected.--现在是没有数据的$ db2 "select * from administrator.aaa"AAA -----------0 record(s) selected.--使用db2move load导入ixf文件$ db2move sample loadApplication code page not determined, using ANSI codepage 1208*****DB2MOVE*****Action:LOADStart time:Sun Apr 24 08:07:57 2011Connecting to database SAMPLE ... successful!Server : DB2 Common Server V9.7.2Binding 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--再检查测试表，有数据了$ db2 "select * from administrator.aaa"AAA ----------- 1231 record(s) selected.--到此为止，基本完成
小结：
1.总的来说，这个是标准的跨平台迁移，相对问题不多，如file format这些小问题更多是OS层面的基本功
2.真正的迁移难点在于在新平台当中参数的设置、表空间、容器、日志等设置，稍后再做一个更全面的
页:
[1]