現在實際工作中，在不同類型的DB間建立連接非常廣泛，像我們會在ORACLE數據庫中使用INFORMIX數據庫中的資料，也相當於ORACLE內部DBLINK的含義，隻是要從其它類型的數據庫抓取資料，這種’DBLINK’建立起來就有點復雜，下面我們以在ORACLE建立到INFORMIX的LINK為例，列以下簡單步驟：
環境如下：
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 2)
ORACLE: Oracle Database10gEnterpriseEdition Release10.2.0.1.0
INFORMIX: IBM Informix Dynamic Server Version 9.40.FC7
使用INFORMIX驅動組件: clientsdk.2.90.UC4.LINUX.tar
一、首先，在裝有ORACLE DB的服務器上安裝INFORMIX的驅動程式
[oracle@test infor] $mdir /u/infor
[oracle@test infor] $groupadd –g 200 informix
[oracle@test infor] $useradd –g 200 –G Informix –d /u/infor Informix
[oracle@test infor] $chown –R Informix:Informix /u/infor
釋放clientsdk.2.90.UC4.LINUX.tar,並安裝相應程式:
[oracle@test infor]$ ls -l
bin
csdk.ini
doc
gls.rpm
installclientsdk
lib
release
clientsdk.2.90.UC4.LINUX.tar
csdk.jar
etc
hua.tar
installesql
message.rpm
RUN_AS_ROOT.clientsdk
clientsdkcontent.rpm demo gls incl install_rpm msg
[oracle@test infor]$ pwd
/u/infor
二、編輯配置文件/etc/odbc.ini
[oracle@test infor]$ cat /etc/odbc.ini
[Infdrv1]
Driver=/u/infor/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=ds
LogonID=FKPCB
pwd=FKPCB
Servername=on_tcp90
CursorBehavior=0
CLIENT_LOCALE=zh_TW.big5
DB_LOCALE=zh_TW.big5
TRANSLATIONDLL=/u/infor/lib/esql/igo4a304.so
\n
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
\n
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/u/infor
[Infuse1]
Driver=/u/infor/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=ds
LogonID=ODBCMMS
pwd=mms1688
Servername=on_tcp99
CursorBehavior=0
CLIENT_LOCALE=zh_TW.big5
DB_LOCALE=zh_TW.big5
TRANSLATIONDLL=/u/infor/lib/esql/igo4a304.so
\n
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
\n
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/u/infor
(當配置多個INFORMIX服務時，也要配置如下文件:
在/etc/services文件尾部加入以下部分即可
# Local services
on_tcp99_srv 9099/tcp)
三、編輯配置文件/u/infor/etc/sqlhosts
[oracle@test etc]$ cat sqlhosts
#********************************************************************
#
# IBM INC.
#
# PROPRIETARY DATA
#
# Licensed Material - Property Of IBM
#
# "Restricted Materials of IBM"
#
# IBM Informix Client SDK
#
# (c) Copyright IBM Corporation 2002. All rights reserved.
#
# Title: sqlhosts.demo
# Description:
# Default sqlhosts file for running demos.
#
#*******************************************************************
cdr129 group - - i=129
on_tcp129 onsoctcp 10.188.131.183 9088 g=cdr129
on_shm129 onipcshm informix on_shm129_srv g=cdr129
cdr90 group - - i=90
on_tcp90 onsoctcp 10.134.98.73 9090 g=cdr90
on_shm90 onipcshm chnhsdb1 on_shm90_srv g=cdr90
cdr69 group - - i=69
on_tcp69 onsoctcp fklntwn on_tcp69_srv g=cdr69
on_shm69 onipcshm fklntwn on_shm69_srv g=cdr69
cdr190 group - - i=190
on_tcp190 onsoctcp test1 on_tcp190_srv g=cdr190
on_shm190 onipcshm test1 on_shm190_srv g=cdr190
cdr99 group - - i=99
on_tcp99 onsoctcp 10.130.14.15 on_tcp99_srv g=cdr99
on_shm99 onipcshm 10.130.14.15 on_shm99_srv g=cdr99
四、添加相應ODBC驅動進系統
[root@test ~]#cd /etc/ld.so.conf.d Enter
[root@test ld.so.conf.d]# cat informix.conf
/u/infor/lib/esql
[root@test ld.so.conf.d]#/sbin/ldconfig Enter
[root@test ld.so.conf.d]#ldd /u/infor/lib/cli/libifcli.so Enter
五、以上OK後，進行測試，看是否可以從INFORMIX中拉資料
[root@test ld.so.conf.d]# export INFORMIXDIR=/u/infor
[root@test ld.so.conf.d]# export DBCINI=/etc/odbc.ini
[root@test ld.so.conf.d]# isql -v infdrv1
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count(*) from zx_file;
+------------------+
| |
+------------------+
| 1042 |
+------------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit
[root@test ld.so.conf.d]#
六、以ORACLE帳號進入，配置相關ORACLE服務
[oracle@test etc]$ cd $ORACLE_HOME
[oracle@test db_1]$ cd hs
[oracle@test hs]$ cd admin
[oracle@test admin]$ ls
inithsodbc.ora initinfuse01.ora initinfuse02.ora listener.ora.sample tnsnames.ora.sample
[oracle@test admin]$ cat initinfuse01.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 =Infdrv1
HS_FDS_TRACE_LEVEL = hsinf.trc
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
#
# ODBC specific environment variables
#
set DBCINI= /etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
[oracle@test admin]$
[oracle@test db_1]$ cd network
[oracle@test network]$ cd admin
[oracle@test admin]$ ls
listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
[oracle@test admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/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 = /u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=infuse01)
(ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM =hsodbc)
)
(SID_DESC =
(SID_NAME=infuse02)
(ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM =hsodbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.182.4.65)(PORT = 1521))
)
)
[oracle@test admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
HSMINF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.0.00)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = infuse01)
)
(HS = OK)
)
HSNINF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.0.00)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = infuse02)
)
(HS = OK)
)
ERPTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.00.00)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = erptest)
)
)
七、測試網絡連通否
[oracle@test admin]$ tnsping hsminf
TNSPingUtility for Linux: Version10.2.0.1.0 - Production on 21-MAR-2008 10:59:34
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.4.65)(PORT = 1521)) (CONNECT_DATA = (SERV
ICE_NAME = infuse01)) (HS = OK))
OK (0 msec)
[oracle@test admin]$ tnsping hsninf
TNSPingUtility for Linux: Version10.2.0.1.0 - Production on 21-MAR-2008 10:59:39
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.4.65)(PORT = 1521)) (CONNECT_DATA = (SERV
ICE_NAME = infuse02)) (HS = OK))
OK (0 msec)
隨後進行lsnrctl restart.
八、在ORACLE中創建DBLink
create public database link diccyw connect to "dzpptyw" identified by "claimdate" using 'DICCYW';
create public database link ywstat connect to "informix" identified by "password" using 'YWSTAT';
注意：帳號密碼即為INFORMIX中所開的ODBC帳號,
服務名稱為tnsnames.ora文件中自行定的services名稱.
測試LINK的可用性:
select * from zx_file@test
九、以上，在linux系統下配置INFORMIX ODBC的驅動，
通過ORACLE數據庫建立相應LINK服務。
為了安全起見，我們可在ORACLE中新開一使用帳號，
立INFORMIX中個別TABLE的VIEW，然後再把這些VIE
賦權給特定用戶使用.
相關SQL如下:
createorreplaceviewimg_fileas
select"img01","img02","img03","img04","img05","img06","img07","img08","img09","img10","img11","img12","img13","img14","img15","img16","img17","img18","img19","img20","img21","img22","img23","img24","img25","img26","img27","img28","img30","img31","img32","img33","img34","img35","img36","img37"fromimg_file@test;
grant select on img_file to sfc;
select * from scott.img_file;
環境如下：
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 2)
ORACLE: Oracle Database10gEnterpriseEdition Release10.2.0.1.0
INFORMIX: IBM Informix Dynamic Server Version 9.40.FC7
使用INFORMIX驅動組件: clientsdk.2.90.UC4.LINUX.tar
一、首先，在裝有ORACLE DB的服務器上安裝INFORMIX的驅動程式
[oracle@test infor] $mdir /u/infor
[oracle@test infor] $groupadd –g 200 informix
[oracle@test infor] $useradd –g 200 –G Informix –d /u/infor Informix
[oracle@test infor] $chown –R Informix:Informix /u/infor
釋放clientsdk.2.90.UC4.LINUX.tar,並安裝相應程式:
[oracle@test infor]$ ls -l
bin
csdk.ini
doc
gls.rpm
installclientsdk
lib
release
clientsdk.2.90.UC4.LINUX.tar
csdk.jar
etc
hua.tar
installesql
message.rpm
RUN_AS_ROOT.clientsdk
clientsdkcontent.rpm demo gls incl install_rpm msg
[oracle@test infor]$ pwd
/u/infor
二、編輯配置文件/etc/odbc.ini
[oracle@test infor]$ cat /etc/odbc.ini
[Infdrv1]
Driver=/u/infor/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=ds
LogonID=FKPCB
pwd=FKPCB
Servername=on_tcp90
CursorBehavior=0
CLIENT_LOCALE=zh_TW.big5
DB_LOCALE=zh_TW.big5
TRANSLATIONDLL=/u/infor/lib/esql/igo4a304.so
\n
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
\n
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/u/infor
[Infuse1]
Driver=/u/infor/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=ds
LogonID=ODBCMMS
pwd=mms1688
Servername=on_tcp99
CursorBehavior=0
CLIENT_LOCALE=zh_TW.big5
DB_LOCALE=zh_TW.big5
TRANSLATIONDLL=/u/infor/lib/esql/igo4a304.so
\n
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
\n
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/u/infor
(當配置多個INFORMIX服務時，也要配置如下文件:
在/etc/services文件尾部加入以下部分即可
# Local services
on_tcp99_srv 9099/tcp)
三、編輯配置文件/u/infor/etc/sqlhosts
[oracle@test etc]$ cat sqlhosts
#********************************************************************
#
# IBM INC.
#
# PROPRIETARY DATA
#
# Licensed Material - Property Of IBM
#
# "Restricted Materials of IBM"
#
# IBM Informix Client SDK
#
# (c) Copyright IBM Corporation 2002. All rights reserved.
#
# Title: sqlhosts.demo
# Description:
# Default sqlhosts file for running demos.
#
#*******************************************************************
cdr129 group - - i=129
on_tcp129 onsoctcp 10.188.131.183 9088 g=cdr129
on_shm129 onipcshm informix on_shm129_srv g=cdr129
cdr90 group - - i=90
on_tcp90 onsoctcp 10.134.98.73 9090 g=cdr90
on_shm90 onipcshm chnhsdb1 on_shm90_srv g=cdr90
cdr69 group - - i=69
on_tcp69 onsoctcp fklntwn on_tcp69_srv g=cdr69
on_shm69 onipcshm fklntwn on_shm69_srv g=cdr69
cdr190 group - - i=190
on_tcp190 onsoctcp test1 on_tcp190_srv g=cdr190
on_shm190 onipcshm test1 on_shm190_srv g=cdr190
cdr99 group - - i=99
on_tcp99 onsoctcp 10.130.14.15 on_tcp99_srv g=cdr99
on_shm99 onipcshm 10.130.14.15 on_shm99_srv g=cdr99
四、添加相應ODBC驅動進系統
[root@test ~]#cd /etc/ld.so.conf.d Enter
[root@test ld.so.conf.d]# cat informix.conf
/u/infor/lib/esql
[root@test ld.so.conf.d]#/sbin/ldconfig Enter
[root@test ld.so.conf.d]#ldd /u/infor/lib/cli/libifcli.so Enter
五、以上OK後，進行測試，看是否可以從INFORMIX中拉資料
[root@test ld.so.conf.d]# export INFORMIXDIR=/u/infor
[root@test ld.so.conf.d]# export DBCINI=/etc/odbc.ini
[root@test ld.so.conf.d]# isql -v infdrv1
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count(*) from zx_file;
+------------------+
| |
+------------------+
| 1042 |
+------------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit
[root@test ld.so.conf.d]#
六、以ORACLE帳號進入，配置相關ORACLE服務
[oracle@test etc]$ cd $ORACLE_HOME
[oracle@test db_1]$ cd hs
[oracle@test hs]$ cd admin
[oracle@test admin]$ ls
inithsodbc.ora initinfuse01.ora initinfuse02.ora listener.ora.sample tnsnames.ora.sample
[oracle@test admin]$ cat initinfuse01.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 =Infdrv1
HS_FDS_TRACE_LEVEL = hsinf.trc
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
#
# ODBC specific environment variables
#
set DBCINI= /etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
[oracle@test admin]$
[oracle@test db_1]$ cd network
[oracle@test network]$ cd admin
[oracle@test admin]$ ls
listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
[oracle@test admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/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 = /u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=infuse01)
(ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM =hsodbc)
)
(SID_DESC =
(SID_NAME=infuse02)
(ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM =hsodbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.182.4.65)(PORT = 1521))
)
)
[oracle@test admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
HSMINF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.0.00)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = infuse01)
)
(HS = OK)
)
HSNINF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.0.00)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = infuse02)
)
(HS = OK)
)
ERPTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.188.00.00)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = erptest)
)
)
七、測試網絡連通否
[oracle@test admin]$ tnsping hsminf
TNSPingUtility for Linux: Version10.2.0.1.0 - Production on 21-MAR-2008 10:59:34
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.4.65)(PORT = 1521)) (CONNECT_DATA = (SERV
ICE_NAME = infuse01)) (HS = OK))
OK (0 msec)
[oracle@test admin]$ tnsping hsninf
TNSPingUtility for Linux: Version10.2.0.1.0 - Production on 21-MAR-2008 10:59:39
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 10.182.4.65)(PORT = 1521)) (CONNECT_DATA = (SERV
ICE_NAME = infuse02)) (HS = OK))
OK (0 msec)
隨後進行lsnrctl restart.
八、在ORACLE中創建DBLink
create public database link diccyw connect to "dzpptyw" identified by "claimdate" using 'DICCYW';
create public database link ywstat connect to "informix" identified by "password" using 'YWSTAT';
注意：帳號密碼即為INFORMIX中所開的ODBC帳號,
服務名稱為tnsnames.ora文件中自行定的services名稱.
測試LINK的可用性:
select * from zx_file@test
九、以上，在linux系統下配置INFORMIX ODBC的驅動，
通過ORACLE數據庫建立相應LINK服務。
為了安全起見，我們可在ORACLE中新開一使用帳號，
立INFORMIX中個別TABLE的VIEW，然後再把這些VIE
賦權給特定用戶使用.
相關SQL如下:
createorreplaceviewimg_fileas
select"img01","img02","img03","img04","img05","img06","img07","img08","img09","img10","img11","img12","img13","img14","img15","img16","img17","img18","img19","img20","img21","img22","img23","img24","img25","img26","img27","img28","img30","img31","img32","img33","img34","img35","img36","img37"fromimg_file@test;
grant select on img_file to sfc;
select * from scott.img_file;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
