讲解使用IBM DB2例程简化迁移的具体步骤

简介

当使用来自于不同供应商的不同数据库系统时，用户和数据库管理员将不可避免地碰到在这些产品中各不相同的特性和功能。通常，可在以下方面发现这些差异：

◆受支持的 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 有能力支持创造性的、可行的功能映射解决方案。