透明网关 --- >从 Oracle 通过DB Link的形式连接到异构数据库
解压安装
tar -zxvf PROGRESS_DATADIRECT_CONNECT64_ODBC_7.1.6.HOTFIX_LINUX_64.tar.Z
安装驱动
ksh unixmi.ksh
其中
Name : 1
Company : 1
Serial Number : 1076681984
Key : 1076681984
自定义tmp和安装目录
进入安装目录
vi odbc.ini
########################################################################
[ODBC Data Sources]
gpodbc=DataDirect 7.1 Greenplum Wire Protocol
gpnew=DataDirect 7.1 Greenplum Wire Protocol
[ODBC]
InstallDir=/opt/app/ora11g/Connect64_for_ODBC_71b
TraceFile=/opt/app/ora11g/Connect64_for_ODBC_71b/trace.log
TraceDll=/opt/app/ora11g/Connect64_for_ODBC_71b/ddtrc27.so
Trace=0
TraceAutoStop=0
[gpodbc]
InstallDir=/opt/app/ora11g/Connect64_for_ODBC_71b
Driver=/opt/app/ora11g/Connect64_for_ODBC_71b/ddgplm27.so
Description=DataDirect 7.1 Greenplum Wire Protocol
AlternateServers=
IANAAppCodePage=113
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=DW_DATA
DefaultLongDataBuffLen=2048
EnableDescribeParam=0
EnableKeysetCursors=0
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=10.5.185.111
InitializationString=
KeyPassword=
KeysetCursorOptions=0
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=5432
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
XMLDescribeType=-10
[gpnew]
InstallDir=/opt/app/ora11g/Connect64_for_ODBC_71b
Driver=/opt/app/ora11g/Connect64_for_ODBC_71b/ddgplm27.so
Description=DataDirect 7.1 Greenplum Wire Protocol
AlternateServers=
IANAAppCodePage=113
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=DW_DATA
DefaultLongDataBuffLen=2048
EnableDescribeParam=0
EnableKeysetCursors=0
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=192.168.56.22
InitializationString=
KeyPassword=
KeysetCursorOptions=0
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=5432
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
XMLDescribeType=-10
###########################################
使用环境变量
source odbc.sh
验证驱动
./ddtestlib ddgplm27.so
测试连通性
/sample/example/example
配置hs
cd $ORACLE_HOME/hs
vi initgpodbc
# 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 = gpodbc(数据源名称)
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME =//opt/app/ora11g/Connect64_for_ODBC_71/libodbc.so(驱动)
###之前错误的是这么写的/opt/app/ora11g/Connect64_for_ODBC_71b/ddgplm27.so(驱动)
#
# ODBC specific environment variables
#
set ODBCINI=/opt/app/ora11g/Connect64_for_ODBC_71b/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
配置监听
LISTENGPNEW =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1527)))
SID_LIST_LISTENGPNEW=
(SID_LIST=
(SID_DESC=
(SID_NAME=gpodbc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LIBPATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/opt/app/ora11g/Connect64_for_ODBC_71b/lib)
)
)
Oracle用户
tnsnames.ora增加:
gpodbc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)
(PORT = 1527))
)
(CONNECT_DATA =
(SID= gpodbc))
(HS=OK)
)
创建dblink
create database link gp_odbc
connect to "test" identified by "test"
using 'gpodbc';
SQL> select count(*) from "dss_pstat"."ref_area"@gp_new;
COUNT(*)
--------------------
9
参考: