现有系统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:
京公网安备 11010802041100号