簡介

當使用來自于不同供應商的不同數(shù)據(jù)庫系統(tǒng)時，用戶和數(shù)據(jù)庫管理員將不可避免地碰到在這些產(chǎn)品中各不相同的特性和功能。通常，可在以下方面發(fā)現(xiàn)這些差異：

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

◆數(shù)據(jù)庫管理器應用程序界面。

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

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

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

清除表

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

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

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

清單1. 過程 TRUNCATE_TABLE的簽名

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

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

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

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

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

清單 3. 用戶定義函數(shù) HOST_NAME 的簽名

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

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

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

注意：

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

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

清單4. 測試函數(shù) HOST_NAME( )

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

在該場景中，DB2 數(shù)據(jù)庫駐留在一個本地 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.

下一個例子測試演示了遠程連接上的函數(shù)的執(zhí)行。

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

到 AIX 上數(shù)據(jù)庫的連接是由一個 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

通過觸發(fā)器或用戶定義函數(shù)調用存儲過程的 UDF

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

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

清單 5. 用戶定義函數(shù) CALL_PROCEDURE 的簽名

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

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

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

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

清單6. 測試函數(shù) CREATE_PROCEDURE( )

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

在該示例中，我們創(chuàng)建 t1 和 t2 這兩個表，帶有一個輸入?yún)?shù)（p）

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

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

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

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

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 中調用包含了兩個參數(shù)的存儲過程。在該例中，我們創(chuàng)建表（ c ），帶有兩個輸入?yún)?shù)的存儲過程（ abc ）以及帶有兩個參數(shù)（parm1，parm2）的 UDF（ udf_withcall ）。當執(zhí)行該 UDF 時，它將調用存儲過程，然后，該存儲過程會將由 UDF 傳遞給它的值插入表 c。對表 c 進行 select 將驗證表 c 的內容以及存儲過程是否執(zhí)行成功。可以在“下載”小節(jié)中找到該腳本（ 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 文件上執(zhí)行 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 上）。

注冊例程

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

清單8. 用于在數(shù)據(jù)庫中注冊例程的腳本

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

@

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

清單9. 執(zhí)行 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 的結合證明了在從競爭的數(shù)據(jù)庫遷移到 DB2 UDB 時，DB2 有能力支持創(chuàng)造性的、可行的功能映射解決方案。