migrate from win to linux(遇到的问题)

之前写过一篇在自己虚拟机简单测试的步骤
http://guoyanxi.iteye.com/blog/1056773

当然其中都是非常简单和理想化的环境，这篇文章打算记录其中遇到的问题

如果有机会应该还会写一篇总结出来，方便以后查阅

问题1.文本文件格式

--简单版当中写过如何处理，这里再复述一下

--使用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

至于去M字符，可以看
http://guoyanxi.iteye.com/blog/1091512


问题2.SQL0670N , SQL0204N 表空间不一致问题

SQL0670N 是表空间page size不一致

--默认create database是使用4K的大小

$ db2pd -alldbs -tablespace

Database Partition 0 -- Database DELPHI2 -- Active -- Up 0 days 00:00:08 -- Date 06/14/2011 09:42:36

Tablespace Configuration:
Address            Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x070000004169B4A0 0     DMS  Regular 4096   4        Yes  4        1     1         Off 1        0          3            SYSCATSPACE
0x070000004169CC00 1     SMS  SysTmp  4096   32       Yes  32       1     1         On  1        0          31           TEMPSPACE1
0x07000000416A0340 2     DMS  Large   4096   32       Yes  32       1     1         Off 1        0          31           USERSPACE1

--可以在建立的时候指定页面大小，注意数字后有一个空格

db2 => create database delphi2 on /db/a1inlean/db2data/DELPHI2 pagesize 16 k

--这时候page size 应该就是16K了，同时，以后建立新的表空间的时候，默认也是使用这个新的page size

$ db2pd -alldbs -tablespace

Database Partition 0 -- Database DELPHI2 -- Active -- Up 0 days 00:02:06 -- Date 06/14/2011 09:58:36

Tablespace Configuration:
Address            Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x070000005169BD20 0     DMS  Regular 16384  4        Yes  4        1     1         Off 1        0          3            SYSCATSPACE
0x070000005169D480 1     SMS  SysTmp  16384  32       Yes  32       1     1         On  1        0          31           TEMPSPACE1
0x07000000516A0BC0 2     DMS  Large   16384  32       Yes  32       1     1         Off 1        0          31           USERSPACE1



--不指定page size建立新的表空间
$ db2 create user TEMPORARY tablespace TEMPSPACE pagesize
DB20000I  The SQL command completed successfully.

$ db2pd -alldbs -tablespace

Database Partition 0 -- Database DELPHI2 -- Active -- Up 0 days 00:06:21 -- Date 06/14/2011 10:11:15

Tablespace Configuration:
Address            Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x070000005169BD20 0     DMS  Regular 16384  4        Yes  4        1     1         Off 1        0          3            SYSCATSPACE
0x070000005169D480 1     SMS  SysTmp  16384  32       Yes  32       1     1         On  1        0          31           TEMPSPACE1
0x07000000516A0BC0 2     DMS  Large   16384  32       Yes  32       1     1         Off 1        0          31           USERSPACE1
0x0700000054696F80 3     DMS  Large   16384  4        Yes  4        1     1         Off 1        0          3            SYSTOOLSPACE
0x0700000054685160 4     SMS  UsrTmp  16384  32       Yes  32       1     1         On  1        0          31           TEMPSPACE

SQL0204N  "<name>" is an undefined name.
这里是指指定的表空间不存在

按照原数据库建立一模一样的表空间就好了


问题3.SQL0668N , 导入后数据完整性问题

db2 => select count(*) from "AKATARIYA"."T_NODE_EVENT"

1          
-----------
SQL0668N  Operation not allowed for reason code "1" on table 
"AKATARIYA.T_NODE_EVENT".  SQLSTATE=57016
db2 => ? 57016

SQLSTATE 57016: The table cannot be accessed, because it is inactive.

--是因为db2move其实是使用load进行数据载入，所以需要set integrity

--使用这个查询确定哪些表需要处理
db2 => Select TABSCHEMA,tabname from syscat.tables where status='C' 

TABSCHEMA                                                                                                                        TABNAME                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AKATARIYA                                                                                                                        T_SCHEMA_PATCH                                                                                                                  
AKATARIYA                                                                                                                        T_USER                                                                                                                          
AKATARIYA                                                                                                                        T_USER_PROFILE                                                                                                                  
AKATARIYA                                                                                                                        T_GROUP            

--可以逐一进行set integrity
db2 => set integrity for AKATARIYA.T_GROUP IMMEDIATE CHECKED

但是有的时候时候会遇到SQL3608N

SQL3608N  Cannot check a dependent table "AKATARIYA.T_PERMISSION_ACTOR_MAP"
using the SET INTEGRITY statement while the parent table or underlying table
"AKATARIYA.T_ACTOR" is in the Set Integrity Pending state or if it will be put
into the Set Integrity Pending state by the SET INTEGRITY statement. 
SQLSTATE=428A8

当然可以按照顺序逐一处理，也可以使用下面的方法偷懒

db2 => set integrity for AKATARIYA.T_FIELD_TYPE ALL IMMEDIATE UNCHECKED  

DB20000I  The SQL command completed successfully.

但是当然会存在约束冲突的风险

不过这里是从原有数据库导入全新的数据库（空的），问题不大