Home » RDBMS Server » Networking and Gateways » CONNECT TO DB2 ERROR by Oracle Gateway in Linux "ORA-28500 AND Data source name not found, and no de (oracle,db2,gateway,Linux)
CONNECT TO DB2 ERROR by Oracle Gateway in Linux "ORA-28500 AND Data source name not found, and no de [message #657993] Wed, 30 November 2016 00:57
lenneyzhaosh
Messages: 1
Registered: November 2016
Junior Member
I have an Oracle in Linux which want to get data from AIX Server's DB2 database; I used Database Gateway for ODBC to link DB2,
when I created a DB link, and used this link to connect to DB2, I got an error as below
ORA-28500: connection from ORACLE to a non-Oracle system returned this message
[unixODBC][Driver Manager][Data source name] {I}

The following is what I've done:
1,install gateway for ODBC
2,install unxiODBC driver
3,uncompress ODBC for DB2
4,check unixODBC setting
5,Configure init<SID>.ora file
6,Configure odbc.ini file
7,Configure odbcinst.ini file
8,Configure db2dsdriver.cfg file
9,Check connect to DB2 succeed by ODBC driver
10, Configure listener.ora and tnsnames.ora
11, restart listener
12, create DB link and test connect


1. My gateway was installed in oracle home path, ORACLE_HOME=/pp/oracle/product/12.1.0/dbhome_1

2. I installed unixODBC driver by default

3. I uncompress ODBC for DB2 Driver in '/pp1/migration/clidriver'

4. unixODBC setting
[root@plm5q101 admin]# odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@plm5q101 admin]#

5. DB2's SID is 'plm'
/pp/oracle/product/12.1.0/dbhome_1/hs/admin/initplm.ora
HS_FDS_CONNECT_INFO=plm
HS_FDS_TRACE_LEVEL=on
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
set ODBCINI=/etc/odbc.ini

6. [root@plm5q101 admin]# cat /etc/odbc.ini
[plm]
Description = IBMDB2 ODBC data source
Driver      = DB2
Trace      = Yes
Data Source Name=plm
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={DB2_CLI_DRIVER_INSTALL_PATH=/pp1/migration/clidriver}

7. [root@plm5q101 admin]# cat /etc/odbcinst.ini
# Example driver definitions

[ODBC]
Trace = yes
TraceFile = /pp1/log/odbc_trace.log


[DB2]
Description  = IBM DB2 ODBC driver (64-bit)
Driver      = /pp1/migration/clidriver/lib/libdb2.so
FileUsage  = 1
DontDLClose = 1

8. [root@plm5q101 cfg]# cat /pp1/migration/clidriver/cfg/db2dsdriver.cfg
<configuration>
   <dsncollection>
      <dsn alias="plm" name="plm" host="example.com" port="50000">
         <parameter name="UserID" value="db2admin"/>
         <parameter name="Password" value="password"/>
      </dsn>
   </dsncollection>
</configuration>
[root@plm5q101 cfg]#

9. use isql connect to DB2 server succeed
[root@plm5q101 cfg]# isql plm
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from T_DRWMST;
+------------+
| 1          |
+------------+
| 3084134    |
+------------+
SQLRowCount returns -1
1 rows fetched
SQL>
10. /pp/oracle/product/12.1.0/dbhome_1/network/admin this diretory's listener.ora and tnsnames.ora

listener.ora file
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME=plm)
      (ORACLE_HOME = /pp/oracle/product/12.1.0/dbhome_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH_64=/pp1/migration/clidriver/lib:/pp/oracle/product/12.1.0/dbhome_1/lib:/usr/lib:/usr/lib64)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=146.215.116.166)(PORT = 1522))
    )
  )

tnsnames.ora file
DB2=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exaple.com)(PORT = 1522))
    (CONNECT_DATA =
      (SID = plm)
    )
 (HS=OK))

CHECK tns is usable:
[root@plm5q101 admin]# tnsping DB2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-NOV-2016 12:19:53

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 146.215.116.166)(PORT = 1522)) (CONNECT_DATA = (SID = plmdb2)) (HS=OK))
OK (0 msec)
[root@plm5q101 admin]#

11. restart listener
lsnrctl stop
lsnrctl start

12. create DBLINK 
create public database link  db2link connect to db2admin identified by password using 'DB2' ;


select count(*) from T_DRWMST@db2link

ORA-28500: connection from ORACLE to a non-Oracle system returned this message
[unixODBC][Driver Manager][Data source name] {I}

see the trace log:
[ODBC][21153][1480414214.502483][SQLGetDiagRecW.c][533]
                Exit:[SQL_SUCCESS]
                        SQLState = IM002
                        Native = 0x7ffd03a5a874 -> 0
                        Message Text = [[unixODBC][Driver Manager]Data source name not found, and no default driver specified]

who can help mi................

Report message to a moderator

Previous Topic: sqlnet.expire_time
Next Topic: Generic Connectivity Not Working (Oracle -Ms Access)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jan 04 17:45:30 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.