3 Tips on Using dg4odbc on 64-bit Linux

I recently imported the contents of an Oracle 8i database into an 11g database. Since the original application needed to query a MS SQL Server database, I had to set up generic connectivity, which among other things, allows , the Oracle database to connect to non-Oracle databases for DML operations. The external database is referred to via a database link. In Oracle 11g, the Database Generic Connectivity for ODBC (dg4odbc) is a replacement for hsodbc.

After following the instructions for a typical setup shown here: Configuring Oracle Database gateway for ODBC, I set up a database link to a remote SQL Server database, and called it SQLSERVER. I planned to use the following query to test whether it is working:

SQL> select * from [email protected];
-- gives me a list of tables that this connection has access to

Since I had installed 11g on a 64-bit Linux system, I downloaded and installed the 64-bit DataDirect drivers (seems logical). I could connect to the SQL Server database using the demoodbc executable. So everything looked good until I ran the test:

 
SQL> select * from [email protected];
select * from [email protected]
              *
ERROR at line 1;
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 SQLSERVER
SQL>

I did enable tracing, but there is no odbc trace yet to look at, and no log either under $ORACLE_HOME/hs/log Interestingly, the only clue I get (from the listener trace) is:


Mon Mar 25 12:59:54 2008
24-MAR-2008 12:59:54 * (CONNECT_DATA=(SID=odbc_SQLSERVER)(CID=(PROGRAM=)(HOST=myhost)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.121)(PORT=43917)) * establish * odbc_SQLSERVER * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe
Mon Mar 25 13:02:50 2008


In plain English, the listener is trying to tell me that it is unable to handle the processing expected with dg4odbc. The seed of doubt is sown: does dg4odbc work? Now would be a good time to check it out:

[[email protected] ~]$ dg4odbc
dg4odbc: error while loading shared libraries: libagtsh.so: cannot open shared object file: No such file or directory
[[email protected] ~]$


Arrggh! dg4odbc is a 32-bit Application — even when installed on 64-bit Linux. For this application to run on 64-bit Linux, I have to ensure that LD_LIBRARY_PATH is set appropriately, that the 32-bit libraries are read before the 64-bit libraries.

[[email protected] ~]$ echo $LD_LIBRARY_PATH
/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib:/lib:/usr/lib
[[email protected] ~]$

And:

[[email protected] ~]$ dg4odbc
Oracle Corporation --- TUESDAY   MAR 25 2008 15:26:11.565
Heterogeneous Agent Release 11.1.0.6.0 - Production  Built with
Oracle Database Gateway for ODBC
[[email protected] ~]$

So that means I can alter the entries in listener.ora to also use the libraries appropriately. Here’s the old listener.ora:

(SID_DESC =
    (SID_NAME = odbc_sqlserver)
    (ORACLE_HOME = /d01/oracle/product/11.1)
    (PROGRAM = dg4odbc)
   (ENVS=LD_LIBRARY_PATH=/d01/oracle/odbc64v53/lib:
                                    /d01/oracle/product/11.1/lib)
)

And the new listener.ora:


(SID_DESC =
(SID_NAME = odbc_sqlserver)
(ORACLE_HOME = /d01/oracle/product/11.1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/d01/oracle/odbc64v53/lib:/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib)
)


Still no joy!


SQL> select * from [email protected]
select * from [email protected]
*
ERROR at line 1:ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from SQLSERVER


Now we have a trace to look at. And it says:

.... Entered hgolofn at 2008/03/25-08:30:03 HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/d01/oracle/odbc64v53/lib/libodbc.so" Entered hgolofns at 2008/03/25-08:30:03 hoaerr:28500 Exiting hgolofns at 2008/03/25-08:30:03 Failed to load ODBC library symbol: /d01/oracle/odbc64v53/lib/libodbc.so(SQLAllocHandle) Exiting hgolofn, rc=28500 at 2008/03/25-08:30:03 Exiting hgoinit, rc=28500 at 2008/03/25-08:30:03 with error ptr <a href="///hgoinit.c">FILE:hgoinit.c</a> LINE:282 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs Entered hgoexit at 2008/03/25-08:30:03 ....

In its language, it’s saying: Sorry this is the wrong library and cannot work with dg4odbc. So the 64-bit drivers were a wrong choice — time to install and configure the 32-bit drivers. I downloaded the evaluation version from DataDirect. After unzipping in a temporary location . . .


[[email protected] data_direct]$ ksh unixmi.ksh

DataDirect Technologies Product Setup is preparing….English has been set as the installation language.

Log file: /tmp/logfile.392.1

———————————————————————-
DataDirect Technologies Product Setup (DPS)
for UNIX operating systems
———————————————————————-
The following operating system has been detected:

Linux

Is this the current operating system on your machine (Y/N) ? [Y]

END USER PRODUCT LICENSE AGREEMENT (“AGREEMENT”)

—snipped —-

Enter YES to accept the above agreement: YES

Please enter the following information for proper registration.

In the Key field, enter either EVAL or the Key provided.

Name :EVAL
Company :Mycompany
Serial Number:EVAL
Key :EVAL
You can install drivers for all supported databases or
install a single driver.
Drivers for All Supported Databases includes
one driver for each supported database. In the case of Oracle,
DataDirect Connect for ODBC 5.3 SP1 has two available drivers that
support the database–a wire protocol driver that does not
require any database client software to connect to the
database, and a client-based driver that does require client
software. By default, the Oracle Wire Protocol driver is
installed.

Single Driver allows you to choose one from any of the
DataDirect Connect for ODBC 5.3 SP1 drivers, including the
client-based Oracle driver.

If you want to evaluate more than one single
driver, you will have the opportunity to do
so after completion of the initial installation.

1. Drivers for All Supported Databases
2. Single Driver

Please enter an option [1]:2

1. DB2 Wire Protocol
2. dBASE
3. Informix Wire Protocol
4. Oracle Wire Protocol
5. Microsoft SQL Server Wire Protocol
6. Sybase Wire Protocol
7. Teradata
8. Text
9. MySQL Wire Protocol
10. Oracle (client)

Please enter driver option:5

You have chosen to eval the Microsoft SQL Server Wire Protocol driver.
To change this information, enter C. Otherwise, press Enter to continue.:

DataDirect Technologies Product Setup is preparing the installation.

Choose a temporary directory.

Enter the full path to the temporary install directory.[/tmp]: /d01/oracle/tmp_datadirect
Checking for available space…
There is enough space.

Extracting files…

Choose a destination directory.
Enter the full path to the install directory.[/opt/odbc32v53]: /d01/oracle/odbc32v53
Checking for available space…

There is enough space.

Extracting files…

Creating license file……

DataDirect Technologies Product Setup successfully removed all of the temporary files.
Thank you for using DataDirect Technologies products.

Installation ended successfully.

Would you like to install another product (Y/N) ? [Y]N

Thank you for using DataDirect Technologies products.
Installation ended successfully.

[[email protected] data_direct]$

. . . I made changes to the ~/.bash_profile (Oracle user uses bash as its default shell). I added:

export ODBC_HOME=/d01/oracle/odbc32v53
export ODBCINI=/d01/oracle/odbc32v53/odbc.ini
export ODBCINST=/d01/oracle/odbc32v53/odbcinst.ini
export LD_LIBRARY_PATH=/d01/oracle/odbc32v53/lib:/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib:/lib:/usr/lib

Then I fixed listener.ora. Instead of using odbc64v53 as before, I moved to odbc32v53:

New:(SID_DESC =
(SID_NAME = odbc_sqlserver)
(ORACLE_HOME = /d01/oracle/product/11.1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/d01/oracle/odbc32v53/lib:/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib)
)

I was ready to run and test.


select * from [email protected];
select * from [email protected]
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'information_schema'.[DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could
not be prepared.
ORA-02063: preceding 2 lines from SQLSERVER


And the trace tells a different story:


hgopoer, line 159: got native error 170 and sqlstate 42000; message follows...

[DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ‘INFORMATION_SCHEMA.TABLES’.[DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Exiting hgopoer, rc=0 at 2008/03/25-09:20:34
hgopars, line 278: calling SQLPrepare got sqlstate 42000
Exiting hgopars, rc=28500 at 2008/03/25-09:20:34 with error ptr <a href=”///hgopars.c”>FILE:hgopars.c</a> LINE:308 FUNCTION:hgopars() ID:Prepare stmt

It turned out that dg4odbc has “hard coded” the quotes and this will need to be changed in odbc.ini. I should have looked more closely at the installation instructions — yes, the example shows QuotedId=Yes. The old:

QuotedId=No

And the new:

QuotedId=Yes

The next attempt:


select * from [email protected];
select * from [email protected]
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[DataDirect][ODBC SQL Server Driver]Connection is busy with results for another
hstmt
ORA-02063: preceding 2 lines from SQLSERVER


Now we are getting somewhere. The trace tells us that the failure happens when the connection tries to get stats for the table:

Entered hgotcis at 2008/03/25-12:18:23 Calling SQLStatistics for TABLES Entered hgopoer at 2008/03/25-12:18:23 hgopoer, line 159: got native error 0 and sqlstate HY000; message follows... [DataDirect][ODBC SQL Server Driver]Connection is busy with results for another hstmt Exiting hgopoer, rc=0 at 2008/03/25-12:18:23 hgotcis, line 384: calling SQLStatistics got sqlstate HY000

And so I did yet another workaround — I added the line:

HS_FDS_SUPPORT_STATISTICS=FALSE

to initodbc_sqlserver.ora.

SQL> select count(*) from [email protected]
  2     /
COUNT(*)
----------
     627
SQL>

Sweet! And so, when next using dg4odbc:

1. The 32-bit drivers are needed to configure Generic Connectivity.
2. In odbc.ini, change QuotedId=No to QuotedId=Yes
3. In initodbc_SQLSERVER.ora, add the line: HS_FDS_SUPPORT_STATISTICS=FALSE

Want to talk with an expert? Schedule a call with our team to get the conversation started.