Oracle 10g Transparent GateWays for DB2

安装环境:
DB2 SERVER:192.168.1.2 version:9.7
ORACLE SERVER:192.168.1.54 version:10.2.0.1
ORACALE GATEWAYS:192.168.1.54; version:10.2.0.1
据网络资料:Oracle 8i gateways为收费插件 oracle10g的10.1.0版本的gateWay也为收费插件 本例使用的是10g的10.2.0.1版本
下载地址: http://www.oracle.com/technetwork/database/10201winsoft-095341.html
安装问题:
1 在一台服务器上安装oracle server 和 oracle gateways时会出现plsql develop连接不到服务错误:ora-12154:TNS:could not resolve theconnect identifier specified; cmd tnsping servername 失败
这个错误是因为安装的时候 oracle gateways的环境变量把oracle server的环境变量给置后了, 只需把环境变量path中oracle server的路径放到前面就可以了
安装前:
path:
E:\oracle\product\10.2.0\tg_1\bin;E:\oracle\product\10.2.0\tg_1/bin;E:\oracle\product\10.2.0\db_1\bin;
修改为:
Path:
E:\oracle\product\10.2.0\db_1\bin;E:\oracle\product\10.2.0\tg_1\bin;E:\oracle\product\10.2.0\tg_1/bin;
*********************************************************************
Oracle 10 g连接db2要借助数据源odbc 因此需要安装驱动, 不过一般情况下 安装了db2客户端以后就会安装db2 驱动.下面就是配置:
第一步 :
分别在ODBC数据源管理器的用户DNS和系统DNS中添加 IBM DB2 ODBC DRIVER – DB2COPY1驱动，数据源名称spph(以我测试为例) 数据库别名：spph
输入用户名 密码 测试连接成功;
第二步:
找到Oracle安装目录下(E:\oracle\product\10.2.0\db_1\hs\admin)的hs\admin文件夹，复制一份inithsodbc.ora文件命名为initspph.ora。打开initspph.ora配置如下参数：
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = spph（注意：确保该参数名称与ODBC中的数据源名称一致）
HS_FDS_TRACE_LEVEL = off


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
第三步:
找到Oracle安装目录下(E:\oracle\product\10.2.0\db_1\network)的network\admin文件夹，配置listener.ora和tnsnames.ora文件。若没有listener.ora，需新建一个。
Listener.ora配置内容：
# listener.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =

(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)

(SID_NAME = spph)

(PROGRAM = hsodbc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.54)(PORT = 1521))
)
)

Tnsnames.ora配置内容：

# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

tt=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.54)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBHOUSE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
spph=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.54)(PORT = 1521))
)
(CONNECT_DATA =
(sid = spph)
)
(hs=ok)
)
第四步：PLSQL连接本地Oracle服务器，创建dblink连接db2的数据库实例DB2ADMIN。
创建语句：
CREATE DATABASE LINK oradb2 connect to "DB2ADMIN" identified by "DB2ADMIN" using ‘spph’;
检查dblink oradb2是否连接成功：
Select * from wh_vad.aa@oradb2;
连接时时报错: 没有select权限 需要用管理员用户赋予查询权限;