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 allows the Oracle database to connect to non-Oracle databases for DML operations.

In Oracle 11g, the Database Generic Connectivity for ODBC (dg4odbc) is the replacement for the older hsodbc. After following the standard setup instructions, I configured a database link to a remote SQL Server database named SQLSERVER. I tested the connection with the following query:

SQL> select * from information_schema.tables@SQLSERVER; 

The 64-bit Trap on Linux

Since I was running 11g on 64-bit Linux, I initially installed 64-bit DataDirect drivers. While the demoodbc executable worked, the Oracle query failed:

ORA-28545: error diagnosed by Net8 when connecting to an agent ORA-02063: preceding 2 lines from SQLSERVER 

The listener trace provided a vital clue: TNS-12518: TNS:listener could not hand off client connection

The issue turned out to be that dg4odbc is a 32-bit Application, even when installed on 64-bit Linux. Running it manually confirmed the missing libraries:

[oracle@myhost ~]$ dg4odbc dg4odbc: error while loading shared libraries: libagtsh.so: cannot open shared object file 

Setting the Correct Library Path

To fix this, the LD_LIBRARY_PATH must prioritize 32-bit libraries. I updated the environment and the listener.ora file:

Updated listener.ora entry:

(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)  ) 

Transitioning to 32-bit Drivers

Attempting to use the 64-bit libraries with the 32-bit dg4odbc resulted in an ORA-28500 error, with the trace showing: Failed to load ODBC library symbol: /d01/oracle/odbc64v53/lib/libodbc.so(SQLAllocHandle)

I proceeded to install the 32-bit DataDirect Microsoft SQL Server Wire Protocol driver.

After installation, I updated the ~.bash_profile.

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

Solving Syntax and Connectivity Issues

Enabling Quoted Identifiers

Even with 32-bit drivers, I encountered a syntax error: [DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'INFORMATION_SCHEMA.TABLES'

It turns out dg4odbc hard-codes quotes for identifiers. This requires an update to the odbc.ini file:

• Old: QuotedId=No
• New: QuotedId=Yes

Disabling Statistics Support

The final hurdle was a "Connection is busy" error: [DataDirect][ODBC SQL Server Driver]Connection is busy with results for another hstmt

This happens when the connection attempts to fetch table statistics. The fix is to add a specific parameter to the initodbc_sqlserver.ora file: HS_FDS_SUPPORT_STATISTICS=FALSE

Successful Test

SQL> select count(*) from information_schema.tables@SQLSERVER;   COUNT(*)  ----------        627 

Key Takeaways for dg4odbc

• Architecture: Use 32-bit drivers for Generic Connectivity, even on 64-bit Linux.
  
  
• Configuration: Always set QuotedId=Yes in your odbc.ini.
  
  
• Initialization: Add HS_FDS_SUPPORT_STATISTICS=FALSE to your gateway init file to avoid hstmt busy errors.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?