LINUX下ORACLE数据库建立INFORMIX的DBLINK的步骤

LINUX下ORACLE数据库建立INFORMIX的DBLINK的步骤 現在實際工作中，在不同類型的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 # # 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 va # [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 "dz ppt 关于艾滋病ppt课件精益管理ppt下载地图下载ppt可编辑假如ppt教学课件下载triz基础知识ppt yw" 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;