In the last steps we have configured unixODBC and Teradata ODBC Driver to access a Teradata 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/initGWVANTAGESID.ora
# HS_FDS_CONNECT_INFO: data source name from odbc.ini
HS_FDS_CONNECT_INFO = vantageexpress20
# 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/lib64/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_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 = /etc/odbc.ini
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 initGWVANTAGE.ora
(SID_NAME = GWVANTAGESID)
# PROGRAM = dg4odbc tells the listener the use the database gateway for odbc
(PROGRAM = dg4odbc)
# ENVS = ... for Teradata ODBC Driver we need an LD_PRELOAD
(ENVS = "LD_PRELOAD=/lib64/libgssapi_krb5.so.2")
)
)
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 GWVANTAGESID:
# Service "GWVANTAGESID" has 1 instance(s).
# Instance "GWVANTAGESID", 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.
# GWVANTAGECONN is my name for the connection. This name will be used in the Database Link
GWVANTAGECONN=
(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 = GWVANTAGESID)
)
# HS = OK : this is a gateway connection
(HS = OK)
)
Checking with tnsping:
tnsping gwvantageconn
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 = GWVANTAGESID)) (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 Teradata 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 GWVANTAGELINK connect to "username" identified by "password" using 'GWVANTAGELINKCONN';
-- alternative if you have not configured an TNS alias
create database link GWVANTAGELINK connect to "username" identified by "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=GWVANTAGESID))(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@GWVANTAGELINK;
-- query for all tables you can see on the target database
select * from all_tables@GWVANTAGELINK;
Case sensitive Object Names
Teradata Object names are case-sensitive, Oracle Object Names normally only have uppercase letters.
In this example, i used the Teradata Admin User “dbc”.
The query for select owner, table_name from all_tables shows us the names:
OWNER TABLE_NAME
-------------------- ----------------------------------------
DBC AccessLog
DBC AccessLogV
DBC AccessRights
DBC AccLogRules
DBC AccLogRulesV
DBC AccLogRuleTbl
DBC AccLogRuleTbl_TD16
DBC AccLogTbl
DBC AccountInfo
DBC AccountInfoV
DBC AccountInfoVX
...
846 rows selected.
To access Objects whith lowercase letters from Oracle, we habe to enclose the name with ” characters:
Example for select from Table dbc.databases:
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 Teradata Types to ODBC Types and the to Oracle Types. More Info you find in the documentation for Teradata 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 Teradata 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
| isql errormessage | Reason |
|---|---|
| [ISQL]ERROR: Could not SQLConnect | use isql -v to get a more detailed message |
| [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified | Wrong datasource in isql command |
| [28000][unixODBC][Teradata][ODBC Teradata Driver][Teradata Database] (210) The UserId, Password or Account is invalid. FailCode = -8017 [ISQL]ERROR: Could not SQLConnect | Wrong username or wrong password |