StarQuest Technical Documents

Using StarSQL with the Oracle Database Gateway for ODBC

Last Update: 24 April 2020
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
  
• Configuring and using DG4ODBC
  
• Hints
  
• UNIX considerations
  
• Troubleshooting and Known Issues
  
• Performance Tips
  

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:
   

HS_FDS_CONNECT_INFO = MYDSN

4. 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).

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

   HS_LANGUAGE=american_america.we8mswin1252

6. 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 must be 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")
   )
   )

   On UNIX, the case of the SID_NAME specified in listener.ora should match the case of the init filename in hs/admin. e.g. if SID_NAME=MYSYS, then the hs/admin file must be named initMYSYS.ora, not initmysys.ora.

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

8. Confirm that network/admin/listener.ora and tnsnames.ora are using hostnames rather than IP address.
9. Stop and restart the TNS Listener service using the Services control panel (Windows) or the commands lsnrctl stop; lsnrctl start (UNIX). Use the commands lsnrctl status and tnsping mysys to verify the status of the listener.
10. 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. If you are using an Oracle multitenant container database (CDB ) available in Oracle 12 & later, create the public database link in the environment where you plan to use it; in most cases, this will be a PDB.:

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

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

SQL> SELECT * FROM MYTAB@mydblink;

Hints

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.

 

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 MYTAB@mydblink;

Metadata from the DB2 database is displayed as if it were an Oracle database. For example, using the desc statement in SQLPLUS will convert the column information to Oracle data types:

SQL> desc myschema.mytab@MYDBLINK;
Name Null? Type
FLD1       NUMBER(10)

Accessing a non-Oracle data dictionary table or view is identical to accessing a data dictionary in an Oracle database. You issue a SELECT statement specifying a database link. The Oracle data dictionary view and column names are used to access the non-Oracle data dictionary. Synonyms of supported views are also acceptable.

For example, the following statement retrieves a list of all schemas in the DB2 system:

SQL> SELECT * FROM all_users@MYDBLINK;

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

No message displayed for 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

Error due to case mismatch

Symptom: SQL statement fails with:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from MYSYS

and there is no useful information in the HS log

Solution:
Make sure that the case of the name matches between the SID defined in listener.ora and the filename of the HS init file - e.g. this error might occur on UNIX if listener.ora contains SID=MYSYS but the filename is hs/admin/initmysys.ora.

However, most other uses of the name are case-insensitive e.g.
tnsping mysys is the same as tnsping MYSYS
SELECT * FROM MYTAB@mydblink is the same as SELECT * FROM MYTAB@MYDBLINK

Error due to port or hostname mismatch

Symptom: SQL statement fails with:

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535

and an HS log is not produced.

Solution:
This condition may occur if there is a problem with port or hostnames. In this case, tnsping will also likely fail. We have encountered this error when listener.ora and tnsnames.ora was configured with IP address rather than a hostname, or when the wrong port was specified for the entry added to tnsnames.ora.

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.