StarQuest Technical Documents

Using StarSQL with the Oracle Database Gateway for ODBC

Last Update: 4 June 2015
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ059

Abstract

The Oracle Database Gateway for ODBC (DG4ODBC), combined with StarSQL, makes it possible to integrate DB2 data with Oracle applications.

This document demonstrates how to configure and use the Database Gateway for ODBC with StarSQL.

Solution

Obtaining and installing the Gateway:

  • In Oracle 11g and later, the gateway is included in the installation of the Oracle database. In older versions, it may be a separate installation. The bitwidth of the gateway usually matches that of the database distribution, so if you install the 64-bit version of Oracle, you will need to use the 64-bit version of StarSQL.
  • An Oracle DB license includes a license for the Gateway for ODBC; no additional license is required. The Gateway does not need to be running on the same machine as the Oracle database.
  • Some literature may refer to the Gateway as DB-Link (or Database Link).
  • Previous Oracle distributions contain an older version of the gateway named "Hetergenous Services Generic Connectivity using ODBC" (hsodbc); this is a 32-bit application even in 64-bit Oracle 10g distributions. The 11g version of DG4ODBC can be installed in pre-11g environments, provided you install a patch; see Oracle tech notes for details.
  • To verify that the Gateway is installed and display its version, locate the dg4odbc executable ($ORACLE_HOME/bin/dg4odbc.exe e.g. C:\oracle\product\11.1.0\db_1\bin\dg4odbc.exe on Windows) and invoke it from a command line.

Configuring and using DG4ODBC:

  1. Create and verify a StarSQL ODBC system data source (e.g. MYDSN). On UNIX, system data sources are typically stored in /usr/local/etc/odbc.ini.
  2. Locate the $ORACLE_HOME/hs/admin directory (e.g. C:\oracle\product\11.2.0\db_1\hs\admin on Windows; /opt/oracle/product/11.2.0/dbhome_1 on UNIX ) and copy the template configuration file initdg4odbc.ora to a new file (e.g. initmysys.ora).
  3. Edit the new configuration file (initmysys.ora) and specify the name of the StarSQL ODBC data source:
  4. HS_FDS_CONNECT_INFO = MYDSN

  5. On UNIX, specify the location of the ODBC Driver Manager in $ORACLE_HOME/hs/admin/initmysys.ora - for example::

    HS_FDS_SHAREABLE_NAME=/usr/share/starsql64/odbc/lib/libodbc.so

(This example is for 64-bit StarSQL on Linux; specify the appropriate value for your StarSQL installation).

  1. On UNIX, specify HS_LANGUAGE if necessary (see Troubleshooting below):

    HS_LANGUAGE=american_america.we8mswin1252

  2. Edit $ORACLE_HOME/network/admin/listener.ora (e.g. C:\oracle\product\11.2.0\db_1\network\admin\listener.ora) and add the following. Note that a sample is available in $ORACLE_HOME/hs/admin/listener.ora.sample:

    LISTENER =
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    )

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=mysys)
    (ORACLE_HOME=C:\oracle\product\11.2.0\db_1)
    (PROGRAM=dg4odbc)
    )
    )

    On UNIX, the ENVS parameter is used to set LD_LIBRARY_PATH (Solaris & Linux), LIBPATH (AIX), or SHLIB_PATH (HP-UX) to the location of the ODBC Driver Manager. For example:

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=mysys)
    (ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1)
    (PROGRAM=dg4odbc)
    (ENVS="LD_LIBRARY_PATH=/usr/share/starsql64/odbc/lib")
    )
    )

  3. Edit $ORACLE_HOME/network/admin/tnsnames.ora (e.g. C:\oracle\product\11.2.0\db_1\network\admin\tnsnames.ora) and add the following. Be sure HS=OK comes after the closing bracket of CONNECT_DATA:

    mysys =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=mysys))
    (HS=OK)
    )

  4. Stop and restart the TNS Listener service using the Services control panel (Windows) or the commands lsnrctl stop; lsnrctl start (UNIX). Use the command lsnrctl status to verify the status of the listener.
  5. Using SQL Plus, odbctest, or another tool that allows entry of SQL statements, connect to the Oracle database as a user with sufficient authority (e.g. SYS AS SYSDBA) and create a public database link:

SQL> CREATE PUBLIC DATABASE LINK mydblink CONNECT TO "db2user" IDENTIFIED BY "db2password" USING 'mysys';

  1. To verify the link, enter the following SQL to fetch data from a table on the DB2 system:

SQL> SELECT * FROM [email protected];

Hints:

Using SQL Developer, you can view the properties of the database link. It also includes a "Test Database Link" function; in order for that test to succeed, a table named "dual" must exist in the user's default schema on the DB2 host database.

Creating a view in the Oracle database allows you to examine the table structure and data using Oracle SQL Developer - e.g.

SQL> CREATE VIEW MYVIEW AS SELECT * FROM [email protected];

The connection remains established until the Oracle session ends. Another session or user can access the same database link and get a distinct connection to the gateway and ODBC data source. On Windows, each connection will appear as a dg4odbc.exe task in Windows Task Manager, running as user SYSTEM (or whatever user ID the Oracle service runs as). Similarly, on UNIX you can use ps and look for dg4odbc processes.

UNIX considerations:

  • The 64-bit Gateway passes 64-bit values to SQLLEN/SQLULEN ODBC API parameters, and therefore must be used with a 64-bit SQLLEN/SQLULEN ODBC driver manager; it is important to use unixODBC 2.2.14 or later (supplied with StarSQL) in 64-bit environments. Note that many Linux distributions ship an older version of unixODBC.
  • On HP-UX PA-RISC, dg4odbc is a 32-bit application.
  • On AIX, the Gateway is linked with the ODBC driver manager as libodbc.so rather than libodbc.a. If you are using unixODBC as shipped by StarQuest, you will need to extract libodbc.so from libodbc.a using the ar command.

Troubleshooting and Known Issues:

Tracing:

To enable DG4ODBC tracing, edit $ORACLE_HOME/hs/admin/initmysys.ora and set HS_FDS_TRACE_LEVEL to a value between 1 (less detail) and 4 (maximum detail). Trace files will be created in the $ORACLE_HOME/hs/log (or trace) directory.

Because the Gateway runs as part of the Oracle service, capturing DRDA and ODBC traces on Windows can be challenging.

We recommend using the StarPipes Gateway to capture DRDA traces on Windows.

On UNIX, enable DRDA tracing by adding the following to etc/swodbc.ini in the StarSQL program directory:

[Tracing]
DRDA = ON
DRDATraceFile = /tmp/trace.sqd

To capture an ODBC trace on Windows, open the tracing panel of ODBC Administrator and select Machine-wide Tracing. The Log file path is ignored; the ODBC trace will be created as SQL.LOG in the \Windows\temp directory.

To capture an ODBC trace on UNIX, set the following in /usr/local/etc/odbcinst.ini:

[ODBC]
Trace = 1
Trace File = /tmp/sql.log

On UNIX systems you can use strace (Linux) or truss (other platforms) to capture the system calls made by dg4odbc. You will need to identify the process ID of the tns listener process.

# ps -eaf | grep tns
# strace -fae -o /tmp/dg4odbc.log -p <process ID>
OR
# truss -fae -o /tmp/dg4odbc.log -p <process ID>

Error Conditions - environment variables:

If you get the following error on UNIX, be sure that the ENVS parameter has been specified in listener.ora:

[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

If necessary, add the environment variable _STARSQL32 or _STARSQL64 to the ENVS line in listener.ora - e.g.:

Linux: (ENVS="LD_LIBRARY_PATH=/usr/share/starsql64/odbc/lib, _STARSQL64=/usr/share/starsql64")

Solaris: (ENVS=LD_LIBRARY_PATH=/opt/starsql64/odbc/lib:/opt/starsql64/lib:/u01/app/oracle/product/11.2.0/dbhome_1/lib, LD_LIBRARY_PATH_64=/opt/starsql64/odbc/lib:/opt/starsql64/lib:/u01/app/oracle/product/11.2.0/dbhome_1/lib,_STARSQL64=/opt/starsql64

Invalid tnsnames.ora configuration

Issue: If you get this error:

HS Agent diagnosed error on initial communication,
probable cause is an error in network administration
Network error 2: NCR-00002: NCR: Invalid usage
HS Gateway: NULL connection context at exit

Solution: Verify the configuration in tnsnames.ora; the location of HS=OK should be after the closing bracket of CONNECT_DATA:

WRONG:
mysys =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=mysys)
(HS=OK))
)

CORRECT:
mysys =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=mysys))
(HS=OK)
)

Connection problems:

Issue: If you get the following connection error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[

Solution: edit $ORACLE_HOME/hs/admin/initmysys.ora and specify an appropriate value for HS_LANGUAGE

HS_LANGUAGE=american_america.we8mswin1252
# HS_LANGUAGE=AL32UTF8

Incomplete data:

The following tip is applicable only to UNIX users using older versions of StarSQL; we recommend that Windows and Linux users, especially on 64-bit platforms, use StarSQL 6.11 or later; other UNIX users should use StarSQL 5.63 or later.

Issue: A customer using 64-bit Oracle 11g on UNIX encountered a problem where the ODBC connection was terminated abruptly before retrieving all available data.

Solution: Upgrade to StarSQL 5.63 or later, or configure setting the following settings in $ORACLE_HOME/hs/admin/initmysys.ora:

HS_FDS_FETCH_ROWS=1
HS_RP_C_FETCH_REBLOCKING=OFF

Problems when using WHERE clause (AlwaysWide=Y):

If StarSQL 6.x is configured for AlwaysWide=Y, you may get the following error when using a WHERE clause in an UPDATE or DELETE statement. In addition, using a WHERE clause in a SELECT statement will fail to find any matching rows. We recommend configuring AlwaysWide=N (default) in the StarSQL data source.

ORA-02070: database MYDBLINK does not support some function in this context

Performance Tips

  • Insert multiple rows with one statement.
  • Use the DBMS_HS_PASSTHROUGH Oracle PL/SQL package to configure the passthrough SQL feature. Statements are sent directly to the DB2 system without being interpreted by the Oracle server.


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.