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

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

本文将介绍一套简单有效的移植方案，指导从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) OUTASDECLARE @v_1 VARCHAR(50) DECLARE @v_2 VARCHAR(1024)BEGINDB2代码:CREATE PROCEDURE Pr_ChkExPreCorp (IN p_1 VARCHAR(9),OUT p_2 VARCHAR(256))LANGUAGE SQLMODIFIES SQL DATAWLM ENVIRONMENT FOR DEBUG MODE WLMENV1ASUTIME NO LIMITNOT DETERMINISTICCOMMIT ON RETURN NOPARAMETER CCSID ASCIIPACKAGE OWNER TESTQUALIFIER TESTRESULT SETS 1BEGINDECLARE 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 C1FETCH C1 INTO ……WHILE @@sqlstatus = 0BEGIN… …FETCH C1 INTO ……ENDDB2 代码：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 <> 0GOTO ERRORDB2代码：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 FOUNDBEGINSET v_sqlcode = SQLCODE;END;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNINGBEGINSET v_sqlcode = SQLCODE;END;