Contents

In the last steps we have configured unixODBC and Informix ODBC Driver to access a Informix target database.
Now we will configure the Oracle Gateway.

Usually the gateway will run on the same system where the Oracle database in running.

Configure Oracle Gateway

For each connection we will configure

the gateway initialization file $ORACLE_HOME/hs/admin/initGWSID.ora
the oracle net listener
tnsnames.ora (optional)
a database link

Gateway initialization file

Oracle supplies a sample initialization file, initdg4odbc.ora. The sample file is stored in the $ORACLE_HOME/hs/admin directory.

For this example we will create a file $ORACLE_HOME/hs/admin/initGWINFORMIXSID.ora

# HS_FDS_CONNECT_INFO: data source name from odbc.ini
HS_FDS_CONNECT_INFO = infoxmixtestdsn

# HS_FDS_TRACE_LEVEL: Values: OFF, ON, DEBUG, default: OFF. Activate to get a trace file in $ORACLE_HOME/hs/log/
#HS_FDS_TRACE_LEVEL = DEBUG

# HS_FDS_SHAREABLE_NAME : full path of the ODBC driver
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

# change the characterset if you have problems
# HS_LANGUAGE should be set exactly the same as Oracle database
## HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252

# If you get ORA-28528: Heterogeneous Services datatype conversion error
# then try HS_FDS_SQLLEN_INTERPRETATION=32
# Problem occurs, if you use a 32Bit unixODBC on a 64Bit System
# (see Oracle Support Doc ID 554409.1)
# HS_FDS_SQLLEN_INTERPRETATION=32

HS_FDS_TIMESTAMP_MAPPING=DATE
HS_FDS_REPORT_REAL_AS_DOUBLE=TRUE
HS_NLS_NCHAR=UCS2
#
# ODBC specific environment variables
#
# ODBCINI: full path of odbc.ini
set ODBCINI = /usr/local/etc/odbc.ini
set INFORMIXDIR=/opt/IBM/informix
set DELIMIDENT=y

Listener configuration

On the system where the gateway is configured, you need to add some lines to the file $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      # full path of ORACLE_HOME here
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      # SID_NAME from initGWINFORMIXSID.ora
      (SID_NAME = GWINFORMIXSID)
      # PROGRAM = dg4odbc tells the listener the use the database gateway for odbc
      (PROGRAM = dg4odbc)
      # ENVS = ... for Informix ODBC Driver we have to extend the LD_LIBRARY_PATH
      (ENVS ="LD_LIBRARY_PATH=/opt/IBM/informix/lib/esql")
    )
  )

If you have already the section SID_LIST= in your file listener.ora the you just add the marked lines.
If dont have the section, you add all lines.

Important: After changing the file listener.ora you have to reload the listener:

lsnrctl reload

Checking the listener:

lsnrctl status
# you should see the GWINFORMIXSID:
#  Service "GWINFORMIXSID" has 1 instance(s).
#    Instance "GWINFORMIXSID", status UNKNOWN, has 1 handler(s) for this service...

tnsnames.ora configuration

This step is optional, TNS information also may be specified in the database link.

On the system where the database is running, we add some lines to the file $ORACLE_HOME/network/admin/tnsnames.ora.

# GWINFORMIXCONN is my name for the connection. This name will be used in the Database Link
GWINFORMIXCONN=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)
         # HOST: system where the gateway is running (or localhost if gateway and database host are the same)
         (HOST = gatwewayserver)
         # PORT: portnumber of listener (usually 1521)
         (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      # Gateway SID from listener.ora
      (SID = GWINFORMIXSID)
    )
    # HS = OK : this is a gateway connection
    (HS = OK)
  )

Checking with tnsping:

tnsping gwinformixconn

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 19-JAN-2016 18:56:54

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

Used parameter files:


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

The tnsping command only verifies the configuration in tnsnames.ora and the listener configuration on the gateway server. It shows, that the listener can be reached and the SID is configured in the listener.

It show OK also when something after the listener is misconfigured or not working.

Database Link configuration

The database link will be created by the oracle schema user who will use it. As an alternative you may create a public database link.

For creation we need:

Username and Password for the target database (e.g. the Informix database)
The TNS alias configured in tnsnames.ora

Your Oracle DB Admin should grant CREATE DATABASE LINK and ALTER DATABASE LINK to your schema user.

-- replace user and password with your values
create database link GWINFORMIXLINK connect to "username" identified by "password" using 'GWINFORMIXCONN';
-- alternative if you have not configured an TNS alias
create database link GWINFORMIXLINK connect to "username" identified by "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=GWINFORMIXSID))(HS=OK))';

Configuration finished!

Using the database link

Now we are ready to test the database link

-- the simplest test. if you get a result, the connection to the target database works.
select 'it works' as status from dual@GWINFORMIXLINK;

-- query for all tables you can see on the target database
select * from all_tables@GWINFORMIXLINK;

Case sensitive Object Names

SQL Server Object names are case-sensitive, Oracle Object Names normally only have uppercase letters.
In this example, the target Informix database has installed a sample database xyz.
The query for all_tables shows us the names:

OWNER                          TABLE_NAME
------------------------------ ------------------------------
informix                       actor
informix                       address
informix                       category
informix                       city
informix                       country
informix                       customer
informix                       film
informix                       film_actor
informix                       film_category
informix                       film_text
informix                       inventory
informix                       language
informix                       payment
informix                       rental
informix                       staff
informix                       store

16 rows selected.

To access Objects whith lowercase letters from Oracle, we habe to enclose the name with ” characters:
Example for select from Table film:

COLUMN "film_id" FORMAT 9999;
COLUMN "title" FORMAT A20;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SELECT "film_id", "title", "last_update" FROM "film"@GWINFOMIXLINK WHERE "film_id" <=10;

film_id title                last_update
------- -------------------- -------------------
      1 ACADEMY DINOSAUR     2006-02-15 05:03:42
      2 ACE GOLDFINGER       2006-02-15 05:03:42
      3 ADAPTATION HOLES     2006-02-15 05:03:42
      4 AFFAIR PREJUDICE     2006-02-15 05:03:42
      5 AFRICAN EGG          2006-02-15 05:03:42
      6 AGENT TRUMAN         2006-02-15 05:03:42
      7 AIRPLANE SIERRA      2006-02-15 05:03:42
      8 AIRPORT POLLOCK      2006-02-15 05:03:42
      9 ALABAMA DEVIL        2006-02-15 05:03:42
     10 ALADDIN CALENDAR     2006-02-15 05:03:42

10 rows selected.

Data Type Conversion

Data Types are converted from Informix Types to ODBC Types and the to Oracle Types. More Info you find in the documentation for Informix ODBC Driver, unixODBC and Oracle Gateways.

To see, what data type we get, we can query all_tab_columns:

COLUMN column_name FORMAT A30
COLUMN data_type FORMAT A8
COLUMN data_length FORMAT 99999
COLUMN data_precision FORMAT 999
COLUMN nullable FORMAT A1

SELECT column_name, data_type, data_length, data_precision, nullable FROM all_tab_columns@GWINFORMIXLINK WHERE table_name='film';

COLUMN_NAME                    DATA_TYP DATA_LENGTH DATA_PRECISION N
------------------------------ -------- ----------- -------------- -
film_id                        NUMBER             2             22 Y
title                          VARCHAR2         765                N
description                    LONG           65535                Y
release_year                   NUMBER             1             22 Y
language_id                    NUMBER             1             22 N
original_language_id           NUMBER             1             22 Y
rental_duration                NUMBER             1             22 N
rental_rate                    NUMBER             6             22 N
length                         NUMBER             2             22 Y
replacement_cost               NUMBER             7             22 N
rating                         CHAR              15                Y
special_features               CHAR             162                Y
last_update                    DATE              16                Y

13 rows selected.

Synopsis of names used in this example

In the configuration many names are used. This will be sometime confusing. Here you see all names:

Name	Purpose	defined in	used in	case sensitive?
`GWMYQLLINK`	database link name	create database link ...	sql queries, eg. select from table@GWINFORMIXLINK*	no
`GWINFORMIXCONN`	TNS alias	`tnsnames.ora`	USING clause of database link	no
`GATEWAYSERVER`	Hostname where Gateway is installed	`tnsnames.ora`		no
`GWINFORMIXSID`	Gateway SID	`listener.ora`	`tnsnames.ora` and filename of `initGWSID.ora`	YES
`INFORMIXDSN`	ODBC Datasource Name	`odbc.ini`	HS_FDS_CONNECT_INFO in file `initGWSID.ora`	no
`ol_informix1210`	Informix DBSERVERNAME	Informix Database Server	`sqlhosts` `odbc.ini`	no
`delphi`	Informix Database name	Informix Database Server	`odbc.ini`	no

There are some limitations on name length and case sensitiveness.
You are free to use own names, even you can use for all objects the same name but you should know what your are doing.

Troubleshooting

For a gateway connection, there are many components involved:

the oracle database
the database link
the TNS Alias in tnsnames.ora
the network connection between database and gateway
the listener configuration in listener.ora
the gateway configuration file initGWSID.ora
the unixODBC installation
the unixODBC configuration file odbc.ini
the Informix ODBC Driver installation
the network connection to target database server
the target database

If the connection does not work, always first test with isql and try to isolate the problem.

Some typical errormessages

Errormessage	Reason
`ORA-12154: TNS:could not resolve the connect identifier specified`	TNS Alias used in the database link not found in tnsnames.ora
`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 TESTLINK`	Mismatch between tnsnames.ora and listener.ora (e.g. wrong SID, wrong port number)
`ORA-28500: connection from ORACLE to a non-Oracle system returned this message: ORA-28541: Error in HS init file on line 39. ORA-02063: preceding 2 lines from TESTLINK`	error in file `$ORACLE_HOME/hs/admin/initGWSID.ora`
`ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Driver Manager]Data source name not found, and no default driver specified {IM002} ORA-02063: preceding 2 lines from TESTLINK`	Mismatch between data source name in `odbc.ini` and HS_FDS_CONNECT_INFO in `initGWSID.ora` Also check the ODBCINI setting in`initGWSID.ora`
`ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Driver Manager]Can't open lib '/opt/IBM/informix/lib/cli/iclit09b.so' : file not found {01000} ORA-02063: preceding 2 lines from GWINFORMIXLINK`	File `listener.ora`: Entry ENVS= missing or incorrect. After editing `listener.ora` you have to reload or restart the listener.
`ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Informix][Informix ODBC Driver][Informix]Attempt to connect to database server (ol_informix1210) failed. {08004,NativeErr = -908} ORA-02063: preceding 2 lines from GWINFORMIXLINK`	Informix Database not running. This error should also occur when testing with isql