Oracle dblink to DB2(이기종 DB 연결)
Oracle database 에서 IBM DB2로의 연결이 필요하여 테스트 한 내용입니다.
Oracle Database Gateway for ODBC를 이용하여 이기종간의 DB 연결
테스트 환경 : oracle Oracle Linux Server release 6.10
oracle 12.2 GRID 환경
DB2 10.5 express-c
PostgreSQL 11.7(eXperDB)
구성절차.
1. unixODBC driver master 설치
2. ODBC driver for DB2 설치
3. Gateway 파라메터 설정
4. Oracle LISTENER 설정
5. tnsnames.ora 파일 설정
6. LISTENER 시작
7. DB Link 생성
테스트중에 문제된 부분은
1. Gateway parameter 설정
$ORACLE_HOME/hs/admin/initdb4odbc.ora 설정
HS_FDS_CONNECT_INFO = DG4DB2
HS_FDS_TRACE_LEVEL=255
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR=UCS2
set ODBCINI=/etc/odbc.ini
2. Grid 환경에서 listener 를 $ORACLE_HOME or $GRID_HOME 중에 어디에 기동해야 하느냐하는 부분
둘중에 어느곳에 설정해도 문제 없었음.
GI 에서 netca 및 netmgr 로 구성
or
srvctl add listener -listener LISTENER_DG4DB2 -p 1588 로 추가 및 변경
--환경설정 부분
[oracle@oradb1 etc]$ cat /etc/odbc.ini
[[ODBC Data Source]
DG4DB2=IBM DB2 ODBC DRIVER
[DG4DB2]
Driver=/u01/app/oracle/unixODBC/DB2/odbc_cli/clidriver/lib/libdb2.so
Description=MYDB2 DB2 ODBC Driver
[PGSQL]
Driver = /usr/pgsql-11/lib/psqlodbc.so
Description=PostgreSQL ODBC Driver
Servername = 192.168.56.102
Port = 5432
Database=experdb
/etc/odbcinst.init 파일 설정 필요없음.
[root@oradb1 cfg]# cat db2cli.ini
[COMMON]
Trace=255
TracePathName=/u01/app/oracle/unixODBC/DB2/odbc_cli/clidriver/trace
TraceComm=1
TraceFlush=1
TraceTimeStamp=1
[DG4DB2]
Database=mydb2
Hostname=192.168.1.169
Port=50000
Protocol=TCPIP
--listener.ora
--$ORACLE_HOME
LISTENER_DG4DB2=
(DESCRIPTION_LIST=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb1)(port=1588)))
)
SID_LIST_LISTENER_DG4DB2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = DG4DB2)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome1)
(PROGRAM = dg4odbc)
)
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = PGSQL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome1)
)
)
ADR_BASE_LISTENER_DG4DB2 = /u01/app/oracle
--tnsnames.ora
DG4DB2=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=oradb1)(port=1588))
(CONNECT_DATA =
(SID = DG4DB2)
)
(HS=OK)
)
PGSQL=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=oradb1)(port=1588))
(CONNECT_DATA =
(SID = DG4DB2)
)
(HS=OK)
)
SQL> select * from "test"@dbl_pgsql ;
id name
---------- ------------------------------------------------------------
1001 user1001
1002 user1002
SQL> select * from test@dbl_pgsql ;
select * from test@dbl_pgsql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: relation "TEST" does not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: preceding 3 lines from DBL_PGSQL
SQL> select * from test@dbl_db2 ;
ID
----------
1