簡介

當使用來自于不同供應商的不同數據庫系統時，用戶和數據庫管理員將不可避免地碰到在這些產品中各不相同的特性和功能。通常，可在以下方面發現這些差異：

◆受支持的 SQL 方言中的不同語法。

◆數據庫管理器應用程序界面。

◆不同的管理工具及其用法。

為了使得將數據庫和應用程序從 Oracle?、Sybase? 或 Microsoft? SQL Server 等數據庫產品遷移到 IBM? DB2? Universal Database?（UDB）更容易，本文將展示一些可行的 DB2 UDB 功能實現，而且這些功能在其他數據庫系統中也可獲得。這些實現將涉及創建存儲過程和用戶定義函數（UDF）以實現那些常常被請求的功能。

在“下載”小節中，您將找到這些過程和函數的源代碼以及包含了 CREATE PROCEDURE 和 CREATE FUNCTION 語句的 SQL 腳本。如果您對確切的實現細節很感興趣，就請查閱這些代碼。一旦編譯并鏈接了源代碼（或安裝了預編譯的庫）以及在數據庫中注冊了這些過程和函數之后，您就可以按本文實例所演示的那樣來使用它們了。另外值得注意的是，這些過程和函數可用于 DB2 UDB 版本 7 和版本 8。

清除表

當從 Oracle 遷移到 DB2 時，所碰到的一個普遍問題就是 TRUNCATE 命令。在 Oracle 中執行時，該命令不用借助一個或多個 DELETE 操作就可快速地清除表中所有內容， DELETE 操作需要進行大量的日志記錄。

DB2 的 IMPORT 功能提供了完成相同功能的方法，只要使用 REPLACE INTO 子句以及將一個空文件指定為數據源。在該情況下，表中所有的行都將被快速清除并且只使用一條日志記錄，接著就從給定的文件中導入新的數據。而對于一個空文件，就不會導入任何內容，從而在該操作結束時清除了該表。

要實現該功能，我們可以利用 DB2 定義的叫做 sqluimpr() 的 C API 函數來以程序的方式將數據導入數據庫的表中。我們將這個 API 包裝到存儲過程中，以便可通過 SQL 接口用于所有的應用程序，而無需考慮編程的語言。清單 1 中展示了存儲過程 TRUNCATE_TABLE 的簽名。

清單1. 過程 TRUNCATE_TABLE的簽名

>>--TRUNCATE_TABLE--(--schema_name--,--table_name--)--------><

VARCHAR(130) 類型的參數 schema_name 指定模式，用以在其中找到表。如果模式名外加了雙引號，就將其看成定界名稱（混合大小寫的和特殊的字符）。如果模式名為 空 ，即未指定模式，那么則要查閱 CURRENT SCHEMA 專用寄存器來確定所要使用的模式。 VARCHAR(130) 類型的參數 table_name指定將被清除的表的未限定名稱。加上顯式或隱式定義的模式名就可惟一地識別出表。如果表名外加了雙引號，就將其看成定界名稱（混合大小寫的和特殊的字符）。

如果輸入參數 schema_name 為 空 ，則由該過程的邏輯來確定默認模式。否則，就刪除現有模式名上的雙引號，或者將未加引號的模式名轉換為大寫體。對于表名同樣如此，比如最后表名上的雙引號會被刪除，或者未加引號的表名會被轉換為大寫體。接著，我們通過查詢 DB2 目錄視圖 SYSCAT.TABLES 來證實該表是否存在。現在就可以啟動導入了。先準備好必要的參數，其中使用的文件是 /dev/null（Windows 上的 NUL 文件），因為它總是存在并且不包含任何內容，也就是可用作數據源的空文件。同樣，/dev/null（Windows 上的 NUL 文件）將用于進行導入所需的消息文件。如果成功地啟動了導入，該過程就會成功返回。如果碰到錯誤，則與消息文本一起返回 SQLSTATE 以指示錯誤。清單 2 演示了過程 TRUNCATE_TABLE 的執行。可以在“下載”小節中找到該腳本（ truncate_example.db2）的源代碼。

清單2. 測試過程 TRUNCATE_TABLE

/* create and insert some values into the table tab1 */

CREATE TABLE tab1 (col1 INTEGER NOT NULL PRIMARY KEY, col2 VARCHAR(15) )

DB20000I The SQL command completed successfully.

INSERT INTO tab1 VALUES ( 1, 'some data' ), ( 2, NULL )

DB20000I The SQL command completed successfully.

/* verify the current contents of table tab1 */

SELECT * FROM tab1

COL1 COL2

----------- ---------------

1 some data

2 -

2 record(s) selected.

/* Call the truncate stored procedure for the DB2INST1 schema, and the table tab1 */

CALL truncate('DB2INST1', 'tab1')

Return Status = 0

/* Verify that the table contents have been truncated. */

SELECT * FROM tab1

COL1 COL2

----------- ---------------

0 record(s) selected.

/* Insert some new values into the tab1 table */

INSERT INTO tab1 VALUES ( 2, 'some new data' ), ( 3, NULL )

DB20000I The SQL command completed successfully.

SELECT * FROM tab1

COL1 COL2

----------- ---------------

2 some new data

3 -

2 record(s) selected.

/* Call the truncate procedure with a NULL schema */

CALL truncate(NULL, 'tab1')

Return Status = 0

/* Verify that the table contents have been truncated. */

SELECT * FROM tab1

COL1 COL2

----------- ---------------

0 record(s) selected.

Sybase 的 host_name 函數

Sybase 數據庫中的 host_name( ) 函數返回的是 客戶機進程（非 Adaptive Server 進程）的當前主機名，也就是運行該應用程序的計算機的主機名而非數據庫服務器的主機名。

清單3 中展示了用戶定義函數 HOST_NAME 的簽名。

清單 3. 用戶定義函數 HOST_NAME 的簽名

>>--HOST_NAME--( )-------------><

該函數訪問存儲在 DBINFO 結構中的應用程序 ID 并解碼客戶機的 IP 地址（它是應用程序 ID 的一部分）。然后便使用 C 庫函數“gethostbyaddr”來解析該 IP 地址的名稱，該函數在必要時將訪問名稱服務器或其他源（比如 /etc/hosts）。

IP 地址是應用程序 ID 中前面 8 字節的編碼，或者使用 '*LOCAL' 來代表本地連接。對于本地連接，解析的是 IP 地址為 127.0.0.1 的主機名。

注意：

由于 DRDA 的需求，如果非本地 IP 地址的第一個字符初始為‘0’到‘9’，就將之映射到字母‘G’到‘P’。而在該名稱查找之前，要將該映射反過來進行。

清單 4 演示了 HOST_NAME 函數的執行。可以在“下載”小節中找到該腳本 host_name_example.db2 的源代碼。

清單4. 測試函數 HOST_NAME( )

下面這個例子測試演示了用以獲取本地連接主機名的函數的執行。

在該場景中，DB2 數據庫駐留在一個本地 AIX 機器上。

地址 127.0.0.1 在 /etc/hosts 文件中被映射到計算機名 demoaix：

/* connect to the local database */

connect to sample

Database Connection Information

Database server = DB2/6000 8.1.2

SQL authorization ID = DB2INST1

Local database alias = SAMPLE

/* execute the host_name function */

values host_name()'

1

------------------------------------------------

demoaix

1 record(s) selected.

下一個例子測試演示了遠程連接上的函數的執行。

在該場景中，DB2 數據庫與上面一樣駐留在同一 AIX 機器上。

到 AIX 上數據庫的連接是由一個 Windows 2000 客戶機建立的；

該客戶機的名字為 mycomputer。

/* The database samplaix is an alias for the SAMPLE database on AIX */

connect to samplaix

Database Connection Information

Database server = DB2/6000 8.1.2

SQL authorization ID = DB2INST1

Local database alias = SAMPLAIX

/* execute the host_name UDF against the remote database 鈥?

it returns the name of the computer of the client connection */

values host_name()

1

------------------------------------------------

mycomputer

通過觸發器或用戶定義函數調用存儲過程的 UDF

當遷移到 DB2 時，碰到的另一個普遍問題就是其他 RDBMS 可以通過觸發器或函數調用存儲過程。雖然 DB2 已經承諾在未來版本中包含該功能，但是我們將展示如何使用 DB2 的當前版本來實現該功能，即通過創建一個將對存儲過程發出調用的 UDF 來實現。

清單 5 中展示了用于該目的的用戶定義函數 CALL_PROCEDURE 的簽名。

清單 5. 用戶定義函數 CALL_PROCEDURE 的簽名

>>--CALL_PROCEDURE--(--procedure_name--,--parameter_list--,----->

>-----database_name--,--user_name--,--password--)-------------><

VARCHAR(257) 類型的參數 procedure_name 指定要被調用的存儲過程的全限定名 —— 在傳遞多個參數時，要用逗號進行分隔。該字符串將被粘貼到用于調用過程的 CALL 語句中，因此其語法需要符合 SQL CALL 語句的要求。 VARCHAR(8) 類型的參數 database_name 指定要執行該存儲過程的數據庫的別名。存儲過程不一定要駐留在同一數據庫中。 VARCHAR(128) 類型的參數 user_name 和 VARCHAR(200) 類型的參數 password 用于確定連接數據庫以及執行該過程時所使用的注冊信息。

該函數調用當前數據庫中的存儲過程。它建立新的連接之后就通過過程名和作為輸入參數而提供的參數來執行 CALL 語句。該 UDF 返回 0（零）表明 CALL 語句（以及相應的 CONNECT 和 CONNECT RESET 語句）執行成功。否則，將返回 DB2 命令行處理器（Command Line Processor，CLP）的返回碼和一條提供了更多信息的出錯消息。清單 6 演示了函數 CREATE_PROCEDURE 的執行。可以在“下載”小節中找到該腳本（ trig_calls_proc.db2）的源代碼。

清單6. 測試函數 CREATE_PROCEDURE( )

下面這個例子測試演示了從觸發器調用包含一個參數的存儲過程。

在該示例中，我們創建 t1 和 t2 這兩個表，帶有一個輸入參數（p）

的過程（abc）以及一個觸發器（ins）。在執行觸發器時，它將調用該過程。

然后，過程將會將 num 列的新值（NEW.coll）插入到表 t1 中。

這可以通過以下操作來測試：在表 t2 上執行插入后對 t1 發出 select

來檢驗該表內容 —— 進而檢驗該過程是否成功執行。

create table t1 ( col1 int)

DB20000I The SQL command completed successfully.

create table t2 ( col1 int )

DB20000I The SQL command completed successfully.

create procedure abc(in p int) begin insert into t1 values(p); end

DB20000I The SQL command completed successfully.

create trigger ins after insert on t2 referencing NEW as new for EACH ROW MODE

DB2SQL BEGIN ATOMIC values ( call_procedure('DB2INST1.ABC', char(new.col1 * 2),

'SAMPLE', 'DB2INST1', 'db2inst1') ); END

DB20000I The SQL command completed successfully.

insert into t2 values 20

DB20000I The SQL command completed successfully.

/* validate that the trigger has fired - it should update t1 */

select * from t1

COL1

-----------

40

1 record(s) selected.

下一個例子演示了在 UDF 中調用包含了兩個參數的存儲過程。在該例中，我們創建表（ c ），帶有兩個輸入參數的存儲過程（ abc ）以及帶有兩個參數（parm1，parm2）的 UDF（ udf_withcall ）。當執行該 UDF 時，它將調用存儲過程，然后，該存儲過程會將由 UDF 傳遞給它的值插入表 c。對表 c 進行 select 將驗證表 c 的內容以及存儲過程是否執行成功。可以在“下載”小節中找到該腳本（ udf_calls_proc.db2）的源代碼。

create table c ( a int check (a <> 8), a1 int )

DB20000I The SQL command completed successfully.

create procedure abc(in p int, in p2 int) begin insert into c values(p,p2); end

DB20000I The SQL command completed successfully.

create function udf_withcall (parm1 int, parm2 int)

returns int

Language SQL

not deterministic

external action

return call_procedure('DB2INST1.ABC', char(parm1) ||','|| char(parm2), 'SAMPLE', 'DB2INST1', 'db2inst1'))

DB20000I The SQL command completed successfully.

select udf_withcall(30,40) from sysibm.sysdummy1

1

-----------

0

1 record(s) selected.

/* verify that the UDF has called the procedure and updated the table */

select * from c

A A1

----------- -----------

10 20

30 40

2 record(s) selected.

構建例程

為了構建 C 例程（UDF 或 存儲過程），必須首先對其進行預編譯、編譯以及鏈接。該過程可通過批文件 bldrtn （UNIX/LINUX 上）或 bldrtn.bat （Windows 上）自動完成，該文件包含在隨 DB2 一同安裝的樣本中。可以在 UNIX/LINUX 上的 /sqllib/samples/c 目錄或 Windows 上的

bldrtn [dbname userid password]

如果未提供 dbname，那么批文件會將之默認為 SAMPLE ，而 userid 和 password 則被默認為當前會話的用戶 ID 和口令。

清單 7 演示了使用 bldrtn 批文件在 functions.sqc 文件中進行的例程構建。

清單7. 在 functions 文件上執行 buildrn

bldrtn functions

值得說明的是，可能需要對 Windows 平臺上的 bldrtn 批文件進行編輯

以便在鏈接指令中包含 ws2_32.lib。應按照下列方法來修改該文件：

:link_step

rem Link the program.

link -debug -out:%1.dll -dll %1.obj db2api.lib ws2_32.lib -def:%1.def

在成功構建 C 例程之后，其共享庫會被自動地移到 sqllib/function 目錄中。

注意：

注意：構建步驟中需要導出文件 .def（WINDOWS 上）或 .exp（UNIX 上）。

注冊例程

一旦構建了例程，就要在數據庫中注冊它們。清單 8 展示了為在數據庫中注冊這些例程而創建的腳本的內容。請注意，字符‘@’在此用作語句結束符：

清單8. 用于在數據庫中注冊例程的腳本

DROP SPECIFIC PROCEDURE truncate_table@

CREATE PROCEDURE truncate ( IN schemaName VARCHAR(130), IN tableName VARCHAR(130) )

SPECIFIC truncate_table

DYNAMIC RESULT SETS 0

MODIFIES SQL DATA

NOT DETERMINISTIC

CALLED ON NULL INPUT

LANGUAGE C

EXTERNAL NAME 'functions!truncate_table'

FENCED THREADSAFE

INHERIT SPECIAL REGISTERS

PARAMETER STYLE SQL

PROGRAM TYPE SUB

NO DBINFO

@

DROP SPECIFIC FUNCTION client_host_name@

CREATE FUNCTION host_name ( )

RETURNS VARCHAR(128)

SPECIFIC client_host_name

EXTERNAL NAME 'functions!host_name'

LANGUAGE C

PARAMETER STYLE SQL

DETERMINISTIC

FENCED

NOT THREADSAFE

RETURNS NULL ON NULL INPUT

NO SQL

NO EXTERNAL ACTION

NO SCRATCHPAD

NO FINAL CALL

ALLOW PARALLEL

DBINFO

@

DROP FUNCTION call_procedure@

CREATE FUNCTION call_procedure ( procedure VARCHAR(257),

parameters VARCHAR(30000), databaseName VARCHAR(8),

userName VARCHAR(128), password VARCHAR(200) )

RETURNS INTEGER

SPECIFIC call_stp

EXTERNAL NAME 'functions!call_procedure'

LANGUAGE C

PARAMETER STYLE SQL

NOT DETERMINISTIC

NOT FENCED

THREADSAFE

CALLED ON NULL INPUT

NO SQL

EXTERNAL ACTION

NO SCRATCHPAD

NO FINAL CALL

DISALLOW PARALLEL

NO DBINFO

@

在文本編輯器中創建好這個腳本之后，就將它保存在一個叫做 functions.db2的文件中，可在“下載”小節中找到該文件。該過程的最后一步是執行該腳本。清單 9 展示了針對數據庫執行該腳本所需的命令。

清單9. 執行 functions.db2 腳本

/* connect to the database */

db2 connect to SAMPLE

/* specify the terminating character (-td@), verbose output (-v), and the */

/* file name (f functions.db2) for script execution*/

db2 -td@ -vf functions.db2

結束語

本文所展示的存儲過程、UDF 以及 DB2 API 的結合證明了在從競爭的數據庫遷移到 DB2 UDB 時，DB2 有能力支持創造性的、可行的功能映射解決方案。