簡(jiǎn)介

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

◆受支持的 SQL 方言中的不同語(yǔ)法。

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

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

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

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

清除表

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

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

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

清單1. 過(guò)程 TRUNCATE_TABLE的簽名

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

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

如果輸入?yún)?shù) schema_name 為 空 ，則由該過(guò)程的邏輯來(lái)確定默認(rèn)模式。否則，就刪除現(xiàn)有模式名上的雙引號(hào)，或者將未加引號(hào)的模式名轉(zhuǎn)換為大寫(xiě)體。對(duì)于表名同樣如此，比如最后表名上的雙引號(hào)會(huì)被刪除，或者未加引號(hào)的表名會(huì)被轉(zhuǎn)換為大寫(xiě)體。接著，我們通過(guò)查詢 DB2 目錄視圖 SYSCAT.TABLES 來(lái)證實(shí)該表是否存在。現(xiàn)在就可以啟動(dòng)導(dǎo)入了。先準(zhǔn)備好必要的參數(shù)，其中使用的文件是 /dev/null（Windows 上的 NUL 文件），因?yàn)樗偸谴嬖诓⑶也话魏蝺?nèi)容，也就是可用作數(shù)據(jù)源的空文件。同樣，/dev/null（Windows 上的 NUL 文件）將用于進(jìn)行導(dǎo)入所需的消息文件。如果成功地啟動(dòng)了導(dǎo)入，該過(guò)程就會(huì)成功返回。如果碰到錯(cuò)誤，則與消息文本一起返回 SQLSTATE 以指示錯(cuò)誤。清單 2 演示了過(guò)程 TRUNCATE_TABLE 的執(zhí)行。可以在“下載”小節(jié)中找到該腳本（ truncate_example.db2）的源代碼。

清單2. 測(cè)試過(guò)程 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ù)庫(kù)中的 host_name( ) 函數(shù)返回的是 客戶機(jī)進(jìn)程（非 Adaptive Server 進(jìn)程）的當(dāng)前主機(jī)名，也就是運(yùn)行該應(yīng)用程序的計(jì)算機(jī)的主機(jī)名而非數(shù)據(jù)庫(kù)服務(wù)器的主機(jī)名。

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

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

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

該函數(shù)訪問(wèn)存儲(chǔ)在 DBINFO 結(jié)構(gòu)中的應(yīng)用程序 ID 并解碼客戶機(jī)的 IP 地址（它是應(yīng)用程序 ID 的一部分）。然后便使用 C 庫(kù)函數(shù)“gethostbyaddr”來(lái)解析該 IP 地址的名稱，該函數(shù)在必要時(shí)將訪問(wèn)名稱服務(wù)器或其他源（比如 /etc/hosts）。

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

注意：

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

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

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

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

在該場(chǎng)景中，DB2 數(shù)據(jù)庫(kù)駐留在一個(gè)本地 AIX 機(jī)器上。

地址 127.0.0.1 在 /etc/hosts 文件中被映射到計(jì)算機(jī)名 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.

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

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

到 AIX 上數(shù)據(jù)庫(kù)的連接是由一個(gè) Windows 2000 客戶機(jī)建立的；

該客戶機(jī)的名字為 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

通過(guò)觸發(fā)器或用戶定義函數(shù)調(diào)用存儲(chǔ)過(guò)程的 UDF

當(dāng)遷移到 DB2 時(shí)，碰到的另一個(gè)普遍問(wèn)題就是其他 RDBMS 可以通過(guò)觸發(fā)器或函數(shù)調(diào)用存儲(chǔ)過(guò)程。雖然 DB2 已經(jīng)承諾在未來(lái)版本中包含該功能，但是我們將展示如何使用 DB2 的當(dāng)前版本來(lái)實(shí)現(xiàn)該功能，即通過(guò)創(chuàng)建一個(gè)將對(duì)存儲(chǔ)過(guò)程發(fā)出調(diào)用的 UDF 來(lái)實(shí)現(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 指定要被調(diào)用的存儲(chǔ)過(guò)程的全限定名 —— 在傳遞多個(gè)參數(shù)時(shí)，要用逗號(hào)進(jìn)行分隔。該字符串將被粘貼到用于調(diào)用過(guò)程的 CALL 語(yǔ)句中，因此其語(yǔ)法需要符合 SQL CALL 語(yǔ)句的要求。 VARCHAR(8) 類型的參數(shù) database_name 指定要執(zhí)行該存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)的別名。存儲(chǔ)過(guò)程不一定要駐留在同一數(shù)據(jù)庫(kù)中。 VARCHAR(128) 類型的參數(shù) user_name 和 VARCHAR(200) 類型的參數(shù) password 用于確定連接數(shù)據(jù)庫(kù)以及執(zhí)行該過(guò)程時(shí)所使用的注冊(cè)信息。

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

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

下面這個(gè)例子測(cè)試演示了從觸發(fā)器調(diào)用包含一個(gè)參數(shù)的存儲(chǔ)過(guò)程。

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

的過(guò)程（abc）以及一個(gè)觸發(fā)器（ins）。在執(zhí)行觸發(fā)器時(shí)，它將調(diào)用該過(guò)程。

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

這可以通過(guò)以下操作來(lái)測(cè)試：在表 t2 上執(zhí)行插入后對(duì) t1 發(fā)出 select

來(lái)檢驗(yàn)該表內(nèi)容 —— 進(jìn)而檢驗(yàn)該過(guò)程是否成功執(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.

下一個(gè)例子演示了在 UDF 中調(diào)用包含了兩個(gè)參數(shù)的存儲(chǔ)過(guò)程。在該例中，我們創(chuàng)建表（ c ），帶有兩個(gè)輸入?yún)?shù)的存儲(chǔ)過(guò)程（ abc ）以及帶有兩個(gè)參數(shù)（parm1，parm2）的 UDF（ udf_withcall ）。當(dāng)執(zhí)行該 UDF 時(shí)，它將調(diào)用存儲(chǔ)過(guò)程，然后，該存儲(chǔ)過(guò)程會(huì)將由 UDF 傳遞給它的值插入表 c。對(duì)表 c 進(jìn)行 select 將驗(yàn)證表 c 的內(nèi)容以及存儲(chǔ)過(guò)程是否執(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.

構(gòu)建例程

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

bldrtn [dbname userid password]

如果未提供 dbname，那么批文件會(huì)將之默認(rèn)為 SAMPLE ，而 userid 和 password 則被默認(rèn)為當(dāng)前會(huì)話的用戶 ID 和口令。

清單 7 演示了使用 bldrtn 批文件在 functions.sqc 文件中進(jìn)行的例程構(gòu)建。

清單7. 在 functions 文件上執(zhí)行 buildrn

bldrtn functions

值得說(shuō)明的是，可能需要對(duì) Windows 平臺(tái)上的 bldrtn 批文件進(jìn)行編輯

以便在鏈接指令中包含 ws2_32.lib。應(yīng)按照下列方法來(lái)修改該文件：

:link_step

rem Link the program.

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

在成功構(gòu)建 C 例程之后，其共享庫(kù)會(huì)被自動(dòng)地移到 sqllib/function 目錄中。

注意：

注意：構(gòu)建步驟中需要導(dǎo)出文件 .def（WINDOWS 上）或 .exp（UNIX 上）。

注冊(cè)例程

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

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

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)建好這個(gè)腳本之后，就將它保存在一個(gè)叫做 functions.db2的文件中，可在“下載”小節(jié)中找到該文件。該過(guò)程的最后一步是執(zhí)行該腳本。清單 9 展示了針對(duì)數(shù)據(jù)庫(kù)執(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

結(jié)束語(yǔ)

本文所展示的存儲(chǔ)過(guò)程、UDF 以及 DB2 API 的結(jié)合證明了在從競(jìng)爭(zhēng)的數(shù)據(jù)庫(kù)遷移到 DB2 UDB 時(shí)，DB2 有能力支持創(chuàng)造性的、可行的功能映射解決方案。