Home » RDBMS Server » Networking and Gateways » Unable to access DB2 data from oracle (Oracle 9.2.0.1.0, Windows 2000, IBM720)
Unable to access DB2 data from oracle [message #334556] Thu, 17 July 2008 01:55 Go to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi all,

I was trying to setup HS (hiterogenous services) to access DB2 datata (IBM720). Since last two days I am sturggling to get data into Oracle from DB2 using HS, but no success.

Please do not give me links / documnetation to follow. Becuase I have altready spend two days in doing so (links, notes, documentation and no luck. So Please debug me where I have gone wrong).

Here is what I have tried (and tired off)
oracle\ora92\hs
===============
inithsodbc.ora
--------------
HS_FDS_CONNECT_INFO=FILEDSN=ACCOUNT
HS_FDS_TRACE_LEVEL = OFF        

set ODBCINI=C:\WINNT

listener.ora
------------
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = MurlidharM)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = o920db)
      (ORACLE_HOME = d:\oracle\ora92)
      (SID_NAME = o920db)
    )
    (SID_DESC =
      (ORACLE_HOME = d:\oracle\ora92)
      (SID_NAME = hsodbc)
      (PROGRAM=hsodbc)
    )
  )


tnsnames.ora
------------
MURLI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MurlidharM)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = o920db)
    )
  )
NKL720 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 191.9.200.8)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = HSODBC))
    (HS=OK)
  )


Lsnrctl Status
==============
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
System parameter file is D:\ORACLE\ORA92\network\admin\listener.ora
Log messages written to D:\ORACLE\ORA92\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MurlidharM.vss)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Pro
tion
Start Date                17-JUL-2008 10:12:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   D:\ORACLE\ORA92\network\admin\listener.ora
Listener Log File         D:\ORACLE\ORA92\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MurlidharM.vss)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "hsodbc" has 1 instance(s).
  Instance "hsodbc", status UNKNOWN, has 1 handler(s) for this service...
Service "o920db" has 1 instance(s).
  Instance "o920db", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Database Link
=============
create database link NKL720 connect to ashima identified by ashima using 'NKL720';

(ACCOUNT IS file DSN, refers to db2 grey library, user/pwd is ashima/ashima.
I can access DB2 grey library from winsql )
Now when I issue the following query
sql> select * from grey.arri@nkl720;

I will get the error as
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from NKL720

So please help me out where I am wrong. Thanks in advance and looking forward......
MSMallya
Re: Unable to access DB2 data from oracle [message #335102 is a reply to message #334556] Mon, 21 July 2008 00:39 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
I had wrongly specified host name in tnsnames.ora
Quote:
NKL720 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 191.9.200.Cool(PORT = 1521))
)
(CONNECT_DATA = (SID = HSODBC))
(HS=OK)
)


and changed as
NKL720 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MurlidharM)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = HSODBC))
    (HS=OK)
  )

After this, when I issue
select * from grey.arrival@nkl720

I am getting following error
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system
returned this message:
[Generic Connectivity Using ODBC][IBM][Client Access ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. COMM RC=0x5
(SQL State: 08S01; SQL Code: -5)
ORA-02063: preceding 2 lines from NKL720


Any solution ? Again Thanks in advance.

MSMallya

[Updated on: Mon, 21 July 2008 00:42]

Report message to a moderator

Re: Unable to access DB2 data from oracle [message #336022 is a reply to message #334556] Thu, 24 July 2008 08:12 Go to previous message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

None?..................

Still I cannnot access IBM DB2/400 V4R3 from
Oracle 9.2.0.1.0. using Generic Connectivity (HS).....

Error being :
select * from sat@nkl720
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][IBM][Client Access ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. COMM RC=0x5
(SQL State:08S01; SQL Code: -5)
ORA-02063: preceding 2 lines from NKL720


MSMallya
Previous Topic: WARNING: Subscription for node down event still pending
Next Topic: ORA-12571: TNS:packet writer failure
Goto Forum:
  


Current Time: Fri Oct 24 07:34:25 CDT 2014

Total time taken to generate the page: 0.05181 seconds