簡單有效的SQL Stored Procedure移植方案

2024-07-21 02:43:56

字體：大中小

來源：轉載

供稿：網友

本文將介紹一套簡單有效的移植方案，指導從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;

第六步：修改 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 ……ENDDB2代碼：IF … THEN BEGIN …… END ；ELSEBEGIN …… 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_3FROM T_2 T_2_aliasWHERE T_1.C_1 = T_2_alias.C_1AND T_1.C_4=V_4DB2代碼：UPDATE T_1 T_1_aliasSET (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_aliasWHERE 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;

上一篇：使用TSM對DB2數據庫應用程序實現存儲備份

下一篇：從三個方面進行講解如何適當優化"SQL"