db2 for iseries에 dg4odbc로 연결하기

이거 보고 광고 한번 안 누른 이는 삼대가 재수가 없을지어다!ㅋㅋ

그림 1

 

Oracle GateWay for odbc 설정하기.docx

다운로드

 

Gateway용 서버에 win64_11gR2_gateways 및 db2 for iSeries용 odbc driver 설치

 

1.odbc datasource

system dsn name = db2

driver iSeries Access ODBC Driver

select only check,naming rule sql,object description type sql object comments

 

2.initdb2.ora ( initSID.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

#

# Environment variables required for the non-Oracle system

#

#set =

 

3.listener.ora

LISTENER =

 (ADDRESS_LIST=

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

 )

 

SID_LIST_LISTENER=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=db2)

         (ORACLE_HOME=C:\product\11.2.0\tg_1)

         (PROGRAM=dg4odbc)

      )

  )

 

#CONNECT_TIMEOUT_LISTENER = 0 

 

4.tnsnames.ora

db2gw  =

  (DESCRIPTION=

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

    (CONNECT_DATA=(SID=db2))

    (HS=OK)

  ) 

※gateway hardware에서 oracle 미사용시 없어도 됨.

 

 

Client용 서버에서 (db서버)

 

1.TNS추가

 

db2gw =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db2)

    )

    (HS=OK)

  )

 

 

2.DB링크생성

 

1) CREATE DATABASE LINK db2

 CONNECT TO USER

 IDENTIFIED BY PWD

 USING 'db2gw';

또는

2) CREATE DATABASE LINK db2

 CONNECT TO USER

 IDENTIFIED BY <PWD>

 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=IPADDR)(PORT=1521))(CONNECT_DATA=(SID=db2))(HS=OK))';

 2)의 경우 TNS필요없음

 

간단한 SQL ETL에 사용하였습니다.