笔者通过从事数据库的开发和管理工作数年!前不久刚刚完成SYBASE 数据库向db2数据库的迁移项目工作!
应网友和一些同学朋友的要求!要我介绍一些数据库迁移的策略和方法!
目前见于网上数据库迁移的资料较少!我实际工作中的遇到的技术难点和解决的策略和方法,简单介绍!希望对想要迁移数据库,又不知道如何下手的朋友有所借鉴!本文列出51个实际中问题和解决的策略和方法!非常注重实际!
ASs=MsoNormal>1AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.SYBASEAScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">数据类型和db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">有那些不同?
ASs=MsoNormal>答:Datetime 对应 timestamp AS-microsoft-com:office:office" />
ASs=MsoNormal>Tinyint ,smallint 对应 smallint
ASs=MsoNormal>Money 类型对应 numeric(19,4)
ASs=MsoNormal>
ASs=MsoNormal>2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">数据库是否支持自增加列设置?
ASs=MsoNormal>答:支持,例如:SYSID BIGINT GENERATED ALWAYS AS IDENTITY
ASs=MsoNormal>将原来sysid 的 numerical(18,0) 改为 BIGINT类型
ASs=MsoNormal>
ASs=MsoNormal>3AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">是否有convert()AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">函数,SYBASE AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">下这样语句如何转换?
ASs=MsoNormal>答:convert(datetime,convert(char(8),dateadd(day,-1,getdate()),112))
ASs=MsoNormal>db2下没有convert()函数,关于转换可以使用如下函数
ASs=MsoNormal>char(),timestamp(),date(),time() …另外可以使用系统内部的系统变量
ASs=MsoNormal>current timestamp , current date , current time 代表系统当前日期时间
ASs=MsoNormal>如上语句可以这样转换
ASs=MsoNormal>timestamp( current date –1 days,time(’00.00.00’))
ASs=MsoNormal>4AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.SYBASEAScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">的isnull(@vc_pici_id,’0’) AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">如何转换?
ASt-language: ZH-CN; mso-bidi-language: AR-SA; mso-bidi-font-family: 'Times New Roman'">答:db2下使用value(vc_pici_id,’0’)或者coalesce(vc_pici_id,’0’)
ASt-language: ZH-CN; mso-bidi-language: AR-SA; mso-bidi-font-family: 'Times New Roman'">ASt-language: ZH-CN; mso-bidi-language: AR-SA; mso-fareASt-font-family: 宋体">
ASs=MsoNormal>5AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">下如何调用存储过程和调用代返回值得存储过程?
ASs=MsoNormal>答: (1)无返回参数调用CALL proc1(v_empno, v_salary)
ASs=MsoNormal>(2)有返回参数调用
ASs=MsoNormal>declare ret_var integer default 0;
ASs=MsoNormal>CALL proc1(var1,var2) ;
ASs=MsoNormal>Get Diagnostics ret_var = RETURN_STATUS;
ASs=MsoNormal>---------
ASs=MsoNormal>declare err_code integer default 0;
ASs=MsoNormal>call proc1(var1,err_code);
ASs=MsoNormal>IF ERR_CODE = 1 THEN
ASs=MsoNormal>XXXX;
ASs=MsoNormal>END IF;
ASs=MsoNormal>6AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.SYBASE AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">下游标控制是非常方便的,使用sqlcode,sqlstateAScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">来控制,不知道db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">下游标是如何控制的?
ASs=MsoNormal>答:db2下游标控制不是非常的轻松和方便的,同样也可以使用sqlcode,sqlstate,或者用户自己控制,db2下SQLCODE,SQLSTATE不能直接使用,必须声明后使用,(也就是说将系统的SQLCODE,SQLSTATE本地实例化一分拷贝)。一般采用用户定义游标开关和sqlcode返回信息一起共同控制的方法.
ASs=MsoNormal>举例1:(这里说明一个问题,游标开关是和SQLCODE捆绑的。‘02000’就是SQLCODE号)
ASs=MsoNormal>//-------
ASs=MsoNormal>标准使用游标的例子
ASs=MsoNormal>标准WHILE DO 控制游标
ASs=MsoNormal>//-------
ASs=MsoNormal>
ASs=MsoNormal>
ASs=MsoNormal>CREATE PROCEDURE CREDITP
ASs=MsoNormal>(IN i_perinc DECIMAL(3,2),
ASs=MsoNormal>INOUT o_numrec DECIMAL(5,0))
ASs=MsoNormal>LANGUAGE SQL
ASs=MsoNormal>BEGIN -- 这里是用户管理事务
ASs=MsoNormal>DECLARE proc_cusnbr CHAR(5);
ASs=MsoNormal>DECLARE proc_cuscrd DECIMAL(11,2);
ASs=MsoNormal>DECLARE numrec DECIMAL(5,0);
ASs=MsoNormal>DECLARE at_end INT DEFAULT 0; -- 开关定义
ASs=MsoNormal>DECLARE not_found CONDITION FOR '02000'; -- 没有数据,游标结尾定义
ASs=MsoNormal>DECLARE c1 CURSOR FOR SELECT cusnbr, cuscrd FROM ordapplib.customer;
ASs=MsoNormal>
ASs=MsoNormal>DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; --定义CONTINUE 条件
ASs=MsoNormal>
ASs=MsoNormal>DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK ; --sqlcode 非'01''00''02'则退出并回滚事务
ASs=MsoNormal>SET numrec = 0;
ASs=MsoNormal>
ASs=MsoNormal>OPEN c1;
ASs=MsoNormal> FETCH c1 INTO proc_cusnbr, proc_cuscrd;
ASs=MsoNormal>WHILE at_end = 0 DO
ASs=MsoNormal> SET proc_cuscrd = proc_cuscrd +(proc_cuscrd * i_perinc);
ASs=MsoNormal> SET numrec = numrec + 1;
ASs=MsoNormal> FETCH c1 INTO proc_cusnbr, proc_cuscrd;
ASs=MsoNormal>END WHILE;
ASs=MsoNormal>SET o_numrec = numrec;
ASs=MsoNormal>CLOSE c1;
ASs=MsoNormal>COMMIT; --提交事务
ASs=MsoNormal>END
ASs=MsoNormal>
ASs=MsoNormal>举例2:
ASs=MsoNormal>--声明游标C1
ASs=MsoNormal>DECLARE c1 CURSOR FOR
ASs=MsoNormal>SELECT cusnbr, cuscrd
ASs=MsoNormal>FROM ordapplib.customer;
ASs=MsoNormal>
ASs=MsoNormal>OPEN c1; --打开游标
ASs=MsoNormal>FETCH c1 INTO proc_cusnbr, proc_cuscrd; --从游标获取数据
ASs=MsoNormal>IF SQLSTATE = '02000' THEN --判断游标是否有数据(无)
ASs=MsoNormal>CALL DATA_NOT_FOUND; --返回调用
ASs=MsoNormal>ELSE
ASs=MsoNormal>DO WHILE (SUBSTR(SQLSTATE,1,2) = '00' | SUBSTR(SQLSTATE,1,2) = '01');
ASs=MsoNormal>FETCH c1 INTO proc_cusnbr, proc_cuscrd;
ASs=MsoNormal>......
ASs=MsoNormal>
ASs=MsoNormal>END IF ;
ASs=MsoNormal>CLOSE c1; --关闭游标
ASs=MsoNormal>
ASs=MsoNormal>7AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">存储过程的标准格式如何?
ASs=MsoNormal>答:db2存储过程的编写比较灵活,但是语法格式是非常严格的。基本如下
ASs=MsoNormal> CTEATE PROCEDURE XHZQ_db.PROCNAME (
ASs=MsoNormal>IN PARA_1 TYPE DEFAULT VALUE ,
ASs=MsoNormal>OUT PARA_2 TYPE DEFAULT VALUE)
ASs=MsoNormal>BEGIN ( 用户管理事务)
ASs=MsoNormal>--用户定义变量
ASs=MsoNormal>DECLARE VAR1 TYPE DEFAULT VALUE;
ASs=MsoNormal>--用户定义控制临时变量
ASs=MsoNormal>DECLARE ERROR_CODE INT DEFAULT 0;
ASs=MsoNormal>DECLARE AT_END INT DEFAULT 0;
ASs=MsoNormal>-- 用户定义条件控制变量
ASs=MsoNormal>DECLARE not_found CONDITION FOR '02000';
ASs=MsoNormal>-- 用户定义游标
ASs=MsoNormal>DECLARE C1 CURSOR FOR SELECT COL FROM TABLE_NAME ;
ASs=MsoNormal>
ASs=MsoNormal>-- 用户定义条件和开关变量关联
ASs=MsoNormal>DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
ASs=MsoNormal>-- 用户定义事务回滚处理条件
ASs=MsoNormal>DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK ;
ASs=MsoNormal>-- 初始化变量直
ASs=MsoNormal>SET VAR = 0;
ASs=MsoNormal>-- 判断入口参数
ASs=MsoNormal>IF (PARA_1 IS NULL) THEN
ASs=MsoNormal>SET PARA = 100;
ASs=MsoNormal>END IF;
ASs=MsoNormal>
ASs=MsoNormal>-- 存储过程语句体集合
ASs=MsoNormal>OPEN C1 ;
ASs=MsoNormal>FETCH C1 INTO XX,XX2,XX3 ;
ASs=MsoNormal>….
ASs=MsoNormal>CLOSE C1;
ASs=MsoNormal>-- 提交事务
ASs=MsoNormal>COMMIT WORK HOLD;
ASs=MsoNormal>SET PARA_2 = 0 ;
ASs=MsoNormal>RETURN PARA_2 ;
ASs=MsoNormal>-- 返回参数0 成功
ASs=MsoNormal>ASt-language: ZH-CN; mso-bidi-language: AR-SA; mso-bidi-font-family: 'Times New Roman'">END
ASs=MsoNormal>ASt-language: ZH-CN; mso-bidi-language: AR-SA; mso-bidi-font-family: 'Times New Roman'">
ASs=MsoNormal>8AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">下char()AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">函数用法
ASs=MsoNormal>答: CHAR(DATE,Keyword) , CHAR(TIME,Keyword)
ASs=MsoNormal>
ASs=MsoNormal>Keyword
ASs=MsoNormal>Date Format
ASs=MsoNormal>Time Format
ASs=MsoNormal>USA
ASs=MsoNormal>07/17/2001
ASs=MsoNormal>01:48PM
ASs=MsoNormal>ISO
ASs=MsoNormal>2001-07-17
ASs=MsoNormal>13.48.04
ASs=MsoNormal>EUR
ASs=MsoNormal>17.07.2001
ASs=MsoNormal>13.48.04
ASs=MsoNormal>JIS
ASs=MsoNormal>2001-07-17
ASs=MsoNormal>13.48.04
ASs=MsoNormal>Select CURRENT DATE,CURRENT TIME From SYSIBM>SYSDUMMY1;
ASs=MsoNormal>
ASs=MsoNormal>
ASs=MsoNormal>9AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">下timestamp()AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">函数用法
ASs=MsoNormal>答:TIMESTAMPDIFF(K,CHAR(TS1 – TS2))
ASs=MsoNormal>
ASs=MsoNormal>K
ASs=MsoNormal>Date Part
ASs=MsoNormal>K
ASs=MsoNormal>Date Part
ASs=MsoNormal>256
ASs=MsoNormal>Years
ASs=MsoNormal>128
ASs=MsoNormal>Quarters
ASs=MsoNormal>64
ASs=MsoNormal>Months
ASs=MsoNormal>32
ASs=MsoNormal>Weeks
ASs=MsoNormal>16
ASs=MsoNormal>Days
ASs=MsoNormal>8
ASs=MsoNormal>Hours
ASs=MsoNormal>4
ASs=MsoNormal>Minutes
ASs=MsoNormal>2
ASs=MsoNormal>Seconds
ASs=MsoNormal>
ASs=MsoNormal>10AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">.db2AScii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">下如何进行字符串组合拼接?
ASs=MsoNormal>答:使用‘||’处理,concat()函数
ASs=MsoNormal> 例如: DECLARE VC_SQL_STR VCHAR(100);
ASs=MsoNormal> DECLARE VC_WHERE VCHAR(100);
ASs=MsoNormal> DECLARE VC_FROM VCHAR(100);
ASs=MsoNormal> SET VC_FROM = ‘ FROM XHZQ_db.SYS_PARAMETER_TAB ‘;
ASs=MsoNormal> SET VC_WHERE = ‘ WHERE VC_ID IS NULL ‘;
ASs=MsoNormal> SET VC_SQL_STR = ‘ SELECT * ‘ || VC_FROM || VC_WHERE ;
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>