简单有效的SQL Stored Procedure移植方案

dn001 2009-01-23 13:07:48

本文将介绍一套简单有效的移植方案，指导从Sybase到DB2 V9 FOR Z/OS 的SQL Stored Procedure的移植，并且基于实际移植案例给出了一个具体示例。

背景介绍

随着企业业务的发展，数据量的海量增长，越来越多的企业采用了性能稳定而强大的 DB2 FOR Z/OS 作为数据库管理系统。如何将已有的应用程序移植到 DB2 FOR Z/OS 成为其中重要的一个环节。在实际的案例中，我们发现目前越来越多的应用程序将数据库业务处理部分尽可能的封装到 SQL Stored Procedure 中，这样不仅能得到了很好的模块化、重用性和性能优化，而且因未来业务需求的改动而带来的二次开发也将变得更快捷、更安全。

IBM 提供了强大而实用的 MTK 来帮助客户完成移植过程，但是目前 MTK 并不支持从 Sybase 到 DB2 FOR Z/OS 的移植。

移植方案的技术介绍

当数据库业务处理部分尽可能的封装到 SQL Stored Procedure 时， Stored Procedure 的数量往往会达到上百甚至上千个，而且单个 Stored Procedure 的代码量也可能达到上百或上千行，这时 SQL Stored Procedure 移植的工作量和难度将成为整个项目移植的关键部分。由于目前没有自动的移植工具，我们曾试验了先利用 MTK 将 SQL Stored Procedure 移植到 DB2 FOR LUW ，然后再移植到 DB2 FOR Z/OS ，但效果不是很理想，所以我们的方案采用的是人工直接修改的策略。

由于参与移植的人员往往不全是原有程序的开发人员，对两个数据库管理系统也可能不是很熟悉，所以如何提高人工修改的效率和技术要求成为移植的关键问题。我们的方案将分三步走。

第一阶段中，挑选具有典型代表意义的 SQL Stored Procedure ，进行研究、移植试验。

Sybase 和 DB2 在 SQL Stored Procedure 的语法和使用习惯上有很多细微的差异，目前还没有一个完整的差异对照表，并且具体业务的不同以及开发人员编程习惯的不同，往往使得我们需要具体问题具体分析。

但是在同一个业务模块中的Stored Procedure往往具有相似性。所以在第一阶段，我们需要挑选各个业务模块中典型的 Stored Procedure ，由技术骨干来进行研究、试验。在我们后面的案例中，大约 5％左右的 Stored Procedure 即包含了所有 Stored Procedure 中的 95% 以上的移植点。我们需要对这些 Stored Procedure 逐个通读代码，发现移植点，研究出等价修改的方法。我们可以使用的调试工具有 Workbench ，文本编辑器等。

第二个阶段中，形成移植手册并加以优化以方便批量修改。

我们需要整理第一阶段中的研究成果，将移植点及其移植方法加以分类整理。移植手册的编制目标是尽可能地使得后期的同事可以根据其中的移植详细步骤将一个 Sybase 的 SQL Stored Procedure 简单地移植到 DB2 中，降低他们对业务和数据库技术的要求。移植手册应该包含绝大部分移植点，完成后最好请其他的同事使用若干第一阶段中未选中 Stored Procedure 加以验证，这样可以发现其中的不足，加以改进优化，并且能够评估后期的工作量。

移植手册可以视具体情况包含多个部分，比如系统环境说明，移植详细步骤，移植点详细解答，移植样例等等，其中移植详细步骤和移植点详细解答是核心。移植详细步骤列举了移植所需要做的修改操作及其顺序。移植点详细解答列举了移植点的上下文、技术详细解释、等价修改及其注意点，是用来更好地理解、补充移植详细步骤的。

第三个阶段中，组织人员根据移植手册进行真正的移植工作。

对于在移植手册中未涵盖的差异，在此期间具体问题具体解决。等全部 Stored Procedure 移植完成后，需要进行完整的功能测试以及必要的性能测试。

移植方案介绍

移植手册是移植方案的核心，其中移植详细步骤和移植点详细解答是关键。限于篇幅，我们这里只是举例了移植详细步骤。

注意：该系统采用 CCSID ASCII ，同时为了信息安全，我们把实例中出现的变量名、列名、表名等有含义的名称统一用 V_n，C_n，T_n (n=1,2,3,4….) 加以替换。

移植准备工作

修改工具： UltraEdit 文本编辑器

方法1：全局替换。查找关键词，用替换词替换即可。

方法2：全局查找，逐个确认替换。查找关键词，确认情景是否符合，然后用替换词替换。

方法3：全局查找，逐个确认，手工修改。查找关键词，确认情景是否符合，根据具体情景修改。

说明：示例代码中“ Sybase 代码”部分是修改前的代码，“ DB2 代码”部分是修改后的代码。

移植详细步骤

第一步：常见替换

方法：方法1，全局替换。

查找关键词：SUBSTRING(

替换词：SUBSTR(

查找关键词：len(

替换词：length(

查找关键词：char_length(

替换词：length(

查找关键词：DATALENGTH(

替换词：length(

查找关键词：ISNULL(

替换词：IFNULL(

查找关键词：+' //表示两个字符串的连接

替换词：||'

查找关键词：!=

替换词：<>

查找关键词： <> NULL

替换词：IS NOT NULL

第二步：修改 Stored Procedure 定义的开头,可以制作成统一的模板。

修改点：

◆把函数说明 ‘/**/’多行注释，用‘--’进行单行注释（ DB2 不支持多行注释）。

◆去掉 schema name: ‘dbo.’

◆传入参数处添加‘( )’

◆传入参数处添加IN 关键词,并修改IN,OUT的位置

◆去掉关键词‘AS’，换成 Stored Procedure 的参数选项

◆以BEGIN作为函数体的开始，把BEGIN移到DECLARE前。

◆变量定义增加初始值及CCSID ASCII关键字。

清单1. Stored Procedure 定义的开头修改示例

Sybase代码:
CREATE PROCEDURE dbo.P_1 
@p_1	VARCHAR(9),
@p_2  	VARCHAR(256) 	OUT
AS
DECLARE	@v_1     		VARCHAR(50)   
DECLARE	@v_2     		VARCHAR(1024)
BEGIN

DB2代码:
CREATE PROCEDURE Pr_ChkExPreCorp (IN p_1	VARCHAR(9),
OUT p_2  	VARCHAR(256))
LANGUAGE SQL
MODIFIES SQL DATA
WLM ENVIRONMENT FOR DEBUG MODE WLMENV1
ASUTIME NO LIMIT
NOT DETERMINISTIC
COMMIT ON RETURN NO
PARAMETER CCSID ASCII
PACKAGE OWNER TEST
QUALIFIER TEST
RESULT SETS 1
BEGIN
DECLARE	v_1  VARCHAR(50) CCSID ASCII DEFAULT ’’;
DECLARE	v_2  VARCHAR(100) CCSID ASCII DEFAULT ’’;

第三步：赋值语句的修改

方法：方法1，全局替换。

查找关键词：SELECT @

替换词：SET //注意：SET 后面带一个空格

清单2 ：赋值语句的修改

Sybase 代码:
SELECT @v_1='0'

DB2 代码:
SET v_1 ='0';

注意：该处使用全局替换可能错误地替换一些地方，比如语句

select @aaa=xxxx, @bbb = yyyy from …… where ……

但是情况不多，可以在 Deploy Stored Procedure 的时候发现错误并改回来。

第四步：全局变量 @@sqlstatus 和 WHILE 语句的改写

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：@@sqlstatus = 0 , WHILE

修改点：

◆去掉 BEGIN, 添加 DO；

◆END 改为 END WHILE；

◆@@sqlstatus = 0 替换成 v_sqlcode = 0。//注意：v_sqlcode 的定义见后面HANDLER的定义；

◆在每个‘FETCH C1 INTO …’之前添加‘SET v_sqlcode =0’。

清单 3 ：全局变量 @@sqlstatus 和 WHILE 语句的改写

Sybase 代码:
OPEN C1
FETCH C1 INTO ……
WHILE @@sqlstatus = 0
BEGIN
… …
FETCH C1 INTO ……
END

DB2 代码：
OPEN C1;
set v_sqlcode = 0;
FETCH C1 INTO ……
WHILE v_sqlcode = 0 DO
… …
set v_sqlcode = 0;
FETCH C1 INTO ……
END WHILE;

第五步：全局变量 @@ERROR 的改写

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：@@ERROR

修改点：

(1)@@ERROR修改

清单4：@@ERROR修改

Sybase代码：
SELECT @v_errcode = CONVERT(VARCHAR(10),@@ERROR)	
IF ERROR <> 0
GOTO ERROR

DB2代码：
IF (v_sqlcode < 0) THEN
      GOTO ERROR;
END IF;

(2) 在前面 DECLARE 部分添加 HANDLER 处理的定义

清单5 ：添加 HANDLER 处理的定义

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE v_sqlcode INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET v_sqlcode = SQLCODE;
END;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET v_sqlcode = SQLCODE;
END;

第六步：修改 DECLARE 语句

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：DECLARE

修改点：

为所有类型添加 DEFAULT 值,

DEFAULT‘’ -- for Char or Varchar

DEFAULT 0; -- for int

DEFAULT 0.0; -- for numeric

◆为 char 和 varchar 添加 CCSID　ASCII。

◆如果函数体中间部分也存在 DECLARE 语句（比如 DECLARE CURSOR ），必须把它移到程序开始处。

注意： DB2 中所有 DECLARE 语句必须位于程序开始处。并且必须遵循以下顺序：

1. SQL variable and condition declarations

2. Statement declarations

3. Cursor declarations

4. Handler declarations

5. Any valid statements for an SQL procedure body

第七步：多行注释等价改写成多个单行

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：/*

修改点：使用‘--’进行逐行单行注释（ DB2 不支持多行注释）。

第八步：IF 语句的等价修改

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：IF

修改点：分两种语句格式：

清单6 ：多行注释等价改写成多个单行

第一种语句格式：
Sybase代码：
IF … 	BEGIN ……END 　

DB2代码：
IF …THEN 
BEGIN …… END ；
END IF;

第二种语句格式：
Sybase代码：
IF … 	BEGIN ……END　
ELSE   BEGIN ……END

DB2代码：
IF … THEN 
BEGIN …… END ；
ELSE
BEGIN …… END；
END IF;

第九步：在句末加上‘;’

方法：在每一个完整的语句末尾添加‘;’表示语句的分隔。

第十步：普通 FUNCTION 替换

方法：方法2，全局查找，逐个确认替换。有时+表示数据相加，不能替换。

查找关键词：+　　//这里表示字符串变量的连接

替换词：||

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：CONVERT

替换词：CAST

修改点：

清单7 ：在句末加上‘;’

Sybase 代码：
CONVERT( DataType , V_1 )    
        
DB2 代码：
CAST( V_1 as DataType )

方法：方法2，全局查找，逐个确认替换。

查找关键词：DATEDIFF

替换词：待定

方法：方法2，全局查找，逐个确认替换。

查找关键词：DATEADD

替换词：待定

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词："

替换词：'

注意：若是表示字符串，直接替为'。但是有时两个单引号表示字符串内部的一个单引号，这是不能替换。

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：SQUARE

修改点： DB2 不支持函数 SQUARE，需要重新改写。

清单8 ：改写对函数 SQUARE 的支持

Sybase 代码：
SQUARE (V_1)  
        
DB2 代码：
V_1 * V_1

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：SQRT

修改点：进行开平方运算时可能会出现精度不够，导致数据误差，需要根据具体情况改写。

清单9：修改关键词SQRT

Sybase 代码：
SQRT ( V_1 / V_2)
        
DB2 代码：
SQRT ( V_1 / 1000000 / V_2) * 1000

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：NULL

修改点：分两种情况:

情况１：表示判断是否为 NULL 的语句。

清单10：关键词NULL情况1

Sybase 代码：
Where V_1 = NULL
        
DB2 代码：
Where V_1 is NULL

情况２：在如下格式的语句‘insert into … select … ,NULL , … from … ’中, select 子句中 NULL 作为插入的值，但 DB2 不支持这种语法,去掉该列，默认插入值即为 NULL 值.

清单11：关键词NULL情况2

Sybase 代码：
insert into T_1(C_1,C_2) select C_1,NULL from T_2;     
   
 DB2 代码：
insert into T_1(C_1) select C_1 from T_2;

第十一步：getdate() 函数替换

getdate() 用于获取不同格式的时间表达。

CONVERT(CHAR, GETDATE(), 112) 时间格式为 YYYYMMDD

CONVERT(CHAR, GETDATE(), 108) 时间格式为 HHMISS

情况1：不在乎时间的格式，可以直接替换。

方法：方法2，全局查找，逐个确认替换。

查找关键词：getdate()

替换词：CURRENT TIMESTAMP

清单12：getdate()函数替换情况1

Sybase 代码：
SELECT @v_1 = GETDATE() ;  
   
DB2 代码：
SET v_1 = TIMESTAMP;

情况２：在乎时间的格式，需要具体情况具体改写。

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：getdate()

清单13：getdate() 函数替换情况2

Sybase 代码：
CONVERT(CHAR, GETDATE(), 112) 
   
DB2 代码：
varchar_format(CURRENT TIMESTAMP,'YYYYMMDD')

Sybase 代码：
CONVERT(CHAR, GETDATE(), 108)
            
DB2 代码：
varchar_format (CURRENT TIMESTAMP,'HH24MISS')

注意： DB2 函数 varchar_format 还支持格式 'YYYYMMDDHH24MISS'：

varchar_format(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS')

第十二步：游标 CURSOR 处理

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：CURSOR

修改点：

如果需要把 CURSOR 返回调用程序，不能关闭它

如果不需要把 CURSOR 返回调用程序，关闭它

DB2 中关闭CURSOR语句： CLOSE cursor-name，把DEALLOCATE CURSOR cursor-name 全局确认替换为 CLOSE cursor-name。

第十三步：update … set … from … 格式语句的等价修改

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：update

修改点：

清单14 ：update … set … from … 语法格式

Sybase 语法：
update … set V_1=C_1, V_2=C_2 … from T_1 … where …,

DB2 不支持上述语法，需要改写成如下语法：
update … set (V_1,V_2 ) = 
(select C_1,C_2 From T_1 … Where …) where …

清单15 ：update … set … from … 格式语句的等价修改

Sybase代码：
UPDATE T_1 
SET V_1 = T_2_alias.C_1, V_2 = T_2_alias.C_2, V_3 = T_2_alias.C_3
FROM T_2 T_2_alias
WHERE T_1.C_1 = T_2_alias.C_1
AND T_1.C_4=V_4

DB2代码：
UPDATE T_1 T_1_alias
SET (V_1, V_2, V_3) =   
(Select T_2_alias.C_1, T_2_alias.C_2, T_2_alias.C_3 
FROM T_2 T_2_alias
WHERE T_1_alias.C_1 = T_2_alias.C_1 )
Where T_1_alias.C_4 = V_4

注意：上述例子中必须为 T_1 指定别名 T_1_alias ，不能在后面的WHERE条件中使用‘T_1.C_1’。WHERE 条件的如何重写（拆分）需要根据业务逻辑具体确定。

第十四步：临时表的等价修改

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：#

修改点：分两种情况:

情况1,利用 select 语句创建临时表

清单16：利用 select 语句创建临时表

Sybase 代码：
select… into #T_1  from  T_2 where 1=2 
   
DB2 代码：
DECLARE GLOBAL TEMPORARY TABLE 
SESSION.#T_1 as (select … from T_2 )   WITH NO DATA 
CCSID ASCII ON COMMIT DROP TABLE;

情况2,利用 create 语句创建的临时表

清单17：利用 create 语句创建的临时表

Sybase 代码：
create table #T_1(C_1 type,C_2 type, … )
   
DB2 代码：
DECLARE GLOBAL TEMPORARY TABLE SESSION.#T_1(C_1 type,C_2 type, … )
CCSID ASCII ON COMMIT DROP TABLE;

注意：

1.所有引用临时表的地方，必须加上‘SESSION.’, 否则会使用当前的 SQLID

2.ON COMMIT DROP TABLE 表示在 COMMIT 的时候把临时表删除。

3. 要根据具体业务逻辑决定使用 CREATE GLOBAL TEMPORARY TABLE 还是 DECLARE GLOBAL TEMPORARY TABLE。

第十五步：ORDER BY 中带有函数运算问题

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：order by

修改点：

DB2 中 order by Y中不支持函数。例如 ‘order by sum(C_1) desc。根据具体业务逻辑进行等价修改。

第十六步：UNION 问题

方法：方法2，全局查找，逐个确认替换。

查找关键词：union

替换词：union all

修改点：

为提高性能（视具体应用程序），将 union 统一替换成 union all

清单18:UNION问题

Sybase 代码：
select C_1 from T_1 union select C_2 from T_2;
   
DB2 代码：
select C_1 from T_1 union all select C_2 from T_2;

第十七步：GROUP BY 中带有非聚合运算涉及的列

方法：方法3，全局查找，逐个确认，手工修改。

查找关键词：group by

修改点：

在 DB2 中，group by 中的列必须是聚集函数所涉及的， Sybase 中则不然。我们要根据具体业务逻辑进行等价修改。基本上可以直接去掉 group by 中多余的列。

清单19:GROUP BY 中带有非聚合运算涉及的列

Sybase代码：
select sum(C_1), C_2 from T_1 group by C_1, C_2;
select C_1, C_2 from T_1 group by C_1, C_2;

DB2 代码：
select sum (C_1),C_2 from T_1 group by C_2;
select C_1, C_2 from T_1;