July 17 2014

linux下odbc透明网关连接db2和sqlserver

Linux下oracle使用odbc做dblink连接db2

Os用配置说明:
Db2  所在机器操作系统window   ip: 192.168.88.5   版本9.5 库名:TOOLSDB  用户名及密码:Administrator/ucjmh
ORACLE   所在机器操作系统 linux5  ip  192.168.88.129  版本11g 用户名及密码 root/oracle oracle/oracle

 
思路如下:
  Oracle所在机器安装db2的client连接到db2所在的机器 使用db2client文件做odbc的源 然后 oracle装透明网关的odbc包 然后建dblink 然后成功
主机名及操作系统:
[root@rm ~]# uname -a
Linux rm 2.6.18-274.el5 #1 SMP Fri Jul 8 17:36:59 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

解压linux下db2的client安装包
root@rm ~]# cd /u01/soft/
[root@rm soft]# ls
linux  linux.x64_11gR2_gateways  v9.7_linuxx64_server.tar.gz
[root@rm soft]# tar -zxvf v9.7_linuxx64_server.tar.gz

装oracle geteways
[root@rm ~]# cd /u01/soft/
[root@rm soft]# ls
linux  linux.x64_11gR2_gateways  server  v9.7_linuxx64_server.tar.gz
[root@rm soft]# chown -R oracle:oinstall linux.x64_11gR2_gateways/
[root@rm soft]# chmod -R 777 linux.x64_11gR2_gateways/
xhost +
cd /u01/soft/linux.x64_11gR2_gateways
./runInstall
 

 
[root@rm ~]# cd /u01/app/oracle/product/11.2.0/tg_1/
[root@rm tg_1]# ./root.sh
Running Oracle 11g root.sh script…

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/tg_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file “oraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file “coraenv” already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@rm tg_1]#
透明网关安装完成  接下来安装 db2客户端再安装odbc 驱动 三个没有先后关系
[root@rm server]# pwd
/u01/soft/server
[root@rm server]# ./db2_install
 
Default directory for installation of products – /opt/ibm/db2/V9.7

***********************************************************
Do you want to choose a different directory to install [yes/no] ?
no
 
 
Specify one of the following keywords to install DB2 products.

  ESE
  CONSV
  WSE
  EXP
  PE
  CLIENT
  RTCL
 
Enter “help” to redisplay product names.

Enter “quit” to exit.

***********************************************************
CLIENT

Total number of tasks to be performed: 30—等这30个安装完就好了

For more information see the DB2 installation log at
“/tmp/db2_install.log.5407”.
装好了
[root@rm server]# cd /opt/ibm/db2/V9.7/instance/
[root@rm instance]# ./db2icrt -a server -s client oracle gpadmin
DBI1070I  Program db2icrt completed successfully.
[root@rm instance]# su – oracle
[oracle@rm ~]$ db2 catalog tcpip node kjssh remote 192.168.88.5 server 50000 ostype linux
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W 

[oracle@rm ~]$ db2 catalog db toolsdb as db2 at node kjssh
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

 

[oracle@rm ~]$ db2 connect to db2 user administrator using ucjmh  用户名和密码

   Database Connection Information

 Database server        = DB2/NT64 9.1.9
 SQL authorization ID   = ADMINIST…
 Local database alias   = DB2

[oracle@rm ~]$ db2 list database directory  查看一下连上了没有 

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = DB2
 Database name                        = TOOLSDB
 Node name                            = KJSSH
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

 

[root@rm Server]# rpm  -ivh unixODBC-*  –nodeps –force 强制安装 或者用下面的yum
[root@rm ~]# cd /u01/
[root@rm u01]# mkdir iso
[root@rm u01]# cd iso/
[root@rm iso]# mkdir server
[root@rm ~]# cd /etc/
[root@rm etc]# cd  yum.repos.d/
[root@rm yum.repos.d]# cp  rhel-debuginfo.repo  rhel-debuginfo.repo.bak
[root@rm yum.repos.d]# vi rhel-debuginfo.repo
[root@rm yum.repos.d]# more rhel-debuginfo.repo
[rhel]
name=Red Hat Enterprise Linux
baseurl=file:///u01/iso/server
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
[root@rm yum.repos.d]# mount /dev/cdrom /u01/iso/server/
mount: block device /dev/cdrom is write-protected, mounting read-only
[root@rm yum.repos.d]# mount /dev/cd
yum install -y unixODBC
[root@rm yum.repos.d]# su – oracle
[oracle@rm ~]$ odbcinst -j
unixODBC 2.2.11
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/oracle/.odbc.ini

 

Su – root

[root@rm ~]# vi /etc/odbcinst.ini
[root@rm ~]# cat /etc/odbcinst.ini
# Example driver definitinions
#
#

# Included in the unixODBC package
[DB2]
Description     = IBM DB2 ODBC driver (64-bit)
Driver          = /opt/ibm/db2/V9.7/lib32/libdb2o.so
Driver64        = /opt/ibm/db2/V9.7/lib64/libdb2o.so
FileUsage       = 3
DontDLClose     = 1
[root@rm etc]# vi /etc/odbc.ini

[db2]

Description = IBMDB2 ODBC data source
Driver      = DB2
DBALIAS     = dg4odbc
uid         = administrator
pwd         = db2
DMEnvAttr   = SQL_ATTR_UNIXODBC_ENVATTR={DB2_CLI_DRIVER_INSTALL_PATH=/opt/ibm/db2/V9.7/}

 

 

[oracle@rm ~]$ cd $ORACLE_HOME
[oracle@rm db_1]$ cd ..
[oracle@rm 11.2.0]$ cd tg_1/hs/admin
[oracle@rm admin]$ cp initdg4odbc.ora initdg4odbc.ora.bak
[oracle@rm admin]$ cat initdg4odbc.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = db2
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /opt/ibm/db2/V9.7/lib64/libdb2o.so

#
# ODBC specific environment variables
#
#set ODBCINI=<full path name of the odbc initilization file>
set ODBCINI=/etc

#
# Environment variables required for the non-Oracle system
#
set <envvar>=<value>
[oracle@rm admin]$ vi initdg4odbc.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = db2
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /opt/ibm/db2/V9.7/lib64/libdb2o.so

#
# ODBC specific environment variables
#
#set ODBCINI=<full path name of the odbc initilization file>
 set ODBCINI=/etc

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
~

 

 

[oracle@rm admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rm)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/tg_1)
         (PROGRAM=dg4odbc)
      )
  )
ADR_BASE_LISTENER = /u01/app/oracle

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
“listener.ora” 19L, 483C written                                                                  
[oracle@rm admin]$ cp /u01/app/oracle/product/11.2.0/tg_1/hs/admin/tnsnames.ora.sample tnsnames.ora
[oracle@rm admin]$ isql -v db2 administrator ucjmh
+—————————————+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |

 

高大上的 先db2连db2 成功了 证明db2客户端和一些基础配置没有问题

 

SQL> CREATE  DATABASE LINK db2  CONNECT TO administrator IDENTIFIED BY “ucjmh” USING ‘DG4ODBC’;
Database link created.
SQL> SELECT * FROM SYSTOOLS.”ID_TABLE”@db2;

        ID
———-
       391



Copyright 2019. All rights reserved.

Posted 2014年7月17日 by ucjmh in category "db2", "oracle", "sqlserver

Leave a Reply

Your email address will not be published. Required fields are marked *