DevOps, CI/CD and Automation

Hi Denis,
let me first refer to your configuration as it contains a major issue with the port numbers and that's why tnsping fails:
In the listener.ora you use port 1527 whereas in the tnsnames.ora you use 1521 (assuming that the hostname in your listener and tnsnames.ora match).
The tnsping is like just checking if there's a listener listening on the port and host specified in the tnsnames.ora so the host and port number must match.


I also don't want to miss to clarify some statements in your introduction:
I found a lot of guides and some of them, indicated that i needed to configure only ODBC and files listener.ora, tnsnames.ora e init<sid>.ora.
=> That's correct
In other hand, some of them, indicated that i need to install a software in WinServer 2003 to this procedure
=> it depends WHERE you install the gateway

So now in details:
With 10.2.0.4 Oracle shipped by default a component called HSODBC which allows you to connect to a foreign database using a foreign ODBC driver. HSODBC was bundled with the Oracle database and you had to use HSODBC on the same platform from the same home as your Oracle database release.
BUT HSODBC was desupported in March 2008 and replaced by a follow up product called Database Gateway for ODBC (=DG4ODBC) which is now more flexible then HSODBC. DG4ODBC can for example be installed independently from the Oracle database - even on its own machine. DG4ODBC is availoable as release 11.2.0.2 and supported with Oracle database 10.2.0.4.

So my advise would be to use DG4ODBC rather then using the desupported HSODBC product. As mentioned earlier, DG4ODBC is independent from the Oracle database which gives you more flexibility.
For example you can install DG4ODBC on your Oracle database AIX box, then you will need a suitable 3rd party ODBC driver manager and ODBC driver (for AIX) which is able to connect to the SQL Server.
A note how to configure DG4ODBC is available on MOS: How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) to Connect to Non-Oracle Databases Post Install [Document 561033.1]

Taking care of another sentence in your introduction:
I configured through ODBC in WinServer 2003 and adjusted the files listener.ora, tnsnames.ora e init<sid>.ora in Aix.
gives us a second opportunity:
You already have an ODBC driver configured on your Windows box. The init<sid>.ora and the listener entries on the AIX box can be ignored - they are useless as you don't have an ODBC driver on your AIX box.
When you want to use the existing ODBC configuration, then you need to download DG4ODBC for Windows and configure it according to note:
For 32bit Windows, please use:
How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install [Document 466225.1]
For 64bit Windows, please use:
How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install [Document 1266572.1]


Like HSODBC the license for DG4ODBC is included in your Oracle database license - even when you install it on a separate machine. So when you now configure DG4ODBC on Windows, you need to set up on the Windows box the ODBC driver, the gateway init file and a listener. On the Oracle database side then edit the tnsnames.ora and make sure that you now refer to the listener running on the Windows box.


Finally you may ask where you get DG4ODBC from ... You can download it from My Oracle Support (=MOS), from OTN or from edelivery.

Hi kgrounau,
Thanks for your reply. I believe that this recommendation might resolve our problems, but how can i download this DG4ODBC?
We have only a SAP contract and i don't have access to Oracle Metalink and My Oracle Support. For example, when we want to apply patches in Oracle, we have to install patches that SAP recommends and releases for download.

Thanks very much.
Denis
Brazil

Look at OTN:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

There's not the latest release (it is only available through MOS), but the base 11.20.1 release which will work as well.

Look for 11.2.0.1 and your platform, then click on "see all" link right behind the platform. For every platform where the gateways are available, you'll find Oracle Database Gateways 11g Release 2 (11.2.0.1.0) - download it and make sure you install the free component Database Gateway for ODBC (=DG4ODBC).

Hi kgrnoau,
Thanks very much for your reply.I believe that this is the most valuable reply i already received in this forum.
I'm downloading the DG4ODBC for Aix and Windows Server. I will post the result and the solution.

Thanks very much.
Denis
Brazil

Hi Kgrnoau,
I downloaded the DG4ODBC for non-oracle database. I already installed in AIX and i have a problem.
I get the error when i execute the relink command.

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 20480 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-10-10_10-32-51AM. Please wait ...libjvm.so preloadLibrary(/tmp/OraInstall2011-
*10-10_10-32-51AM/jre/bin/libjava.a): 0509-022 Cannot load module /tmp/OraInstall2011-10-10_10-32-51AM/jre/bin/libjava.a.*
*0509-103 The module has an invalid magic number.*
libjvm.so preloadLibrary(/tmp/OraInstall2011-10-10_10-32-51AM/jre/bin/libjava.a): 0509-022 Cannot load module /tmp/OraInstall2011-10-10_10-32-51
AM/jre/bin/libjava.a.
*0509-103 The module has an invalid magic number.*
*<error: unable to load java ( 0509-022 Cannot load module /tmp/OraInstall2011-10-10_10-32-51AM/jre/bin/libjava.a.*
*0509-103 The module has an invalid magic number.)>*
JVMJ9VM015W Initialization error for library jclscar_23(14): JVMJ9VM009E J9VMDllMain failed

Could you help us?

Aix 6.1
Oracle 10g

Denis

Denis,
hope you didn't install the 11g gateway software into an Oracle database 10g home - as this won't work because the libraries are completely different.
So can you please be more precise - where did you install which product?

When you state you have an issue with the relink command- which product do you want to relink, the gateway or the database? In which home does it reside? In its own or in the database home?

Hi kgronau,
Thanks for your reply.
I installed the Oracle database and gateway in the same path (/oracle/TSA/102_64). I read in this forum, guys indicate that we have to install the gateway in the different location of the Oracle Database 10g. Do you agree with this?
I accessed the $ORACLE_HOME/bin and tried the relink. In the same case, if I try to execute the dbca, i got the same error.
Now, I decide to continue the configuration and i get another error.
I want to access the SQL Server (WinServer 2003 32bits) by Oracle Database (Aix 64bits).
Do you believe that this error is caused because the odbc are different platforms ?


ORA-28513: internal error in heterogeneous remote agent
ORA-02063: preceding line from GTWCAPTA
*28513. 00000 - "internal error in heterogeneous remote agent"*
**Cause: An internal error has occurred in the Oracle remote agent*
supporting a heterogeneous database link.
**Action: Make a note of how the error was produced and contact the*
customer support representative of the agent vendor.
Error at Line: 1 Column: 19

Thanks very much
Denis

When installing 11g software into an existing 10g home, then you have corrupted your current installation. The libraries shipped with 11g are different from 10g and thus this won't work.
Best would be restore from a backup the 10g Oracle_Home (only the software, a db restore isn't required). If you don't have a backup from your database home, deinstall the gateway, install the 10g software and apply all previous applied patches again.

As I tried to explain, when you install DG4ODBC on AIX, then you need to have the ODBC driver on the AIX platform. When you only have an ODBC driver for Windows, then you have to install DG4ODBC for Windows on the machine where you have the ODBC driver installed. DG4ODBC requires the ODBC driver and needs to load the ODBC driver libraries. So it needs to be on the same machine on which your ODBC driver resides.

Hi Kgronau,
Thanks again.
I restored the Oracle binaries .
I have a question, What tool do i need to install in Aix? When i started to install in Aix, i can see six or more options, for example, Oracle Database Gateway for Informix, for Sybase, for Microsoft Sql server, for Teradata, for APPC, for WebSphere, for ODBC...
How i want to connect the Oracle to Sql Server, Do i need to install the Oracle Database Gateway for Microsoft Sql Server in Aix (Oracle) and the Oracle Database Gateway for Oracle in Windows (Sql Server)?

Thanks very much.

Denis
Brazil

All these gateways Oracle Database Gateway for Informix, for Sybase, for Microsoft Sql server, for Teradata, for APPC, for WebSphere require an additional support license - they are NOT for free.

The only gateway which is for free is the Oracle Database Gateway for ODBC.
When you install this gateway into its own Oracle_home directory on your AIX box, then you need a 64bit ODBC driver which is able to connect to your SQL Server. Common ODBC vendors who sell this kind of driver are DataDirect, Openlink and Easysoft.
When you decide to use the Database Gateway for ODBC on the AIX box, then you don't need to install any software in the Windows machine. All components must reside on the AIX box (the gateway and the ODBC driver).

If you want to use a solution completely for free, then you have to use Database Gateway for ODBC on the WINDOWS platform. On this machine you need to make sure that you have installed also the Microsoft MS SQL Server ODBC driver which is available for free on Windows from Microsoft.

You can't install DG4ODBC on AIX and use the MS SQl Server ODBC driver on Windows. That does NOT work. The ODBC driver is establishing the connection to the foreign database and it is loaded by DG4ODBC. So it must reside on the same machine where you install DG4ODBC.

Hi kgrounau,
Thanks for your replies.
I observed that I have the Aix 64bits with Oracle 10g 64bits and Windows Server 2003 32bits with Sql Server 2005 32bits. Is that probaly my problem?
I will try to configure this feature with Windows Server and Sql Server 64bits. If this is not work I will try the ODBC downloaded from EasySoft.

Thanks
Denis

Hi Denis,
no, that's not a problem. Just install DG4ODBC on your AIX into a separate ORACLE_HOME, install a 64bit ODBC driver on your AIX, configure it and you're done.

Hi kgronau,
Thanks again for your replies.
When I try to connect in SQL Server I receive the error:

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from GTWCAPTA
*28545. 0000 - "error diagnosed by Net8 when connecting to an agent"*
**Cause: An attempt to call an external procedure or to issue SQL*
to a non-Oracle system on a Heterogeneous Services database link
failed at connection initialization. The error diagnosed
by Net8 NCR software is reported separately.
**Action: Refer to the Net8 NCRO error message. If this isn't clear,*
check connection administrative setup in tnsnames.ora
and listener.ora for the service associated with the
Heterogeneous Services database link being used, or with
*'extproc_connection_data' for an external procedure call.*
Error at Line: 1 Column: 19

Thanks for your help.

Denis

Denis,
ORA-28545 is a configuration mismatch.
Could you please post your gateway listener.ora file, your tnsnames.ora file located in the Oracle database and finally the listener status of the gateway listener (lsnrctl status <gateway listener name>) and a tnsping output using the configured tns alias from your Oracle database home (tnsping <gateway tn alias>).

Hi Kgronau,
Thanks again.
Follows the output.

listener.ora
################
# Filename......: listener.ora
# Created.......: created by SAP AG, R/3 Rel. >= 4.0A
# Name..........:
# Date..........:
################
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= TSA.WORLD)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= TSA)
)
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = unbru10)
(PORT = 1527)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32768)
(SID_NAME = TSA)
(ORACLE_HOME = /oracle/TSA/102_64)
)
(SID_DESC=
(SID_NAME=GtwCapta)
(ORACLE_HOME=/oracle/TSA/102_64/dg4odbc)
(PROGRAM=dg4odbc)
)

)

tnsnames.ora
################
# Filename......: tnsnames.ora
# Created.......: created by SAP AG, R/3 Rel. >= 4.0A
# Name..........:
# Date..........:
################
SATP.WORLD =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.172.18)(PORT = 1521))
)
(CONNECT_DATA =
(SID = satp)
(GLOBAL_NAME = SATP.WORLD)
)
)


TSA.WORLD=
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = unbru10)
(PORT = 1527)
)
)
(CONNECT_DATA =
(SID = TSA)
(GLOBAL_NAME = TSA.WORLD)
)
)


GtwCapta.WORLD =
( DESCRIPTION =
( ADDRESS_LIST =
( ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))
)
(CONNECT_DATA=(SID=GtwCapta))
(HS=OK)
)

lsnrctl status
unbru10:oratsa 2> lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:33:09

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=TSA.WORLD))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
Start Date 19-OCT-2011 09:05:31
Uptime 5 days 23 hr. 27 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /oracle/TSA/102_64/network/admin/listener.ora
Listener Log File /oracle/TSA/102_64/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA.WORLD)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1527)))
Services Summary...
Service "GtwCapta" has 1 instance(s).
Instance "GtwCapta", status UNKNOWN, has 1 handler(s) for this service...
Service "TSA" has 1 instance(s).
Instance "TSA", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

lsnrctl status <gateway listener name>
unbru10:oratsa 4> lsnrctl status GtwCapta

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:34:18

Copyright (c) 1991, 2007, Oracle. All rights reserved.

TNS-01101: Could not find service name
unbru10:oratsa 5> lsnrctl status GtwCapta.World

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:34:33

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))(CONNECT_DATA=(SID=GtwCapta))(HS=OK))
TNS-01189: The listener could not authenticate the user

tnsping gtwcapta
unbru10:oratsa 6> tnsping gtwcapta

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 25-OCT-2011 08:35:35

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

Used parameter files:
/oracle/TSA/102_64/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact ( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))) (CONNECT_DATA=(SID=GtwCapta)) (HS=OK)
)
OK (0 msec)

initGtwCapta.ora
unbru10@root:/oracle/TSA/102_64/hs/admin:#more initGtwCapta.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#Its my ODBC
HS_FDS_CONNECT_INFO = DBCaptaCont
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

#
# ODBC specific environment variables
#
set ODBCINI=<full path name of the odbc initilization file>


#
# Environment variables required for the non-Oracle system
#
set <envvar>=<value>

Thanks,
Denis

Denis,
there are a couple of issues:

Let me start with your listener:
It is listening on (HOST = unbru10) (PORT = 1527) and serves the SID
(SID_NAME=GtwCapta)
(ORACLE_HOME=/oracle/TSA/102_64/dg4odbc)
(PROGRAM=dg4odbc)

It is very risky to use the 10.2 listener to start the 11g gateway which resides in a different home (/oracle/TSA/102_64/dg4odbc).

You've also tested the tnsping successfully:
tnsping gtwcapta
...
Attempting to contact ( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS=(PROTOCOL=tcp)(HOST=10.20.172.63)(PORT=1521))) (CONNECT_DATA=(SID=GtwCapta)) (HS=OK))

But look at the address information - it does not match!

You're trying to connect to a different host!
Listener:(HOST = unbru10) (PORT = 1527)
Tnsnames:(HOST=10.20.172.63)(PORT=1521)

That won't work. Although tnsping reports a successful test in fact it wasn't. TNSPING is only looking for a listener listening on a certain port - but it does not check for the SID. So with the address information you've specified in the tnsnames.ora you've accidentally hit an Oracle listener, but who know what this listener is serving...

Also have a look at the gateway init file initGtwCapta.ora configuration you've posted:
1. it seems it is in the database home - not in the gateway hone within hs/admin - see he shell output:/oracle/TSA/102_64/hs/admin:#more
Now to the config itself. You've specified the odbc.ini contains a DSN entry called DBCaptaCont - but you didn't specify the ODBCINI parameter which points to the odbc.ini file that contains this entry: set ODBCINI=<full path name of the odbc initilization file>

You also didn't specify the ODBC Driver Manager which is mandatory on Unix: HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>




in your previous update you've stated you get the ORA-28545 error. this error is happening when the Oracle SQL*Net configuration doesn't match. As explained earlier the listener and tnsnames entries (host & port) do not match. If you are really sure that you have set up DG4ODBC on this unbru10 host, then you have to correct these parameters so that they match. For example change the tnsnames.ora entry to:
GtwCapta.WORLD =
( DESCRIPTION =
( ADDRESS_LIST =
( ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1527))
)
(CONNECT_DATA=(SID=GtwCapta))
(HS=OK)
)

Unfortunately it is not sufficient - you also need to modify your listener. You have to specify the ODBC library directory in the listener and for the DG4ODBC executable the full path (because you are using a 10g listener to spawn the 11g gateway)....

From my perspective it would be best to check out the My Oracle Support note How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) to Connect to Non-Oracle Databases Post Install [Document 561033.1] again and start from scratch.

I would also recommend to log a service request as this would allow us to assist you online.

Hi kgronau,
I verified that i don't have the libodbc.ora.

unbru10@root:/:#find / -name odbc.ini
unbru10@root:/:#find / -name libodbc.so
unbru10@root:/:#find / -name libodbc.

What happened? I installed the ODBC with no error.
I verify too (http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31042/configodbc.htm#CIHEGGIF), how you can said, i need to adjust the HS_FDS_SHAREABLE_NAME=full_path_of_odbc_driver. I don't have this libs.

Thanks
Denis

On Unix you commonly need an ODBC driver Manager and an ODBC driver. Only a few ODBC drivers (for example IBM) include the driver manager code in the odbc driver. But most of the vendors ship a separate CD or rely on a 3rd party ODBC driver manager library.

When you don't have a driver manager installed, then install the free ODBC driver manager from www.unixodbc.org.
In addition not every ODBC installation is creating an odbc.ini file. You need to check out the ODBC vendor's documentaion to see which parameters you have to specify in the odbc.ini file.

Hi kgronau, what's up?
I followed your advice, I've already had installed the EasySoft Sql Server ODBC Driver and the Oracle Database Gateway.
How you advice me, I installed the DG4ODBC in another $ORACLE_HOME. My Oracle 10g in /oracle/TSA/102_64 and my DG4ODBC in /oracle/TSA/112_64.
At the Windows Server 2003 with Sql Server 2005, I already configured the ODBC Data Source.
Now i get an error and i need your help.

Message:
unbru10:oratsa 5> lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 30-NOV-2011 09:18:01

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=TSA.WORLD))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
Start Date 30-NOV-2011 09:02:19
Uptime 0 days 0 hr. 15 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /oracle/TSA/102_64/network/admin/listener.ora
Listener Log File /oracle/TSA/102_64/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA.WORLD)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TSA)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1527)))
Services Summary...
Service "TSA" has 1 instance(s).
Instance "TSA", status UNKNOWN, has 1 handler(s) for this service...
Service "hscapta" has 1 instance(s).
Instance "hscapta", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
unbru10:oratsa 6> tnsping capta.world

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 30-NOV-2011 09:18:07

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

Used parameter files:
/oracle/TSA/102_64/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS= (PROTOCOL = TCP) (HOST = 192.168.10.20) (PORT = 1432)) (CONNECT_DATA= (SID = hscapta)) (
HS = OK)))
TNS-12537: TNS:connection closed



Listener
unbru10:oratsa 4> more listener.ora

################
# Filename......: listener.ora
# Created.......: created by SAP AG, R/3 Rel. >= 4.0A
# Name..........:
# Date..........:
################
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= TSA.WORLD)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= TSA)
)
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = unbru10)
(PORT = 1527)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32768)
(SID_NAME = TSA)
(ORACLE_HOME = /oracle/TSA/102_64)
)
(SID_DESC=
(SID_NAME=hscapta)
(ORACLE_HOME=/oracle/TSA/112_64)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib)
)
)

Tnsnames
unbru10:oratsa 7> more tnsnames.ora

################
# Filename......: tnsnames.ora
# Created.......: created by SAP AG, R/3 Rel. >= 4.0A
# Name..........:
# Date..........:
################
SATP.WORLD =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.15)(PORT = 1521))
)
(CONNECT_DATA =
(SID = satp)
(GLOBAL_NAME = SATP.WORLD)
)
)


TSA.WORLD=
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = unbru10)
(PORT = 1527)
)
)
(CONNECT_DATA =
(SID = TSA)
(GLOBAL_NAME = TSA.WORLD)
)
)

CAPTA.WORLD=
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP) (HOST = 192.168.10.20) (PORT = 1432))
(CONNECT_DATA= (SID = hscapta))
(HS = OK)
)
)

*/etc/Odbc.ini*
[CAPTA_CONT]
Driver=Easysoft ODBC-SQL Server
Description=Easysoft SQL Server ODBC driver
Server=192.168.10.20
Port=1432
Database=sefunmp
User=sa
Password=adm
Mars_Connection=YES
Logging=No
LogFile=
QuotedId=Yes
AnsiNPW=Yes
Language=
Version7=No
ClientLB=No
Failover_Partner=
VarMaxAsLong=No
DisguiseWide=No
DisguiseLong=No
DisguiseGuid=No
Trusted_Connection=No
Trusted_Domain=
IPv6=No


Thanks for your great help.
Denis

Denis,
your listener.ora is wrong.

Your output shows:
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production

=> so you're using a 10.2 listener but you're trying to use a 11.2 gateway (ORACLE_HOME=/oracle/TSA/112_64)

Commonly it is not supported that an Oracle listener of a lower version spawns a connection to a higher release - Oracle us downwards compatible but not upwards. Best would be to configure a separate listener for the 11.2 gateway in the 11.2 gateway home listening on its own port.


Next wrong config is the tnsnames.ora. YOU have configured (ADDRESS= (PROTOCOL = TCP) (HOST = 192.168.10.20) (PORT = 1432))
The connect flow of DG4ODBC is the Oracle database wants to connect to the foreign database, hence it connects to the listener that knows dg4odbc. This connection is done using SQL*Net. So the tnsnames.ora entry of the Oracle database needs to point to the oracle listener that spawns the dg4odbc process which is currently running on unbru10 and listening on port 1527 (but should be changed according to my first comment).

To be complete DG4ODBC is then loading the ODBC driver and the ODBC driver connects to the foreign database - so the only location where you have to configure the SQL Server connect details is in the odbc.ini.

Hi kgronau,
How can i start the Oracle 11g listener at the Oracle 10g installation?
Or, can i use two listeners at the same machine and with the different versions?

Thanks,
Denis

You have to use 2 separate listeners. For 10g a 10g listener and for 11g the 11g listener. To start the database listener, you first need to source your Oracle database environment. When you want to start the gateway listener, then source the gateway environment.

Hi Kgronau,
Do you know how to use this listener?
I tried to alter the TNS_ADMIN variable, but didn't work. I tried to use the paths at the LD_LIBRARY_PATH, but didn't work too.

Before
TNS_ADMIN=/oracle/TSA/102_64/network/admin

After
TNS_ADMIN=/oracle/TSA/112_64/network/admin


Listener
################
# Filename......: listener.ora
# Created.......: created by SAP AG, R/3 Rel. >= 4.0A
# Name..........:
# Date..........:
################
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= TSA.WORLD)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= TSA)
)
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = unbru10)
(PORT = 1527)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32768)
(SID_NAME = TSA)
(ORACLE_HOME = /oracle/TSA/102_64)
)
)

LISTENER2=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1528))))

SID_LIST_LISTENER2=
(SID_LIST=
(SID_DESC=
(SID_NAME=hscapta)
(ORACLE_HOME=/oracle/TSA/112_64)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib:/oracle/TSA/112_64/lib:/oracle/TSA/112_64/bin)
)
)

Thanks for your help.
Denis

to source the environment for a gateway the minimum settings are:

TNS_ADMIN=/oracle/TSA/112_64/network/admin
export TNS_ADMIN
ORACLE_SID=dg4odbc
export ORACLE_SID

ORACLE_HOME=/oracle/TSA/112_64
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
cd $ORACLE_HOME

once set you can now use the 11.2 lsnrctl utility to start the 11.2 listener: lsnrctl start LISTENER2

Hi Kgronau,
Thanks again.
I adjusted this environment variables for a user that a called oracle. The listener worked with the Oracle 11g.

unbru10@oracle:/oracle/TSA/112_64:#lsnrctl status listener2

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 30-NOV-2011 11:35:07

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1528)))
STATUS of the LISTENER
------------------------
Alias listener2
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2011 11:29:01
Uptime 0 days 0 hr. 6 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /oracle/TSA/112_64/network/admin/listener.ora
Listener Log File /oracle/TSA/112_64/network/log/listener2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=unbru10)(PORT=1528)))
Services Summary...
Service "hscapta" has 1 instance(s).
Instance "hscapta", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

unbru10@oracle:/oracle/TSA/112_64:#tnsping capta.world

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 30-NOV-2011 11:35:26

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS= (PROTOCOL = TCP) (HOST = unbru10) (PORT =1528)) (CONNECT_DATA= (SID = hscapta)) (H
S = OK)))
OK (0 msec)

unbru10:oratsa 3> ps -ef | grep lsn
oratsa 10879128 5701858 1 11:29:39 pts/0 0:00 grep lsn
oratsa 13828300 1 0 10:30:18 pts/1 0:00 /oracle/TSA/102_64/bin/tnslsnr listener -inherit
oracle 14811158 1 0 11:29:00 pts/0 0:00 /oracle/TSA/112_64/bin/tnslsnr listener2 -inherit


Now, i created the dblink and tried to execute the test.

select * from dual@"CAPTA.WORLD"

ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
12504. 00000 - "TNS:listener was not given the SID in CONNECT_DATA"
*Cause: The SID was missing from the CONNECT_DATA.
*Action: Check that the connect descriptor corresponding to the service
name in TNSNAMES.ORA has an SID component in the CONNECT_DATA.
Error at Line: 1 Column: 19

Tnsnames.ora at the /oracle/TSA/112_64/network/admin
unbru10@oracle:/oracle/TSA/112_64/network/admin:#more tnsnames.ora
CAPTA.WORLD=
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP) (HOST = unbru10) (PORT =1528))
(CONNECT_DATA= (SID = hscapta))
(HS = OK)
)
)

Thanks very much.
Denis

The Oracle database is 10.2 so it will use the tnsnames.ora which belongs to the 10.2 environment.
Please source the database environment, then use the 10.2 tnsping to check the alias: tnsping capat.world

Kgronau,
I tried this:


unbru10:oratsa 1> tnsping capta.world

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 30-NOV-2011 11:42:04

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

Used parameter files:
/oracle/TSA/102_64/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS= (PROTOCOL = TCP) (HOST = unbru10) (PORT = 1528)) (CONNECT_DATA= (SID = hscapta)) (HS = OK)))
OK (0 msec)
unbru10:oratsa 2>


Thanks,
Denis

just saw it - there a mistake in the tnsnames.ora
change your entry to:

CAPTA.WORLD=
<space>(DESCRIPTION=
<space><space>(ADDRESS= (PROTOCOL = TCP) (HOST = unbru10) (PORT =1528))
<space><space>(CONNECT_DATA=
<space><space><space> (SID = hscapta))
<space><space>(HS = OK)
<space>)


without address_list and the closing bracket.

Kgronau,
Now, when i try to execute the command select * from dual@"CAPTA.WORLD", I received the error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from CAPTA
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
Error at Line: 1 Column: 28

Thanks,
Denis

Denis,
It looks like you are now getting to the SQL*Server database and that is then returning an error.
Could you set up debug tracing by setting the following in the gateway init<sid>.ora file -

HS_FDS_TRACE_LEVEL=debug

and run the select from a new SQLPLUS session. The trace should show the error being returned from SQL*Server.
What is the full version of the ODBC driver you are using ?

Regards,
Mike

Hi Mike,
I tested this parameter, but didn't work.

HS_FDS_CONNECT_INFO = CAPTA_CONT
HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc
HS_FDS_SHAREABLE_NAME =/usr/local/easysoft/unixODBC/lib/libodbc.so
HS_FDS_TRACE_LEVEL=debug

I checked the hsodbcsql.trc file, it's empty.
I'm using EasySoft Sql Server ODBC Driver v1.4.

Aix 6.1
Oracle 10g
Oracle Database Gateway 11g
Windows Server 2003
Sql Server 2005

Thanks
Denis

Denis,
did you open a new session once you changed the trace parameter? The gateway init file is only read at the beginning when the gateway process is spawned from the listener. When yo now have opened a gateway process (even when it failed) there might be a gateway process in the memory that belongs to your session and when you now reuse the statement by executing the failing statement again, then the trace parameter isn't read and executed.

So please make sure no dg4odbc process is running, then retry the select. Also remove the parameter HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc.
Make sure that the $ORACLE_HOME/hs/log directory is empty - then start a new session and execute your statement.

Hi Kgronau,
I tried this, changed the parameter and opened a new session by SQL Plus.
Now, I removed the HS_FDS_TRACE_FILE_NAME parameter, restarted the machine, started the two listeners, started up the instance and executed my statement. I checked the directory $ORACLE_HOME/hs/log and $ORACLE_HOME/hs/trace and anything created.
How can i check the dg4odbc process?

Thanks again,
Denis

Denis,
When you run the select from SQLPLUS then what error do you see ? Is it the same one you reported earlier -

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from CAPTA
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
Error at Line: 1 Column: 28

If so, this indicates the dg4odbc executable is being called so it should have created a trace file.
According to the listener.ora file you posted earlier the gateway is installed in the directory -

/oracle/TSA/112_64

so the trace should be in -

/oracle/TSA/112_64/hs/log

To check the executable itself go to -

/oracle/TSA/112_64/bin

and do -

./dg4odbc

the output should be similar to -

Oracle Corporation --- THURSDAY DEC 01 2011 12:24:35.396

Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production Built with
Oracle Database Gateway for ODBC

If the executable runs okay then it post the most recent versions of the following files so we can check the setup again -

- inithscapta.ora
- gateway listener.ora

Regards,
Mike

Hi Mike,
Thanks for your help.
I checked the /oracle/TSA/112_64/hs/log directory and the trace files were there.
I cheched the last file created.

#ls -ltr

The output:

unbru10@root:/oracle/TSA/112_64/hs/log:#more hscapta_agt_13828262.trc


Oracle Corporation --- THURSDAY DEC 01 2011 09:05:35.270


Heterogeneous Agent Release
+11.2.0.1.0+


Unable to open init file inithscapta.ora in directory /oracle/TSA/112_64/hs/admin

HS Gateway: NULL connection context at exit

So, I copied the inithscapta.ora from /oracle/TSA/102_64/hs/admin to /oracle/TSA/112_64/hs/admin.

Now, follow the new output.

unbru10@root:/oracle/TSA/112_64/hs/log:#more hscapta_agt_4325414.trc


Oracle Corporation --- THURSDAY DEC 01 2011 09:48:53.047


Heterogeneous Agent Release
+11.2.0.1.0+




Oracle Corporation --- THURSDAY DEC 01 2011 09:48:53.046

Version 11.2.0.1.0

Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "debug"
Entered hgosdip
setting HS_OPEN_CURSORS to default of 50
setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
setting HS_FDS_RECOVERY_PWD to default value
setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
setting HS_IDLE_TIMEOUT to default of 0
setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
setting HS_NLS_NCHAR to default of "AL32UTF8"
setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
setting HS_FDS_DATE_MAPPING to default of "DATE"
setting HS_RPC_FETCH_REBLOCKING to default of "ON"
setting HS_FDS_FETCH_ROWS to default of "100"
setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
setting HS_FDS_MAP_NCHAR to default of "TRUE"
setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
setting HS_FDS_QUERY_DRIVER to default of "TRUE"
setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
Parameter HS_FDS_QUOTE_IDENTIFIER is not set
setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGet
Info"
setting HS_FDS_DELAYED_OPEN to default of "TRUE"
setting HS_FDS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
ORACLE_SID is "hscapta"
Product-Info:
Port Rls/Upd:1/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:hscapta
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=2
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=2
HOCXU_SEM_VER=102000
Entered hgolofn at 2011/12/01-09:48:53
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/easysoft/unixODBC/lib/libodbc.so"
Entered hgolofns at 2011/12/01-09:48:53
hoaerr:28500
Exiting hgolofns at 2011/12/01-09:48:53
Failed to load ODBC library symbol: /usr/local/easysoft/unixODBC/lib/libodbc.so(SQLAllocHandle)
Exiting hgolofn, rc=28500 at 2011/12/01-09:48:53
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 ID:Loading ODBC aray of function ptrs
Entered hgoexit
HS Gateway: NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 ID:Connection context

Thanks,
Denis

Denis,
Thanks for following up and resolving the problems so far.
The problem is with the libodbc.so driver manager -

Failed to load ODBC library symbol: /usr/local/easysoft/unixODBC/lib/libodbc.so(SQLAllocHandle)

I'll check and get back to you.

Regards,
Mike

Denis,
Can you check the following -

1. The gateway listener.ora entry for the gateway still has the correct LD_LIBRARY_PATH entry ? it should have -

(ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib)

2. The libodbc.so is a 64-bit version -

cd /usr/local/easysoft/unixODBC/lib

file libodbc.so

If it reports a symbolic link then issue 'file' against that.

3. That the file has the correct entries -

cd /usr/local/easysoft/unixODBC/lib

nm libodbc.so | grep SQLA

It should report something like -

000000000002fb2e T SQLAllocConnect
000000000002fa14 T SQLAllocEnv
000000000002f6b0 T SQLAllocHandle
000000000002fc5c T SQLAllocStmt
0000000000060c5c t _ZN5DMEnv15SQLAllocConnectEPPv

4. Make sure the Easysoft driver is ODBC v3 compliant. You'll have to check with the supplier for that.

Regards,
Mike

Mike,
Thanks for your help.
I found the solution here http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc.html#config.

Follow step by step.
Now, I have another error:

select count(*) from sefcli@"CAPTA.WORLD"
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
+[unixODBC][Driver Manager]Can't open lib+
+'/usr/local/easysoft/sqlserver/lib/libessqlsrv.a' : file not found {01000}+
ORA-02063: preceding 2 lines from CAPTA

Trace file
Oracle Corporation --- THURSDAY DEC 01 2011 10:46:02.941


Heterogeneous Agent Release
11.2.0.1.0




Oracle Corporation --- THURSDAY DEC 01 2011 10:46:02.940

Version 11.2.0.1.0

Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "debug"
Entered hgosdip
setting HS_OPEN_CURSORS to default of 50
setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
setting HS_FDS_RECOVERY_PWD to default value
setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
setting HS_IDLE_TIMEOUT to default of 0
setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
setting HS_NLS_NCHAR to default of "AL32UTF8"
setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
setting HS_FDS_DATE_MAPPING to default of "DATE"
setting HS_RPC_FETCH_REBLOCKING to default of "ON"
setting HS_FDS_FETCH_ROWS to default of "100"
setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
setting HS_FDS_MAP_NCHAR to default of "TRUE"
setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
setting HS_FDS_QUERY_DRIVER to default of "TRUE"
setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
Parameter HS_FDS_QUOTE_IDENTIFIER is not set
setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
setting HS_FDS_DELAYED_OPEN to default of "TRUE"
setting HS_FDS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
ORACLE_SID is "hscapta"
Product-Info:
Port Rls/Upd:1/0 PrdStat:0
Agent:Oracle Database Gateway for ODBC
Facility:hsa
Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:hscapta
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=2
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=2
HOCXU_SEM_VER=102000
Entered hgolofn at 2011/12/01-10:46:02
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/easysoft/unixODBC/lib/libodbc.so"
Entered hgolofns at 2011/12/01-10:46:02
symbol_peflctx=0xa09df658
hoaerr:0
Exiting hgolofns at 2011/12/01-10:46:02
Entered hgolofns at 2011/12/01-10:46:02
symbol_peflctx=0xa09df6d0
hoaerr:0
Exiting hgolofns at 2011/12/01-10:46:02
Entered hgolofns at 2011/12/01-10:46:02
symbol_peflctx=0xa09df700
hoaerr:0
Exiting hgolofns at 2011/12/01-10:46:02
Entered hgolofns at 2011/12/01-10:46:02
symbol_peflctx=0xa09df7a8
hoaerr:0
Exiting hgolofns at 2011/12/01-10:46:02
Exiting hgolofn, rc=0 at 2011/12/01-10:46:02
HOSGIP for "HS_OPEN_CURSORS" returned "50"
HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
treat_SQLLEN_as_compiled = 1
Exiting hgoinit, rc=0 at 2011/12/01-10:46:02
Entered hgolgon at 2011/12/01-10:46:02
reco:0, name:sa, tflag:0
Entered hgosuec at 2011/12/01-10:46:02
Exiting hgosuec, rc=0 at 2011/12/01-10:46:02
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using sa as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
Entered hgocont at 2011/12/01-10:46:02
HS_FDS_CONNECT_INFO = "CAPTA_CONT"
RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
Entered hgogenconstr at 2011/12/01-10:46:02
dsn:CAPTA_CONT, name:sa
optn:
Entered hgocip at 2011/12/01-10:46:02
dsn:CAPTA_CONT
Exiting hgocip, rc=0 at 2011/12/01-10:46:02
Exiting hgogenconstr, rc=0 at 2011/12/01-10:46:02
Entered hgopoer at 2011/12/01-10:46:02
hgopoer, line 233: got native error 0 and sqlstate 01000; message follows...
[unixODBC][Driver Manager]Can't open lib '/usr/local/easysoft/sqlserver/lib/libessqlsrv.a' : file not found {01000}
Exiting hgopoer, rc=0 at 2011/12/01-10:46:02
hgocont, line 2753: calling SqlDriverConnect got sqlstate 01000
Exiting hgocont, rc=28500 at 2011/12/01-10:46:02 with error ptr FILE:hgocont.c LINE:2772 ID:Something other than invalid authorization
Exiting hgolgon, rc=28500 at 2011/12/01-10:46:02 with error ptr FILE:hgolgon.c LINE:781 ID:Calling hgocont
Entered hgoexit at 2011/12/01-10:46:02
Exiting hgoexit, rc=0


I already checked this file at this directory and it exists. I changed the permissions to oratsa:dba too, but didn't work.

Thanks by support for all gurus.

Denis

Denis,
As you see the error is coming from the driver manager.
I checked the configuration files again and for AIX the ENVS setting should use LIBPATH instead of LD_LIBRARY_PATH so could you change the listener.ora entry from -

(ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib:/oracle/TSA/112_64/lib:/oracle/TSA/112_64/bin)

tp

(ENVS=LIBPATH=/usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib:/oracle/TSA/112_64/lib:/oracle/TSA/112_64/bin)

if you still have problems then add the directory -

/usr/local/easysoft/sqlserver/lib

to the LIBPATH in the listener.ora.

In both cases stop and start he gateway listener and select from a new SQLPLUS session.

You could also try the select without quotes round the db link name -

select count(*) from sefcli@CAPTA.WORLD ;

Also, if SQL*Server is case sensitive for table names then you may need to do -

select count(*) from "sefcli"@CAPTA.WORLD ;

or perhaps also with the owner -

select count(*) from "owner"."sefcli"@CAPTA.WORLD ;

Regards,
Mike

Hi Mike,
Thanks a lot. I changed this parameter and worked.
Now, i would want to know... Do i need to adjust the types at any file? For instance, i tried to execute a select at the SAP and it returned a error null column.

Thanks Kgronau and Mike.

Denis

Hi Denis,
Glad you have it working now.

Dg4ODBC maps ODBC datatypes to Oracle datatypes and this depend on the data type passed to it by the ODBC driver.
This detailed in the documentation -

Oracle® Database Gateway for ODBC
User’s Guide
11g Release 2 (11.2)

in the section -

Mapping ODBC Data Types to Oracle Data Types

For example -

ODBC Oracle
SQL_BIGINT NUMBER(19,0)
SQL_BINARY RAW
SQL_CHAR CHAR

If you have problems we need to know -

- the datatype of the column in the SAP table, that is the column type in the create table statement
- the datatype that this is translated to by the ODBC driver - which should be shown in an ODBC trace
- the datatype that is seen by the gateway and the datatype to which it is mapped - shown by a gateway debug or 255 level trace

Regards,
Mike

Mike,
I created a synonym to sefcli@CAPTA.WORLD.

CREATE PUBLIC SYNONYM sefcli
FOR sefcli@CAPTA.WORLD;

I tried to execute select from sefcli;* by SAP Application. It's return an error "Syntax error in generated propram: The field string "TY_DATA" contains no fields ".

Thanks,
Denis

Are you able to select from SAP the table with the at command?
select * from sefcli@CAPTA.WORLD;
Which SAP Application are you using and does it work with SQL*Plus?