[DB]CentOS UnixODBC 설정 방법

 수동설치

1. http://www.unixodbc.org/

odbc 설치

 

2. tar xzvf unixODBC-2.3.7.tar.gz

 

3. cd unixODBC-2.3.7/

 

4. ./configure

 

5. make

 

6. make install

 

7. isql

 

 

자동

===선택===

#RedHat Enterprise Server 6

curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

 

#RedHat Enterprise Server 7

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

 

#RedHat Enterprise Server 8 and Oracle Linux 8

curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo

===선택===

 

1. yum install -y mysql-connector-odbc

**epel-release

==============================

2. yum install msodbcsql17 ===선택===

3. yum install mssql-tools ===선택===

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

============================

2. yum install freetds unixODBC-devel

**yum install -y unixODBC

sudo yum install unixODBC-devel

 

odbcinst -q -s 는 등록된 odbc.ini 목록을 보여준다(사용자)

odbcinst -q -d 는 등록된 odbcinst.ini 목록을 보여준다.(드라이버)

odbcinst -j ini 파일 등록 위치를 보여준다.

 

3. unixODBC 세팅

 

# vi /etc/odbcinst.ini

 

[FreeTDS]

Description = FreeTDS Driver

Driver = /usr/lib64/libtdsodbc.so.0

Setup = /usr/lib64/libtdsS.so.2

FileUsage = 1

client charset = utf-8

 

6. /etc/odbc.ini에 내용 추가.

[mssqlserver]

driver=FreeTDS

server=192.168.100.62 --> SQL Server's IP

port=1433 --> SQL Server 에서 사용하는 Port

database=test --> SQL Server에 있는 Database 중 연결하려는 Database Name

tds_version = 8.0

user = sa

password = 1q2w3e4r!Q

 

========================

- /etc/odbc.ini 에 작성한 내용을 기반으로 아래 내용을 자신의 환경에 맞게 작성한다.

 

#!/usr/bin/perl

use DBI;

my @dsns = DBI->data_sources('ODBC');

foreach my $d (@dsns)

{

print "$d\n";

}

my $dbh = DBI-> connect('dbi:ODBC:DB2016', "dymoon", "dymoon00");

my $sql = "SELECT count(*) FROM [DB2016Monitoring].[MonitorData].[Session]";

my $sth = $dbh->prepare($sql); $sth->execute();

while ( @first = $sth->fetchrow_array ) {

foreach $field (@first) {

print "field: $field\n";

}

}

$dbh->disconnect();

========================

 

7. vi /etc/freetds.conf

# A typical Microsoft server

[mssqlserver] ==

host = WIN-6T245D9UFU4

port = 1433

tds version = 7.4

 

8. isql -v mssqlserver sa '1q2w3e4r!Q'

 

 

 

 

신규

=================================================

 

Linux odbc / mssql mysql oracle 설치 및 환경 설정

sudo yum -y install freetds mysql-connector-odbc glibc-devel.i686 unixODBC-devel.i686 glibc-devel.x86_64 unixODBC-devel

yum groupinstall “Development Tools”

 

// oracle odbc 드라이버 설치

(64bit일경우, 다운로드 : http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html)

sudo rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

sudo rpm -ivh oracle-instantclient11.2-odbc-11.2.0.3.0-1.x86_64.rpm

 

// /etc/freetds.conf 설정 파일 수정

# A typical Microsoft server

[mssqlserver]

host = 192.168.10.135

port = 1433

tds version = 7.4

client charset = utf-8

 

//oracle Configure init 생성

 

vi $ORACLE_HOME/hs/admin/initmssqlserver.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 = mssqlserver

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9

set ODBCINI=/etc/odbc.ini

 

vi $ORACLE_HOME/hs/admin/initmysqlserver.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 = mysqlserver <-- [해당부분은 접속 하고자하는 MySQL DB 명 입력]

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so <-- [사용하고자 하는 ODBC 드라이버]

HS_FDS_TRACE_LEVEL = OFF <--[접속은 되는데 결과값이 안나온다면 255 로 설정해 trace 확인]

HS_FDS_SUPPORT_STATISTICS = FALSE

HS_LANGUAGE=AMERICAN_AMERICA.KO16MSWIN949 <--[Oracle 서버쪽 DB의 캐릭터셋]

HS_RPC_FETCH_REBLOCKING = OFF

HS_KEEP_REMOTE_COLUMN_SIZE = ALL

HS_NLS_LENGTH_SEMANTICS = VARCHAR <-- [VARCHAR or CHAR 등으로 환경에 맞게 설정 가]

HS_FDS_TIMESTAMP_MAPPING = DATE

HS_NLS_NCHAR=UCS2

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

# Environment variables required for the non-Oracle system

#

 

 

//oracle Configure Listener 추가 설정

vi $ORACLE_HOME/network/admin/listener.ora

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME=mysqlserver)

(ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM=/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc)

(ENVS=LD_LIBRARY_PATH = /usr/lib:/usr/lib64:/oracle/product/11.2.0/db_1/lib:/oracle/product/11.2.0/db_1/hs/lib)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME=mssqlserver)

(ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM=/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc)

(ENVS=LD_LIBRARY_PATH = /usr/lib:/usr/lib64:/oracle/product/11.2.0/db_1/lib:/oracle/product/11.2.0/db_1/hs/lib)

)

)

 

 

//oracle Configure tnsnames 추가 설정

vi $ORACLE_HOME/network/admin/tnsnames.ora

 

mssqlserver =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA = (SID = mssqlserver)

)

(HS = OK)

)

mysqlserver =

(DESCRIPTION =

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

(CONNECT_DATA = (SID = mysqlserver)

)

(HS = OK)

)

 

// odbcinst.ini 및 odbc.ini 설정파일 위치 파악

odbcinst -j

 

// odbc driver 설정

# vi /etc/odbcinst.ini

 

# Driver from the mysql-connector-odbc package

# Setup from the unixODBC package

[MySQL ODBC 5.3 Driver]

Description = ODBC for MySQL

Driver = /usr/lib/libmyodbc5.so

Setup = /usr/lib/libodbcmyS.so

Driver64 = /usr/lib64/libmyodbc5.so

Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

 

[FreeTDS]

Description = FreeTDS Driver

Driver = /usr/lib64/libtdsodbc.so.0

Setup = /usr/lib64/libtdsS.so.2

FileUsage = 1

client charset = utf-8

 

[OracleODBC-11g]

Description = Oracle ODBC driver for Oracle 11g

Driver = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

FileUsage = 1

Driver Logging = 7

 

// DSN 설정하기

vi /etc/odbc.ini

 

[mssqlserver]

Driver = FreeTDS

Server = 192.168.10.135

Port = 1433

TDS version = 7.4

USER = sa

Password = 1q2w3e4r!Q

tabase = test

 

[oracle]

Description=ODBC for oracle client 64

Driver=OracleODBC-11g

Server = 192.168.10.134

USER = testuser

Password = 1q2w3e4r!Q

 

[mysqlserver]

Driver = MySQL ODBC 5.3 Driver

Description = Mysql

SERVER = 192.168.10.110

Port = 3306

USER = root

Password = 1q2w3e4r!Q

 

// odbc driver 파악

odbcinst -q -d

 

// odbc dsn 파악

odbcinst -q -s

 

// isql -v oracle 에러 권한 777 변경

chmod 777 -R /usr/lib/oracle

ln -s /usr/lib64/libodbcinst.so.2 /usr/lib64/libodbcinst.so.1

 

lsnrctl start

 

tnsping mysqlserver

tnsping mssqlserver

 

// isql 로 접속해보기

isql oracle userid password

 

// sqlplus 로 접속해보기(TWO_TASK로 접속대상을 설정해서 호스트는 생략)

sqlplus userid/password

 

// oracle Create DbLink

create database link mssqlserver connect to "sa" IDENTIFIED BY "1q2w3e4r!Q" USING 'mssqlserver';

create database link mysqlserver connect to "root" IDENTIFIED BY "1q2w3e4r!Q" USING 'mysqlserver';