在 InfoSphere Federation Server V9.7 中连接存储过程返回的结果集

核心提示：简介InfoSphere Federation Server（以下简称 Federation Server）是提供远程数据访问的多数据库服务器，它提供对各种数据源的访问能力，在 InfoSphere Federation Server V9.7 中连接存储过程返回的结果集，这些数据源可驻留在不同平台上，包括 IBM 和

简介

InfoSphere Federation Server（以下简称 Federation Server）是提供远程数据访问的多数据库服务器。它提供对各种数据源的访问能力，这些数据源可驻留在不同平台上，包括 IBM 和其他供应商平台、关系型和非关系型平台。存储过程（Stored Procedure）是封装特定业务逻辑的 SQL 语句集，可以提高性能以及促进代码重用。 联邦存储过程可以看作是位于远程数据源的存储过程在本地的昵称，通过在 Federation Server 中创建联邦存储过程，用户可以像访问本地存储过程一样访问远程数据源上的存储过程。

对用户来说，如何把存储过程返回的结果集和本地的表或其他数据库对象连接，是一个很常见的需求。然而，目前 DB2 没有提供 SQL 语法支持直接连接存储过程返回的结果集，用户只能通过表函数间接连接存储过程返回的结果集。

创建表函数来返回存储过程的结果集是一个较为繁琐的过程：首先创建表函数映射到这种存储过程返回的结果集上，然后用某种编程语言去实现表函数的主体，最后再用这些表函数去做连接操作。这要求客户使用 C/Java 创建表函数并完成编译工作，手动创建一次十分耗时。

Federation Server V9.7 中新增了自动化工具 db2fedgentf，用于帮助用户自动创建上述的表函数。

db2fedgentf 功能介绍

用户可以使用 db2fedgentf 来为存储过程创建一个表函数，通过表函数返回存储过程的结果集，用于和本地或远程表进行连接。db2fedgentf 适于两种存储过程：

位于本地的存储过程。是指位于本地 DB2/Federation Server 的存储过程。

联邦存储过程。是为了在 Federation Server 中访问远程数据源上的存储过程而创建的。

下面将通过一个例子说明 db2fedgentf 的功能。假设在远程 DB2 LUW 数据源上存在存储过程 INVENTORY，根据输入的商品编号返回供应商姓名，如清单 1 所示。

清单 1. 位于远程 DB2 LUW 上的存储过程定义

　CREATE　PROCEDURE　INVENTORY　(IN　num1　int)　
　LANGUAGE　SQL　
　DYNAMIC　RESULT　SETS　1　
　READS　SQL　DATA　
　BEGIN　
　DECLARE　cursor1　CURSOR　WITH　RETURN　TO　CALLER　FOR　
　SELECT　*　FROM　TBL_INVT　where　NUM　=　num1;　
　OPEN　cursor1;　
　END　

其返回的结果集如清单 2 所示。

清单 2. 远程存储过程的执行结果

　call　INVENTORY(1)　
　Result　set　1　
　--------------　
　
　NUM　　　　　NAME　
　-----------　--------------------------------　
　　　　　　1　Martin　
　　　　　　1　Brett　
　
　2　record(s)　selected.　
　
　Return　Status　=　0　

在本地联邦数据库中，存在联邦存储过程 FS_INVENTORY 映射到远程存储过程 INVENTORY，如清单 3 所示。

清单 3. 联邦存储过程定义

　create　procedure　FS_INVENTORY　source　remoteuser1.INVENTORY　
for　server　SERV　reads　sql　data　

联邦存储过程 FS_INVENTORY 将返回与 INVENTORY 相同的结果集。本地联邦数据库中，表 NOTEBOOK 存有供应商的电话号码，如果我们希望通过货物编号查询供应商的电话号码，则需要类似清单 4 的 SQL 语句。

清单 4. 连接联邦存储过程的伪 SQL 代码

　select　PH_NUM　from　NOTEBOOK　a,　FS_INVENTORY(1)　b　where　a.NAME　=　b.NAME　

请注意，运行上述 SQL 会得到错误。因为 DB2 不支持直接连接存储过程返回的结果集。

使用 db2fedgentf 创建表函数

为了连接联邦存储过程返回的结果集，在 Federation Server V9.7 中，使用 db2fedgentf 来创建表函数 TF_INVENTORY，其命令如清单 5 所示。

清单 5. 使用 db2fedgentf 创建表函数命令
　db2fedgentf　-db　test1　-u　user1　-p　password1　-create　–　stps　USER1　-stpn　FS_INVENTORY　
　
　-c　"PH_NUMBER　INT,　NAME　VARCHAR(32)"　-tfn　TF_INVENTORY　

其中，-create 参数表明要创建表函数；-stps 参数指定联邦存储过程所在的 SCHEMA，-stpn 参数指定了联邦存储过程的名字，-c 参数指定联邦存储过程返回的结果集，-tfn 参数指定了要生成的表函数名称，如果不指定 -tfn 参数，那么生成的表函数将与存储过程同名。另外，可以使用 -tfs 参数指定生成表函数所在的 SCHEMA，还可以使用 -echo 参数查看 db2fedgentf 创建表函数的 SQL 语句。

这样，就可以使用表函数 TF_INVENTORY 获取联邦存储过程 FS_INVENTORY 返回的结果集，并和本地表 NOTEBOOK 进行连接操作了，如清单 6 所示。

清单 6. 通过表函数实现连接联邦存储过程的结果集
　select　a.PH_NUM,　a.NAME　from　NOTEBOOK　a,　table(TF_INVENTORY　(1))　b　where　a.NAME　=　b.NAME　
　
　PH_NUM　　　　　　　　　　　　　　NAME　
　--------------------------------　--------------------------------　
　123-567　　　　　　　　　　　　　Martin　
　234-678　　　　　　　　　　　　　Brett　
　
　2　record(s)　selected.　

使用 db2fedgentf 删除表函数

除了为联邦存储过程创建相应的表函数，也可以使用 db2fedgentf 删除指定的表函数，如清单 7 所示。

清单 7. 使用 db2fedgentf 删除表函数
　db2fedgentf　-db　test1　-u　user1　-p　password1　-drop　-tfn　TF_INVENTORY　

同样，可以使用 -tfs 参数指定要删除的表函数所在的 SCHEMA，还可以使用 -echo 参数查看 db2fedgentf 删除表函数的 SQL 语句。

db2fedgentf 的工作原理

这里将简单介绍 db2fengentf 的工作原理。仍然考虑本文中使用的查询供应商电话号码的例子。图 1 显示了 db2fedgentf 工具的工作原理以及如何通过表函数访问联邦存储过程返回的结果集。

图 1.db2fedgentf 工作原理

查看原图（大图）

db2fedgentf的创建表函数的工作过程大致如下：

解析检查输入参数；

从联邦数据的 catalog 中读取联邦存储过程 FS_INVENTORY 的相关信息，如输入输出参数信息等；

生成表函数 TF_INVENTORY 所需的 JAVA 源文件并编译；

创建表函数 TF_INVENTORY；

图 1 中的橙色箭头表示 db2fedgentf 创建表函数的过程，而蓝色箭头表示通过表函数访问联邦存储过程的路线。

db2fedgentf 生成 Java 外部表函数，因此会自动创建所需的 Java 源代码，其中使用 JDBC 方法调用联邦存储过程，并返回生成的结果集。最后 db2fedgentf 将编译后的 class 文件拷贝至 DB2 安装目录下的 function 目录下。

db2fedgentf 的适用范围和限制

db2fedgentf 目前支持的数据源包括：

DB2 数据源

ORACLE 数据源

Sybase 数据源

MS SQL Server 数据源

由于 db2fedgentf 采用 Java 表函数返回存储过程的结果集，表函数的实现依赖于 JDBC 的实现，因此表函数只支持那些同时被 Federation 和 JDBC 所支持的数据类型。 另外 JDBC 中的一些限制也会带入表函数中，例如 JDBC 中，timestamp 类型的精度只能达到纳秒，及 10-9 秒。

对位于 Sybase 和 MS SQL Server 的远程存储过程 , 如果存储过程同时返回输出参数和结果集，那么为之创建的联邦存储过程将无法得到任何结果集，联邦存储过程只能返回输出参数。如果使用 db2fedgentf 为这样的联邦存储过程创建表函数，同样无法得到任何结果集。因此 db2fedgentf 不适用那些包含输出参数的 Sybase 和 MS SQL Server 存储过程。

在创建联邦存储过程时，可以使用 with return to caller all 指定结果集返回给调用者 (caller)，这也是默认情况。或者指定 with return to client all 将结果集返回给客户端应用 (client)，如清单 8 所示。

清单 8. 带有 with return to client all 子句的联邦存储过程
　create　procedure　FS_INVENTORY　source　remoteuser1.INVENTORY　for　server　SERV　
　
　reads　sql　data　with　return　to　client　all　

对 with return to client all 的联邦存储过程，使用 db2fedgentf 为之建立表函数将不能获得结果集。

与之类似，为本地存储过程创建表函数时，本地存储过程定义中游标（cursor）声明也可以分为 with return to caller 或 with return to client。对于使用 with return to client 游标的本地存储过程，为之创建的表函数也不能获得结果集，如清单 9 中所示的存储过程就不适用于 db2fedgentf。

清单 9. 使用 with return to client 游标的 DB2 本地存储过程
　CREATE　PROCEDURE　LOCAL_PROC(IN　num1　int)　
　LANGUAGE　SQL　
　DYNAMIC　RESULT　SETS　1　
　READS　SQL　DATA　
　BEGIN　
　DECLARE　cursor1　CURSOR　WITH　RETURN　TO　CLIENT　FOR　
　SELECT　*　FROM　TBL_INVT　where　NUM　=　num1;　
　OPEN　cursor1;　
　END　

访问具有多结果集的联邦存储过程

db2fedgentf 所支持的数据源（DB2, ORACLE, Sybase, MS SQL Server）都具备存储过程返回多个结果集的能力。在清单 10 中，一个 DB2 的存储过程返回两个结果集。

清单 10. 返回多个结果集的 DB2 存储过程
　CREATE　PROCEDURE　REMOTE_MULTI(IN　num1　Integer)　
　DYNAMIC　RESULT　SETS　2　
　LANGUAGE　SQL　
　READS　SQL　DATA　
　BEGIN　
　DECLARE　cursor1　CURSOR　WITH　RETURN　FOR　
　select　*　from　TBL_INVT　where　num　=　num1　+　1;　
　DECLARE　cursor2　CURSOR　WITH　RETURN　FOR　
　select　*　from　TBL_INVT　where　num　=　num1;　
　OPEN　cursor1;　
　OPEN　cursor2;　
　END　

清单 11 显示了调用这个存储过程返回的结果。

清单 11. 具有多结果集的 DB2 存储过程执行结果
　call　REMOTE_MULTI(1)　
　
　Result　set　1　
　--------------　
　NUM　　　　　NAME　
　---------　---------　
　　　　　　2　Lisa　
　　　　　　2　Helen　
　2　record(s)　selected.　
　
　Result　set　2　
　--------------　
　NUM　　　　　NAME　
　---------　---------　
　　　　　　1　Martin　
　　　　　　1　Brett　
　2　record(s)　selected.　
　
　Return　Status　=　0　

通过 db2fedgentf 为返回多个结果集的联邦存储过程创建表函数，根据创建联邦存储过程时指定的 WITH RETURN TO 子句不同，会有表 1 中所示的几种情况：

表 1. with return to 子句对具有多结果集的联邦存储过程的影响

待访问存储过程所在位置	创建联邦存储过程时指定的 with return to 子句	db2fedgentf 创建的表函数返回结果集
位于远程 DB2, Sybase, MS SQL Server	caller all	返回第一个结果集
client all	不能返回结果集
位于远程 Oracle	caller all	返回第一个结果集
client all	不能返回结果集
client(m, n, p, … )	返回未出现在列表中的第一个结果集

如上表所示，针对 Oracle 的联邦存储过程可以指定哪些结果集返回到 client。那么意味着剩余的结果集将被返回到 caller。例如，如果 oralce 数据源中的存储过程返回 3 条结果集， 此时建立联邦存储过程的时候可以增加关键字 with return to client(1,3)，即只有第 2 条结果集可以被返回到 caller，如清单 12 所示。

清单 12. 为远程 Oracle 数据源创建的联邦存储过程
　create　procedure　FS_ORA_MULTI　source　remoteuser1.ORA_MULTI　for　server　SERV　
　reads　sql　data　with　return　to　client　(1,3)　

对于这个联邦存储过程，使用 db2fedgentf 建立的表函数将获得第 2 个结果集，那么调用 db2fedgentf 时指定的 -c 参数就必须以第 2 个结果集的字段列表为准。

如果本地存储过程返回多个结果集，那么使用 db2fedgentf 为之创建的表函数将只能返回第一个结果集。

访问重载的存储过程

如果要访问的存储过程具有的重载定义（表函数的名称相同但是输入参数个数不同），那么使用 db2fedgentf 建立表函数时需要使用参数 -stpc 指定存储过程的参数个数（包括所有输入和输出参数）来区分多个 fstp。如清单 13 所示。

清单 13. 重载的存储过程
　--　指定　db2fedgentf　的　-stpc　参数为　1　
　CREATE　PROCEDURE　Proc(IN　num1　int)　
　--　指定　db2fedgentf　的　-stpc　参数为　3　
　CREATE　PROCEDURE　Proc(IN　num1　int,　IN　num2　int,　OUT　num3　int)　

如果您访问的存储过程包含输出参数，那么 db2fedgentf 将会忽略这些输出参数，生成的表函数将只包含该存储过程的输入参数。

那么对两个重载的存储过程来说，如果它们去掉输出参数后的参数列表相同，使用 db2fedgentf 创建表函数时，必须使用 -tfn 参数为两个重载存储过程分别指定不同的表函数名称。如果不指定 -tfn，那么只能为两个重载存储过程中的一个建立表函数，否则 db2fedgentf 将提示具有同样签名的表函数已经被创建。

如果您创建的两个表函数名字相同但参数不同，如清单 14 所示

清单 14. 查看重载的表函数
　=>　select　ROUTINENAME,　PARM_COUNT　,　SPECIFICNAME　from　syscat.routines　
　
　where　ROUTINESCHEMA　=　'USER1'　and　ROUTINETYPE='F'　
　
　ROUTINENAME　　　PARM_COUNT　　　SPECIFICNAME　
　-----------　　　----------　　　------------------------------　
　OVER_LOAD_TF　　　1　　　　　　　　DB2FEDGENTF_SQL090704233411000　
　OVER_LOAD_TF　　　2　　　　　　　　DB2FEDGENTF_SQL090704233518200　

那么使用 db2fedgentf 删除时无法通过 -tfn 来指定唯一的表函数。这时可以通过 -tfsn 参数指定要删除的表函数 specific_name，如清单 15 所示。

清单 15. 指定 specific_name 删除重载的表函数
　=>　db2fedgentf　-db　test1　-u　user1　-p　password1　-drop　-tfsn　
DB2FEDGENTF_SQL090704233411000　
　
　db2fedgentf　successfully　dropped　the　table　function　:　
　
　DB2FEDGENTF_SQL090704233411000(SPEICFIC_NAME).　

小结

db2fedgentf 是 InfoSphere Federation Server V9.7 中的新增特性，可以支持多种数据源上的存储过程，帮助客户自动创建表函数来获取存储过程返回的结果集，在无需掌握 C/Java 编程技术的情况下，即可实现连接存储过程返回的结果集。希望通过阅读本文，读者可以运用 db2fengentf 工具提供的功能，更加方便的访问联邦数据库产品中的存储过程。