Konfigurasi Unix ODBC dan Freetds dan Koneksi Oracle ke MS SQL Server

 

Unix ODBC dibutuhkan untuk koneksi ke beberapa database dengan berbasis linux.

 

Freetds sendiri adalah driver generik yang gratis, untuk membuat koneksi ODBC ke MS SQL Server. 

 

Platform :

 

-          RHEL 4

 

-          Oracle Database (include di APPS)

 

-          MS SQL Server Express 2005

 

 

 

Package unix yang di butuhkan :

 

-          unixODBC (http://www.unixodbc.org/)

 

-          FreeTDS  (http://freetds.org/)

 

 

 

I.        Install unixODBC package

 

# tar -xvzf unixODBC-2.2.12.tar.gz

 

# ./configure –prefix=/usr/local –enable-gui=no

 

# make

 

# make install

 

 

 

II.     Install freeTDS package

 

# tar -xvzf freetds-stable.tgz

 

# ./configure –with-tdsver=8.0 –with-unixODBC=/usr/local

 

# make

 

#make install

 

 

 

III.   Konfigurasi freeTDS

 

Tambahkan di baris paling bawah di file freetds.conf

 

[sqlexpresstds]

 

host = 192.168.1.1

 

port = 1433

 

tds version = 7.0

 

client charset = UTF-8

 

note : untuk port disesuaikan dengan port IP address di SQL Servernya dan pastikan enginenya run serta ip address enabled = yes

 

 

 

IV.    Konfigurasi unixODBC

 

a.      Tambahkan di file odbcinst.ini

 

########################

 

[FreeTDS]

 

Description=v0.63 with protocol v8.0

 

Driver=/usr/local/lib/libtdsodbc.so

 

Setup=/usr/local/lib/libtdsodbc.so

 

Trace=Yes

 

TraceFile=/tmp/freetds.log

 

FileUsage=1

 

 

 

[ODBC]

 

Trace                   = Yes

 

TraceFile               = /tmp/sql.log

 

ForceTrace              = Yes

 

Pooling                 = No

 

########################

 

 

 

note: file system yang diatas harus bisa diakses oleh group user

DBA

oracle

 

 

 

b.      Tambahkan di file odbc.ini

 

########################

 

[ODBC Data Sources]

 

sqltest = Sqlserver 2000 connection (ISIDB)

 

 

 

[sqlserverDB]

 

Driver      = FreeTDS

 

Description = Test Server

 

Trace       = Yes

 

Database    = master

 

TraceFile   = /tmp/sql.log

 

Servername  = sqlexpresstds

 

Port        = 1433

 

########################

 

 

 

V.      Tes koneksi menggunakan

 

        # isql -v sqlserverDB [user] [password]

        +---------------------------------------+

 

        | Connected!                       |

 

        |                                        |

 

        | sql-statement                    |

 

        | help [tablename]               |

 

        | quit                                 |

 

        |                                       |

 

        +---------------------------------------+

 

        SQL> select * from "sysObjects";

 

 

 

VI.    Konfigurasi heterogeneous service (hsodbc) agar bisa konek dari oracle ke MS SQL 

 

a.      Buat file init<sid>.ora  -> inithsodbc.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 = sqlserverDB

 

HS_FDS_TRACE_LEVEL = 4

 

HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc

 

HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

 

#

 

# ODBC specific environment variables

 

#

 

set ODBCINI=/etc/odbc.ini

 

#

 

# Environment variables required for the non-Oracle system

 

#

 

#set <envvar>=<value>

 

 

 

note : isi HS_FDS_CONNECT_INFO

dengan nama DNS contoh di atas adalah sqlserverDB, file system yang diatas harus bisa diakses oleh group user DBA oracle

 

 

 

b.      Simpan file di direktori $ORACLE_HOME/hs/admin 

 

c.       Edit oracle listener $ORACLE_HOME/network/admin

 

DEV =

 

  (ADDRESS_LIST =

 

        (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCHRDEV))

 

        (ADDRESS= (PROTOCOL=TCP)(Host= oracledbserver )(Port= 1521))

 

  )

 

 SID_LIST_ DEV =

 

  (SID_LIST =

 

    (SID_DESC =

 

      (ORACLE_HOME= /dev/db01/devdb/10.2.0)

 

      (SID_NAME = DEV)

 

    )

 

    (SID_DESC =

 

      (SID_NAME = PLSExtProc)

 

      (ORACLE_HOME = /dev/db01/devdb/10.2.0)

 

      (PROGRAM = extproc)

 

    )

 

    (SID_DESC =

 

      (PROGRAM = hsodbc)

 

      (ORACLE_HOME= /dev/db01/devdb/10.2.0)

 

      (SID_NAME =hsodbc)

 

      (ENVS="LD_LIBRARY_PATH = /lib:/usr/local/lib:/dev/db01/devdb/10.2.0/lib")

 

    )

 

  )

 

 

 

d.      Restart listener 

 

# lsnrctl stop DEV

 

# lsnrctl start DEV

 

 

 

e.      Edit oracle TNSNAME $ORACLE_HOME/network/admin

Tambahkan tnsname untuk hsodbc

 

hsodbc =

 

  (DESCRIPTION=

 

                  (ADDRESS=(PROTOCOL=tcp)(HOST= oracledbserver)(PORT=1521))

 

                  (CONNECT_DATA=(SID=hsodbc))

 

      (HS=OK)

 

  )

 

 

 

f.        Test TNSNAME

 

# tnsping hsodbc

 

 

 

g.      Buat DBLINK 

 

SQL> create database link hsodbc connect to user identified by password using 'HSODBC';

 

 

 

h.      Jalankan SQL ke SQL Server

 

SQL> select * from "SQLDB.dbo.[nama table]"@hsodbc;

 

 

 

 

 

Add comment


Security code
Refresh

Joomla templates by a4joomla