Step by step to configure Oracle Heterogeneous Connectivity with 10g and DB2

Step by step to configure Oracle Heterogeneous Connectivity with 10g and DB2

1. Create a ODBC named DB2_ODBC [Note: The DSN must be system not user]

Go to start-->Settings-->Control Panel-->Administrative Tools

Then Double click on Data Sources (ODBC) and create System DSN.

 

 

-----------------------------------------------------------------------------

2. Modify C:\Oracle\product\10.2.0\db_1\hs\admin\initDB2_ODBC.ORA as follows:

# 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 =DB2_ODBC

HS_FDS_TRACE_LEVEL = off

set ODBCINI= C:\WINDOWS\odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set =

---------------------------------------------------------------------------------

3. Modify C:\Oracle\product\10.2.0\db_1\network\admin\LISTENER.ORA as follows:

SID_LIST_LISTENER_HS =

  (SID_LIST =

       (SID_DESC=

       (SID_NAME = db2_odbc)

       (ORACLE_HOME = C:\Oracle\product\10.2.0\db_1)

       (PROGRAM = hsodbc)

    )

  )

LISTENER_HS =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.129)(PORT = 1522))

  )

---------------------------------------------------------------------------------

4. Modify C:\Oracle\product\10.2.0\db_1\network\admin\tnsnames.ora as follows:

# TNSNAMES.ORA Network Configuration File: C:\Oracle\product\10.2.0\db_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

#hsagent  =

#  (DESCRIPTION=

#    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

#    (CONNECT_DATA=(SID=hsagent))

#    (HS=)

#  )

DB2_ODBC.BD.BRACBANK.COM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = tcp)(HOST = 10.100.1.129)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SID = DB2_ODBC)

    )

    (HS = OK)

  )

-----------------------------------------------------------------------------

5. Create Database Link  as follows:

create public Database link DB2_ODBC

connect to "db2admin" identified by "db2admin" using 'DB2_ODBC';

select * from db2admin.SAID@DB2_ODBC

where rownum<7 o:p="">

create table test as select * from db2admin.SAID@DB2_ODBC;

---------------------------------------------------------------------------------

Do not forget to restart the listener services

---------------------------------------------------------------------------------