Connecting to VDP using Oracle DB links

Applies to: Denodo 8.0 , Denodo 7.0 , Denodo 6.0
Last modified on: 03 Feb 2022
Tags: Connectivity Oracle

Download document

You can translate the document:

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 installed by performing the following steps:

  • Open a browser and navigate to the Oracle Technical Resources page.
  • Select Database Downloads.
  • Select an Oracle Database version.
  • In the next screen, select “See all” next to the correct release and operating system.
  • Search for “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

Virtual DataPort Developer Guide: Access Through ODBC

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training