对包含结果集的 Sybase 存储过程的访问

中国IT动力,最新最全的IT技术教程

最新100篇 | 推荐100篇 | 专题100篇 | 排行榜 | 搜索 | 在线API文档

当前位置：> IBM专区 > DB2 > 集成

对包含结果集的 Sybase 存储过程的访问

作者:佚名时间:2005-08-05 11:30 出处:互连网责编:小渔

摘要：DB2 Information Integrator 对包含结果集的 Sybase 存储过程的访问

级别: 中级

Micks Purnell
IBM Advanced Technical Support Americas, IBM Corporation
2004 年 9 月

DB2® Information Integrator 使您能够访问公司很多部门以不同数据类型、不同格式存储在不同的数据库中的数据。如果您需要在 DB2 Information Integrator 中访问 Sybase 存储过程，本文可以提供帮助。Micks Purnell 将告诉您如何设置 DB2 II，以利用 Sybase 中已存在的包含结果集的存储过程。

简介
Sybase 数据库用户常常出于不同的目的创建一些存储过程。需要经常访问 Sybase Adaptive Server Enterprise (ASE) 的 DB2 Information Integrator (DB2 II) 用户都喜欢利用 Sybase 中已有的存储过程。然而，DB2 II 仍不支持用户在 DB2 II 中发出对存储过程名的请求，并由 DB2 II 将该请求发送给 Sybase 中的存储过程。

解决这一问题的一种方法是，让 DB2 II 用户在 DB2 II 中创建存储过程所使用的 Sybase 表的昵称，然后创建一个 DB2 版的存储过程，直接在 DB2 II 中运行。 IBM Sybase-to-DB2 Migration Toolkit便可以创建 Sybase 存储过程的 DB2 版。

本文描述的是在 Sybase 存储过程返回一个单独的结果集的情况下，可用来解决上述问题的另一种方法。通过这种方法，DB2 II 可以使用 Sybase 中已有的存储过程。简而言之，首先在 Sybase 服务器为存储过程的结果集创建“现有表（existing table）”，然后创建 Sybase “现有表”的 DB2 II 昵称。当 DB2 II 用户访问该昵称时，DB2 II 向 Sybase 发送一个访问“现有表”的请求。然后，Sybase 运行存储过程并将结果集发送给 DB2 II。

技术描述
Sybase 现有表（existing table）是 Sybase ASE 中的一种代理表。创建“现有表”时，首先会创建一个代理表，然后检索和存储远程表中的元数据，并将该元数据放入到这个代理表中。Sybase ASE 中的 "create existing table" 命令允许将代理表映射到在远程位置的一个表、视图或者过程。

在以下情况下，DB2 Information Integrator 用户和应用程序可以访问会返回结果集的 Sybase ASE 存储过程：

为该存储过程创建了一个 Sybase 现有表。
为该 Sybase 现有表创建了一个 DB2 II 昵称。

DB2 II 用户或应用程序从昵称中进行选择，而 DB2 II 则连接到 Sybase，并从现有表中进行选择。Sybase 将运行现有表定义中引用的存储过程，并返回存储过程结果集，DB2 II 会将该结果集交给用户或应用程序。

要获得对 Sybase ASE 中 "create exiting table" 命令的描述，请参阅 Sybase Reference Manual: Commands。注意，"create existing table" 命令要求 Sybase ASE 的版本为 Version 11.9.2 、Version 12 或 Version 12.5。而且，必须在 Sybase 服务器上安装并启动 Sybase ASE Component Integration Services。

要获得访问 Sybase ASE 时对 DB2 II 的要求以及如何配置 DB2 II 才能访问 Sybase ASE 和创建昵称的描述，请参阅 DB2 Information Integrator documentation，尤其是 Installation Guide和 Data Source Configuration Guide。

图 1说明了 DB2 II 昵称、Sybase 现有表和存储过程之间的关系。

图 1. DB2 II 通过“现有表”访问 Sybase 存储过程
DB2 II 通过'现有表'访问 Sybase 存储过程

注意，Sybase 现有表是只读的。我们可以使用 SELECT 语句查看存储过程的结果集，但不能让存储过程用 INSERT/UPDATE/DELETE 语句更新数据。对于 Sybase 现有表的 DB2 II 昵称，也是一样。您可以对昵称使用 select 语句，但是不能使用 insert/update/delete 语句。请参阅本文的错误小节，以获得当 DB2 II 用户尝试更新一个存储过程的 Sybase 现有表的昵称时，可能出现的错误信息的示例。

对于 Sybase 现有表，可以使用 WHERE 子句指定现有表的列名，以限制存储过程的输出。对于现有表的 DB2 II 昵称，也可以使用 WHERE 子句指定昵称中与 Sybase 现有表各列对应的列名，以限制 DB2 II 用户或应用程序接收来自 Sybase 存储过程的输出。

在 Sybase 中为存储过程执行 "create existing table" 时，结果集的列名必须有恰当的描述，这些描述包括数据类型、长度/精确度、小数点后的位数（scale）以及 null/not null 等特征。"create existing table" 将现有表各列的信息放入 Sybase 编目中。当创建现有表的一个 DB2 II 昵称时，DB2 II 便从 Sybase 编目中获得现有表的列信息，并使用该信息映射被创建的 DB2 II 昵称的各列的属性。

当为一个存储过程执行 Sybase "create existing table" 命令时，Sybase 并不验证存储过程结果集的各列的属性。如果在 "create existing table" 语句的列属性中有错误，这些错误也只有在使用“现有表”，并且“现有表”的列属性与存储过程返回值的数据类型不符时才会碰到。换句话说，只有从现有表的 DB2 II 昵称中进行选择时，才会发现"create existing table" 列描述中的错误。请参阅本文中错误小节，以获得相关示例。

如果存储过程有多个结果集，那么 Sybase 现有表只能从存储过程那里获得第一个结果集。现有表定义必须指定存储过程第一个结果集的各列的特征。而且，DB2 II 用户或应用程序收到的记录也将来自存储过程的第一个结果集。

DB2 Information Integrator 用户映射到的 Sybase 帐户必须有“现有表”上的选择许可和存储过程上的执行许可。请参阅本文中错误小节，以获得可能发生的错误的示例。

虽然存储过程访问的表可以包含调用存储过程时使用的索引，但在 Sybase 编目中，Sybase 现有表没有与之相关的索引信息。而且，现有表没有统计信息。因此，在为一个 Sybase 现有表创建 DB2 II 昵称时，CREATE NICKNAME 语句不能将索引信息或统计信息放入昵称的 DB2 II 编目中。如果昵称进行自我引用，那么 DB2 II 优化器仍将下推（push down）与昵称一起使用的谓词。但是在某些情况下，在创建昵称之后，可能需要添加索引信息和统计信息，以便让优化器下推与昵称一起使用的谓词。可以用 DB2 UDB SQL 语句 CREATE INDEX…SPECIFICATION ONLY 向昵称添加索引信息。并且可以用 UPDATE SSYSSTAT.TABLES 和 UPDATE SSYSSTAT.COLUMNS 来添加统计信息。

示例
在这个示例中，我们将在 Sybase 中为 Sybase 编目存储过程 sp_tables 创建一个现有表，然后为该现有表创建一个 DB2 II 昵称。我们还将给出一个使用昵称的示例。

图 2展示了示例中 DB2 II 昵称、Sybase 现有表和存储过程之间的关系。

图 2. 昵称、现有表和存储过程之间的关系

存储过程
首先，我们来看一看将要使用的存储过程（sp_tables）。

sp_tables 是一个 Sybase ASE 编目存储过程。在 Sybase 参考手册：关于编目（Catalog）存储过程的规程章节中，对 sp_tables 进行了描述。该手册中对 sp_tables 的描述包括对其结果集的描述，以及谁可以使用该存储过程：

表 1. 对 sp_tables 结果集的描述

列 数据类型 描述

table_qualifier varchar(30) 数据库名，该字段可以为 NULL

table_owner varchar(30)

table_name varchar(30) 表名。不为 NULL

table_type varchar(32) 不为 NULL.。可以是 'TABLE'、'VIEW'或'SYSTEM TABLE'

remarks varchar(254) NULL

许可：任何用户都可以执行 sp_tables

使用存储过程
在 UNIX 上，存储过程存放在 Sybase ASE 中，但是我们使用的是 Windows，并且安装了 Sybase Open Client。为了使用存储过程，我们需要连接到 Sybase 服务器。

我们将使用 isql （Open Client 自带）来连接到 Sybase ASE 服务器，该服务器位于一个名为 viper 的系统上。我们访问的 Sybase 数据库叫做 'dj'，Sybase 的帐户是 'j15user1'。从客户机发出如下命令：


            isql -I c:\Sybase\ini\sql.ini -S Sybase12_5 -D dj -U j15user1 -P j15pwd

关于这个登录到 Sybase ASE 的示例的注解：

–I c:\Sybase\ini\sql.ini:在 Windows 中，Sybase 接口文件被命名为 'sql.ini'，并存放在 Sybase \ini 子目录中。而在 UNIX 中，该接口文件被命名为 'interfaces'，通常存放在 Sybase 主目录中。
-S Sybase12_5:’Sybase12_5’ 是本地 Sybase 接口文件中关于我们要连接到的 Sybase ASE 服务器的条目。
–D dj:'dj' 是 Sybase ASE 服务器上我们要访问的数据库的名称。
–U j15user1:'j15user1' 是我们在 Sybase ASE 服务器上要使用的 Sybase 帐户。

接下来我们使用存储过程：


            1> sp_tables
            2> go

下面是结果集：

表 2. 执行存储过程返回的结果集

table_qualifier table_owner table_name table_type remarks

dj j15user1 mixtab TABLE NULL

dj j15user1 mixtab2 TABLE NULL

dj j15user1 mixtest TABLE NULL

dj j15user1 appl_trans TABLE NULL

dj j15user1 mrg_eval TABLE NULL

注意：在该 Sybase 服务器上的数据库中，sp_tables 的结果集中还有许许多多的记录。我们只展示了前 5 条记录。

存储过程上的许可
所有 Sybase 帐户都可以执行 sp_tables。如果您需要授予访问权，下面是一条授权命令的示例，该命令将授予 PUBLIC 的许可，以执行 sp_tables：


            > grant execute on dj.dbo.sp_tables to public
            > go

为存储过程创建现有表
在 Sybase 中，我们将为存储过程 dbo.sp_tables 创建一个名为 j15user1.sp_tabs 的现有表，该存储过程位于 Sybase 数据库 'dj' 中。下面是我们对 sp_tables 使用的 'create existing table' 语句：


            1>create existing table dj.j15user1.sp_tabs
            2>(table_qualifier  varchar(30) null,
            3>table_owner      varchar(30) not null,
            4>table_name       varchar(30) not null,
            5>table_type        varchar(32) not null,
            6>remarks           varchar(254) null    )
            7>external procedure
            9>at 'local.dj.dbo.sp_tables'
            10>go

注意：

列定义：我们匹配来自存储过程结果集的描述中的列名、数据类型、长度/精确度/小数点后位数和 null/not null 等特征。
在 'local.dj.dbo.sp_tables'：
- local—— 服务器名。在执行 'create existing table' 之前，Sybase ASE 系统管理员使用
  sp_addserver "local", "ASEnterprise", "sybase 12_5"
  添加一个名为 'local' 的服务器，其中 'sybase12_5' 是 Sybase ASE 服务器在接口文件中的名称，该接口文件也位于那台服务器上。
- dj—— 定义存储过程时所在的 Sybase 数据库的名称。
- dbo—— 存储过程的所有者。
- sp_tables—— 存储过程名。

现有表上的许可
如果在创建现有表时的所有者不同于 DB2 II 用户映射将要使用的帐户，那么我们需要将‘现有表’上的选择权限授予将在 DB2 II 用户映射中使用的帐户。


            1> grant select on j15user1.sp_tabs to public
            2> go

测试现有表
我们仍然使用 isql 连接到 Sybase。

我们将尝试从现有表中进行选择，并看一看返回的结果是怎样的：

1> select * from dj.j15user1.sp_tabs 2> go

结果如下：

表 3. 从现有表中进行选择所产生的结果

table_qualifier table_owner table_name table_type remarks

dj j15user1 mixtab TABLE NULL

注意：在结果中有多个行。我们只显示了第一行。

DB2 II configuration to the Sybase 服务器
在我们的示例中，我们已经在 Windows 上安装了 DB2 II。

同时还安装了 Sybase Open Client 12.5。Sybase Open Client 已经配置为连接到我们要访问的存储过程所在的 Sybase ASE 服务器。我们已经使用 Open Client 自带的 isql 对该连接进行了测试。

DB2 II 包装器、服务器和用户映射的定义都可以在 Federated Database Objects 图标下的 DB2 Control Center 中实现，或者通过命令实现。下面是我们使用的命令：


            CREATE WRAPPER SYBASE LIBRARY ‘db2ctlib.dll’
            ;
            CREATE SERVER SYB_VIPER
            TYPE SYBASE
            VERSION 12.5
            WRAPPER SYBASE
            OPTIONS
            (NODE ‘Sybase12_5’,
            DBNAME ‘dj’,
            IFILE ‘c:\Sybase\ini\sql.ini’ )
            ;
            CREATE USER MAPPING FOR DB2ADMIN
            SERVER SYB_VIPER
            OPTIONS
            (REMOTE_AUTHID ‘j15user1’,
            REMOTE_PASSWORD ‘j15pwd’)
            ;

注解：

WRAPPER SYBASE—— 包装器名可以是任意的，但是在 CREATE WRAPPER 语句中，必须在 CREATE SERVER 语句中引用指定为包装器名的名称。
LIBRARY 'db2ctlib.dll' —— Windows 上用来使用 Sybase Open Client ctlilb 的 DB2 II 包装器库。在 AIX 上，该包装器库的名称为 'libdb2ctlib.a'。在 Solaris 和 Linux 上，该名称为 'libdb2ctlib.so'，而在 HP 上，该名称则为 'libdb2ctlib.sl'。
SERVER SYB_VIPER—— 服务器名可以是任意的，但是在 CREATE SERVER 语句中，必须在 CREATE USER MAPPING 和 CREATE NICKNAME 语句中引用指定为服务器名的名称。
TYPE SYBASE—— 类型必须是 SYBASE。
VERSION 12.5—— Sybase ASE 服务器的版本。
NODE 'Sybase 12_5'—— 该名称与 DB2 II 系统上 /sql.ini 接口文件中的 Sybase ASE 服务器的名称相匹配。
DBNAME 'dj'—— 拥有存储过程的 Sybase ASE 数据库的名称。
IFILE 'c:\Sybase\ini\sql.ini'—— 我们通过该 Server 选项来指定接口文件的位置。另一种选择是将 sql.ini 文件复制到 DB2 II sqllib 目录中，并将其重命名为 'interfaces'。
USER DB2ADMIN—— 正在配置 DB2 II 到 Sybase 的访问的 DB2 II 用户 id。如果其他 DB2 II 用户需要访问 Sybase 存储过程，则需要为他们进行附加的用户映射。
REMOTE_AUTHID 'j15user1'—— Sybase ASE 服务器上的帐户。
REMOTE_PASSWORD 'j15pwd'—— Sybase 帐户的密码。

我们建议，在完成 Wrapper/Server/User Mapping 的定义之后，测试 DB2 II 与 Sybase ASE 服务器的连接。您可以在 DB2 Control Center 中使用 Create Nickname 'Discover' 功能做到这一点。在服务器定义下，高亮显示 Nicknames图标 -> 单击右键-> Create Nickname，并选择右上角的 Discover按钮。在 Discover 过滤器上，选择 Count按钮。

这将使 DB2 II 连接到 Sybase ASE 服务器，并获得 DB2 II 服务器定义所针对的 Sybase 数据库中所有表的计数。或者，如果您想使用命令来测试该连接，那么可以使用 SET PASSTHRU。记住，SET PASSTHRU 语句本身并不能测试该连接。真正测试该连接的是该语句后面的那条语句。例如：


            SET PASSTHRU SYB_VIPER
            ;
            SELECT COUNT(*) FROM dbo.sysobjects
            ;
            SET PASSTHRU RESET
            ;

Sybase 现有表的 DB2 II 昵称
要使用存储过程的 Sybase 现有表，则需要为之创建一个昵称。

您可以在 DB2 Control Center 中创建昵称，Create Nickname 'Discover' 功能应该能够找到 Sybase 现有表。

图 3. Control Center 的 CREATE NICKNAME Discover 过滤器

记住，存储过程所在的 Sybase 数据库的名称是 DB2 II 服务器定义的一部分。

下面是在不使用 Control Center 的情况下为现有表创建昵称的命令：


            CREATE NICKNAME SYB_VIPER.SP_TABS
            FOR SYB_VIPER."j15user1"."sp_tabs"
            ;

注解：

NICKNAME SYB_VIPER.SP_TABS
- SYB_VIPER 是我们所要创建的昵称的模式。
- SP_TABS 是我们要创建的昵称的名称。
FOR SYB_VIPER."j15user1"."sp_tabs"
- SYB_VIPER 是 Create Server 语句中的服务器名。
- "j15user1" 是 Sybase 中现有表的所有者。这里用双引号将其括起来，这样 DB2 II 在查询 Sybase 编目之前就不会将它转为大写形式。
- "sp_tabs" 是 Sybase 中‘现有表’的名称。这里用双引号将其括起来，这样 DB2 II 在查询 Sybase 编目之前就不会将它转为大写形式。

创建好昵称之后，我们可以看到它的列定义。DB2 II 使昵称各列的列名与现有表各列的列名一致，不过昵称的列名都是大写的。

对于昵称各列的数据类型，DB2 II 首先找出现有表各列的数据类型，然后使用 DB2 II Sybase-ctlib 包装器内的默认类型映射确定昵称各列的 DB2 数据类型。对于昵称各列的长度/精确度、小数点后位数和 null/not null 等特征，DB2 II 使用了 Sybase ‘现有表’中相应列的长度/精确度、小数点后位数和 null/not null 特征。

我们可以在 DB2 Control Center 中查看昵称各列的特征，方法是进入服务器定义下的昵称图标，获得一个昵称列表，将鼠标移到我们想要查看的昵称上使之高亮显示 -> 右击 -> Alter。

图 4. 用于存储过程 sp_tables 的 Sybase ‘现有表’ j15user1.sp_tabs 的昵称

图 5. Sybase 现有表 j15user1.sp_tabs 的昵称的列属性

或者，我们也可以使用 DESCRIBE TABLE 命令找出昵称各列的属性：


            DESCRIBE TABLE SYB_VIPER.SP_TABS

输出如下：

表 4. DESCRIBE TABLE 输出

Column name Type schema Type name Length Scale Nulls

TABLE_QUALIFIER SYSIBM VARCHAR 30 0 Yes

TABLE_OWNER SYSIBM VARCHAR 30 0 Yes

TABLE_NAME SYSIBM VARCHAR 30 0 No

TABLE_TYPE SYSIBM VARCHAR 32 0 No

REMARKS SYSIBM VARCHAR 254 0 Yes

我们也可以通过从昵称中进行选择来查看存储过程的结果集。我们可以在 DB2 Control Center 中看到这一结果集，方法是将鼠标移到昵称图标上使之高亮显示 ->单击右键-> Sample contents。

或者，我们可以使用命令：


            SELECT * FROM SYB_VIPER.SP_TABS

输出如下。注意，输出结果中实际上有很多条记录。但在这里，我们只显示了第一行。

表 5. 从昵称中进行选择所得到的结果

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS

dj j15user1 mixtab TABLE -

图 6. 来自 Sybase 现有表的昵称的 Control Center ‘Sample Contents’

使用包含昵称的 WHERE 子句
sp_tables 有一些输入参数。其中一个参数是 table_name。当使用 sp_tables 时，可以这样来指定该参数：


            1>sp_tabs @table_name='mixtab'
            2>go

当使用昵称时，可以这样来指定该参数：


            SELECT * FROM SYB_VIPER.SP_TABS WHERE TABLE_NAME='mixtab'

可能发生的错误
以下这些是在使用 Sybase 存储过程的 Sybase 现有表的 DB2 II 昵称时，可能碰到的错误的示例。

来自数据源 "SYB_VIPER" 的 SQL1822N Unexpected error code "11216"错误。
相关文本和标记是 "Internal Ct-Lib/Cs-Lib error 33816856:
'cs_convert: csl". SQLSTATE-560BD

如果 Sybase 现有表定义没有为存储过程结果集的某一列指定适当的数据类型，就可能发生上述错误。如果 Sybase 存储过程有多个结果集，而现有表定义不是针对第一个结果集中的各列，那么也可能发生这种错误。
SQL0407N Assignment of a NULL value to a NOT NULL column
"no column name " 是不允许的。SQLSTATE-23502

如果 Sybase 现有表定义声明某一列为 NOT NULL，但是在结果集中的某一行记录在那一列没有值，就可能发生上述错误。
在使用 DB2 II 昵称时得到的 the values received for one of the columns are truncated错误。如果 Sybase 现有表定义指定了一个列的长度，但是这个长度对于存储过程结果集中这一列的值来说太短了，就会发生这种错误。
SQL1822N Unexpected error code "10330"来自数据源 "SYB_VIPER".
相关文本和标记是 "SELECT permission denied on object sp_mixtab2_tab, database"。SQLSTATE=560BD

如果在 DB2 II 用户映射当中，映射到的 Sybase 帐户没有从现有表中进行选择的许可，就可能发生上述错误。
SQL1822N Unexpected error code "10330"来自数据源 "SYB_VIPER".
相关文本和标记是 "EXECUTE permission denied on object sp_mixtab2, databas"。SQLSTATE=560BD

如果在 DB2 II 用户映射当中，映射到的 Sybase 帐户没有 Sybase 存储过程的执行许可，就可能发生上述错误。
SQL1822N Unexpected error code "11203"来自数据源 "SYB_VIPER".
相关文本和标记是 "The command 'DELETE' is not legal for objects of this t"。SQLSTATE=560BD

如果 DB2 II 用户/应用程序尝试对 Sybase 存储过程的‘现有表’的昵称执行 insert/update/delete 操作，就可能发生上述错误。对于 Sybase 现有表，insert/update/delete 操作是不受支持的，因此这些操作对于现有表的 DB2 II 昵称也不受支持。
关于作者
Micks Purnell 是一名数据管理软件 IT 专家，也是 IBM 的 Advance Technical Support Americas。从 1995 年开始，Micks 一直从事于 DB2 Information Integrator 及其前任产品 DB2 Relational Connect and DataJoiner 的研究工作。

关闭本页