Goal
This document describes how to connect to a Denodo Virtual DataPort database from an Oracle database using the Oracle Database Gateway for ODBC.
Content
Oracle Database Links allow access to distributed Oracle databases. With the help of the Oracle Database Gateways, heterogeneous data sources can also be accessed. To access the Denodo VDP Server from an Oracle Database, the Database Gateway for ODBC has to be installed and configured. Oracle Database Gateways can be downloaded by performing the following steps:
- Open a browser and navigate to the Oracle Downloads page.
- Select Oracle Software Delivery Cloud.
- Search for and download “Oracle Database Gateways”.
Prerequisites
The Denodo ODBC driver has to be installed on the machine where the Database Gateway for ODBC will be installed. The installation of the Denodo ODBC driver is described in the Denodo product documentation:
The connectivity to the Denodo VDP Server can be tested with the command isql
[oracle@linux admin]$ isql denodo_DSN +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> |
The connectivity to the Denodo VDP Server can be tested with the “Test” functionality of the ODBC Data Source administrator:
Architecture of the Oracle Database Gateway for ODBC
The architecture of the Database Gateway for ODBC is shown in the illustration below. The Oracle Database Gateway for ODBC is a separate installation which needs to integrate the Denodo ODBC driver. The Oracle database component “Heterogeneous Services(HS)” is the Generic component of all Oracle Database Gateways in Oracle database servers. The Database Gateway for ODBC has to be accessed via Oracle Net from Oracle databases.
Installation of the Database Gateway for ODBC
The best practice is to install the Database Gateway for ODBC in a separate ORACLE_HOME directory. The configuration of a dedicated Gateway listener helps to avoid Oracle release dependencies. The Oracle Database Gateway for ODBC is installed with the Oracle Installer (runInstaller in the software distribution media).
Configuration of the Database Gateway for ODBC to access the Denodo VDP server
To access the Denodo VDP Server via Oracle Database Links, several configuration files have to be configured.
First of all, a SID for the Database Gateway for ODBC has to be selected. In our example we selected the SID DNDO.
There is a sample Gateway configuration file available in the directory $ORACLE_HOME/hs/admin. The name of the file is initdg4odbc.ora.
Copy that file to the file initDNDO.ora. Now the file can be modified; Denodo_DSN is the ODBC System DSN used to connect to the Denodo VDP Server.
File initDNDO.ora
HS_FDS_CONNECT_INFO=Denodo_DSN HS_FDS_TRACE_LEVEL=off HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so HS_KEEP_REMOTE_COLUMN_SIZE=ALL # # ODBC specific environment variables # set ODBCINI=/etc/odbc.ini # # Environment variables required for the non-Oracle system ############################################################ # set hs_language in case of single byte charactersets # hs_language=american_america.WE8ISO8859P1 or WE8ISO8859P15 # set hs_language in case of a Unicode environment # hs_language=american_america.AL32UTF8 ############################################################# hs_language=american_america.WE8ISO8859P1 |
The next step is to configure a Gateway listener. For that, the file listener.ora in the $ORACLE_HOME/network/admin has to be modified.
Note that $ORACLE_HOME has to point to the Gateway home directory. In this example, we configured a listener with the name GW_LISTENER.
Please note that “SID_NAME” corresponds to the SID selected in the previous step.
File listener.ora
GW_LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=<ip address>)(PORT=1525)) )
SID_LIST_GW_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=DNDO) (ORACLE_HOME=/mnt/sdb/stage/oragw) (PROGRAM=dg4odbc) ) )
#CONNECT_TIMEOUT_GW_LISTENER = 0 |
The last step of the network configuration is the configuration of the tnsnames.ora file of the Oracle database accessing the Database Gateway for ODBC. The entry below has to be added to the tnsnames.ora file. Note that the standard location of this file is $ORACLE_HOME/network/admin but the file can be stored in a different location indicated by the TNS_ADMIN environment variable. An LDAP directory could also be used.
Entry added to the tnsnames.ora file
DNDO = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=<ip address>)(PORT=1525)) (CONNECT_DATA=(SID=DNDO)) (HS=OK) ) |
Start the Gateway listener
The Gateway listener has to be started from the Gateway environment. For that a lsnrctl start GW_LISTENER command has to be executed:
[oracle@linux admin]$ lsnrctl start GW_LISTENER
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 05-AUG-2019 17:00:01
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Starting /mnt/sdb/stage/oragw//bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 18.0.0.0.0 - Production System parameter file is /mnt/sdb/stage/oragw/network/admin/listener.ora Log messages written to /mnt/sdb/stage/diag/tnslsnr/linux/gw_listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<ip address>)(PORT=1525)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=<ip address>)(PORT=1525)) STATUS of the LISTENER ------------------------ Alias GW_LISTENER Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production Start Date 05-AUG-2019 17:00:01 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /mnt/sdb/stage/oragw/network/admin/listener.ora Listener Log File /mnt/sdb/stage/diag/tnslsnr/linux/gw_listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<ip address>)(PORT=1525))) Services Summary... Service "DNDO" has 1 instance(s). Instance "DNDO", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Create a database link
After this, a database link which points to the Oracle Database Gateway for ODBC has to be created:
In our example we will create a public database link with the name “denodo” in sqlplus.
Note that the Denodo userid and password is case sensitive. Because of that, it is necessary to enter the Denodo username and password in double quotes.The default behavior of Oracle databases are capital letters.
SQL> create public database link denodo connect to "myuser" identified by "mypassword" using 'DNDO’; SQL> Database link created. |
Test the connection
Now, the access to a view created in the Denodo VDP from an Oracle database can be tested. Note that the names in Oracle SQL Statements are not case sensitive unless double quotes are used.
SQL> select * from "my_emp"@denodo where rownum < 2
empno ename job mgr hiredate sal comm deptno ---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 12-DEC-80 800 0 20 |
References
Installing Oracle Database Gateway for ODBC
Configuring Oracle Database Gateway for ODBC
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
Recommended resources Recommendations generated by AI
Questions
