透明网关 通过 ODBC 连接到 GP(11.2.0.4——>greemplum)

透明网关 --- >从 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
                   

 
 

                    

参考:

https://docs.oracle.com/middleware/12211/biee/BIEMG/GUID-C03C12DB-50AA-4ED5-B5B0-A32EA0E73D92.htm#datadirect_sybase