Install and Configure DB Link to SQL Server Database in Linux
This document provides step by step instructions to install IBM DB2 Driver 9.1 for ODBC and CLI for Linux 64bit
Prerequisites:
· Oracle Database Gateway for ODBC is installed
· unixODBC is installed
# rpm -q unixODBC
unixODBC-2.2.11-7.1
unixODBC-2.2.11-7.1
# rpm -q unixODBC-devel
unixODBC-devel-2.2.11-7.1
unixODBC-devel-2.2.11-7.1
Installing FreeTDS ODBC Driver for SQL Server
1. Login as su user
# su –
2. Create the directory /usr/local/freetds, where you will install the driver.
# mkdir –p / # mkdir /usr/local/freetds
3. Locate the compressed file that contains the driver / /u01/install/Linux-64_11gR1/SQL_Server_ODBC_Driver/ freetds-stable.tar
4. Copy v9fp6a_linuxx64_db2driver_for_odbc_cli.tar to , /u01/app/orcbin/db2_odbc_driver.
# cp /u01/install/Linux-64_11gR1/SQL_Server_ODBC_Driver/freetds-stable.tar .
5. Unpack freetds_stable.tar:
# tar -xvf freetds-stable.tar
6. Install freetds
# cd freetds-0.82
# ./configure --prefix=/usr/local/freetds --with-tdsver=8.0
#
7. Make
# make; make install; make clean
Configuring FreeTDS Driver for ODBC
1. Configure ODBC.ini
$ su – root
# vi /etc/odbc.ini
[ODBC Data Sources]
MYDB2=IBM DB2 ODBC DRIVER
SQLDB=FreeTDS
[SQLDB]
Driver = /usr/local/freetds/lib/libtdsodbc.so
#Driver = /usr/lib64/libodbc.so
Server = dbSQLDB.gotodba.com
Database = SQLDB
Port = 1433
TDS_Version = 8.0
2. Test SQL Server ODBC connectivity
# isql -v SQLDB userid password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
Configuring Oracle Database Gateway for ODBC
http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31042/configodbc.htm#CIHFEGDA
1. Configure the Gateway Initialization Parameter File. For Oracle RAC database ORCL, the initial parameter GLOBAL_NAMES = true, DB_Domain = gotodba.com, which enforce that database links have same name as remote database. So in the initSQLDB.ora, HS_DB_NAME and HS_DB_DOMAIN parameters have to be set.
# su – oracle
$ cd $ORACLE_HOME/hs/admin
$ cp initdg4odbc.ora initSQLDB.ora
$ vi initSQLDB.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 = SQLDB
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_DB_NAME = SQLDB
HS_DB_DOMAIN = GOTODBA.COM
HS_FDS_SUPPORT_STATISTICS = FALSE
#
# ODBC specific environment variables
#
set ODBCINI = /etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
Note:
Need to set
HS_FDS_SUPPORT_STATISTICS = FALSE
Check Metsalink Doc. ID: 744636.1 for
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[FreeTDS][SQL Server]Invalid cursor state
ORA-02063: preceding 2 lines from DG4ODBC
2. Configure Oracle Net for the Gateway
$ cd $ORACLE_HOME/network/admin
$ vi listener.ora
# Append the following lines
SID_LIST_LISTENER_TWNYCSLD04=
(SID_LIST=
(SID_DESC=
(SID_NAME = MYDB2)
(ORACLE_HOME = /u01/app/orcbin/oracle/product/11.1.0/db_1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/u01/app/orcbin/db2_odbc_driver/clidriver/lib:/u01/app/orcbin/oracle/product/11.1.0/db_1/lib)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/orcbin/oracle/product/11.1.0/db_1)
(SID_NAME = SQLDB)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/usr/lib64:/usr/local/freetds/lib:/u01/app/orcbin/oracle/product/11.1.0/db_1/lib)
)
)
$ vi tnsname.ora
# Append the following lines
SQLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = nycdb1-vip.gotodba.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = nycdb2-vip.gotodba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SQLDB)
)
(HS = OK)
)
3. Restart Listner
$lsnrctl reload
Create DB Link in the Oracle database
Login to the database as spdc,
CREATE DATABASE LINK SQLDB
CONNECT TO userid
IDENTIFIED BY password
USING 'SQLDB';
Note:
Oracle RAC database ORCL, the initial parameter GLOBAL_NAMES = true, DB_Domain = gotodba.com, the above command will create db link SQLDB.GOTODBA.COM, the db link name has to match initSQLDB.ora HS_DB_NAME. HS_DB_DOMAIN