mysql迁移到db2实践记录

现有系统struts2+spring3+myibatis+mysql

现在要迁移到db2,mysql有好多的sql和db2不兼容，需要改sql。

1、数据迁移

用jpa在per*.xml中配置两个数据源，eclipse新建jpa实体，插入db2，生成数据库的表，然后再导入数据到db2。

注意有关键字，例如,type,path等。

2、sql改造

把分页的sql改成db2的sql，例子如入：

 SELECT loan.*,biddingAmount/amount as schedule FROM loan WHERE ( loan.STATUS=300 OR loan.STATUS=400 OR loan.STATUS=500) AND openTime IS NOT NULL  AND (values DAYS(current timestamp)-DAYS(openTime))<8  ORDER BY schedule ASC, loan.status ASC, loan.loanId DESC limit 1,10

改为

SELECT * FROM (  SELECT ROWNUMBER() OVER() AS RN, loan.*,biddingAmount/amount as schedule FROM loan WHERE ( loan.STATUS=300 OR loan.STATUS=400 OR loan.STATUS=500) AND openTime IS NOT NULL  AND (values DAYS(current timestamp)-DAYS(openTime))<8  ORDER BY schedule ASC, loan.status ASC, loan.loanId DESC ) AS A WHERE A.RN>=1 AND A.RN<=10

把mysql的now()函数改为(VALUES CURRENT TIMESTAMP)

日期加几天，主要用于登陆失败10次后锁定账户：

UPDATE user_login_failure SET lastLocked=DATE_ADD(NOW(), INTERVAL #{expr} DAY) WHERE userId=#{userId}

改为：

UPDATE user_login_failure SET lastLocked=(values current timestamp +#{expr} DAYS) WHERE userId=#{userId}

 

UPDATE user_login_failure SET lastLocked=(values current timestamp +#{expr} DAYS) WHERE userId=#{userId}

 

DATE_ADD(NOW(), INTERVAL #{expr} DAY)

 

3、db2导入导出

 

db2look -d mgmt -o d:\mgmt.sql -i username -w pwd

4、db2中删除表的字段后执行，否者不能更新数据

 runstats on table administrator.loan_investor;
reorg table administrator.loan_investor;

5、SQL 增加或删除一列 

       alter table loan_investor add payFlowId varchar(50) NULL;

alter table tablename drop column columnname;————删除一列

6、mybatis多条数据返回类型是一个汇报如下错误

 nested exception is java.rmi.UnmarshalException: Error unmarshaling return; nested exception is: