Connect Oracle E-Business Suite to Vertica, MySQL and other non Oracle databases

Table of Contents

Introduction

In this post I will show how to connect Blitz Report to non-Oracle databases like Vertica, MySQL, PostgreSQL using Oracle Database Gateway and Heterogeneous Services. This can be useful if your organization has information stored in databases from different vendors and you would like to use Blitz Report for consolidated E-Business Suite Reporting across these systems.

Gateway can be installed on the same server where an Oracle database resides but into a different Oracle Home, or it can be installed on a separate server. Here is the topology of the infrastructure used in this tutorial:

Diagram describing connection from Oracle to other databases via Gateway and ODBC

Install Oracle Database Gateway for ODBC

Reference

https://docs.oracle.com/cd/E11882_01/gateways.112/e12013/newoverview.htm

Download gateway installer

Download Oracle Database Gateways 11.2.0.4 for Linux x86-64. It’s available as a Patch 13390677: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER on MOS.
Download only the following archive p13390677_112040_Linux-x86-64_5of7.zip.

Install gateway

On gateway server Unzip the installer archive into a stage directory and launch runInstaller

Follow the installation screens

There are multiple Gateway isntallation options for different databases. For this tutorial Oracle Database Gateway for ODBC was installed.

When prompted to run root.sh, please run it in a separate shell session.

At the end of the installation you will be prompted for a listener configuration. Please choose the listener name and port according to your needs.

Create a gateway environment file

To make the gateway administration easier create an environment file setting the following requiered environment variables.

[oracle@gateway ~]$ cat GATEWAY.env export ORACLE_HOME=/u01/gateway/oracle_base/oracle_home export PATH=/u01/gateway/oracle_base/oracle_home/bin:$PATH export TNS_ADMIN=/u01/gateway/oracle_base/oracle_home/network/admin LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

Install the unixODBC driver manager on the gateway server

Use the following command to install unixODBC from public_ol6_latest repository:

[root@gateway yum.repos.d]# yum install unixODBC*

Verify that odbc libraries were successfully installed

[root@gateway unixODBC-2.3.9]# ls -l /usr/lib64/|grep odbc total 2912 -rwxr-xr-x 1 root root 969 Sep 17 12:35 libodbccr.la lrwxrwxrwx 1 root root 18 Sep 17 12:35 libodbccr.so -> libodbccr.so.2.0.0 lrwxrwxrwx 1 root root 18 Sep 17 12:35 libodbccr.so.2 -> libodbccr.so.2.0.0 -rwxr-xr-x 1 root root 524982 Sep 17 12:35 libodbccr.so.2.0.0 -rwxr-xr-x 1 root root 955 Sep 17 12:35 libodbcinst.la lrwxrwxrwx 1 root root 20 Sep 17 12:35 libodbcinst.so -> libodbcinst.so.2.0.0 lrwxrwxrwx 1 root root 20 Sep 17 12:35 libodbcinst.so.2 -> libodbcinst.so.2.0.0 -rwxr-xr-x 1 root root 506315 Sep 17 12:35 libodbcinst.so.2.0.0 -rwxr-xr-x 1 root root 931 Sep 17 12:35 libodbc.la lrwxrwxrwx 1 root root 16 Sep 17 12:35 libodbc.so -> libodbc.so.2.0.0 lrwxrwxrwx 1 root root 16 Sep 17 12:35 libodbc.so.2 -> libodbc.so.2.0.0 -rwxr-xr-x 1 root root 1925747 Sep 17 12:35 libodbc.so.2.0.0 drwxr-xr-x 2 root root 4096 Sep 17 12:35 pkgconfig

Configure connection to a Vertica database

Importing a Vertica community edition virtual machine

This step is optional because you may already have a functioning vertica database. In case you don’t have one you can download a Community Edition 10.0.x Virtual Machine from the following link.

Downloading Vertica Community Edition Virtual machine

The downloaded appliance can be imported into different hypervisors. I will show an example of importing into Virtual Box. Click File->Import Appliance

Importing Vertica Community Edition Virtual machine

Provide path to downloaded appliance and configure the virtual machine options

After the startup and network configuration the virtual machine is ready for use.

Install Vertica ODBC drivers on the gateway server

On the gateway server use the following instructions for the drivers installation.
Download drivers from the following link.

Install drivers

[root@gateway gateways]# mkdir -p /opt/vertica/ cp /backup/development/apavlik/temp/vertica-client-10.0.1-0.x86_64.tar.gz /opt/vertica cd /opt/vertica/ tar -xzvf vertica-client-10.0.1-0.x86_64.tar.gz -d -C /

Two folders are created: one for the include file, and one for the library files. The path of the library file depends on the processor architecture: lib for 32-bit libraries, and lib64 for 64-bit libraries. So, a 64-bit driver client download creates the directories:

/opt/vertica/include, which contains the header file
/opt/vertica/lib64, which contains the library file

Creating an ODBC data source name on the gateway server

unixODBC driver manager expects odbc.ini to be present under /etc/. So create /etc/odbc.ini file on the gateway server with the following settings:

[Vertica]
Description = Vertica Driver = /opt/vertica/lib64/libverticaodbc.so Database = vmart Servername = vertica.localdomain UID = dbadmin PWD = password Port = 5433 ConnSettings = AutoCommit = 0 Locale = en_US@collation=binary

DSN in square brackets can be set to any convenient name
Driver points to the driver library that we installed in the previous step.
Database points to vmart which is an example database that is shipped with Vertica Community Edition virtual machine.
Servername should be resolved via DNS or hosts file
UID, PWD I’m using dbadmin user and its default password in Vertica Community Edition virtual machine.
Port is 5433 which is the default port for ODBC connections in Vertica.

Configuring ODBC driver settings on on the gateway server

Instructions for this step are provided on the following link. They should be perfromed on the gateway server.

Set the system wide VERTICAINI Environment Variable

Add the following line to /etc/profile

export VERTICAINI=/etc/vertica.ini

Create vertica.ini file

Create /etc/vertica.ini with the following contents:

[Driver]
DriverManagerEncoding=UTF-16 ODBCInstLib=/usr/lib64/libodbcinst.so ErrorMessagesPath=/opt/vertica LogLevel=4 LogPath=/tmp

Create an initialization parameter file on the gateway server

You must create an initialization file for your Oracle Database Gateway for ODBC. Oracle supplies a sample initialization file, initdg4odbc.ora. The sample file is stored in the $ORACLE_HOME/hs/admin directory.

To create an initialization file for the ODBC gateway, copy the sample initialization file and rename it to initsid.ora, where sid is the system identifier (SID) you want to use for the instance of the non-Oracle system to which the gateway connects. I’ve chosen SID to be ‘vertica’.

[oracle@gateway admin]$ cd $ORACLE_HOME/hs/admin
[oracle@discoverer-enginatics admin]$ cp initdg4odbc.ora initvertica.ora
[oracle@gateway admin]$ vi initvertica.ora

HS_FDS_CONNECT_INFO = Vertica
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
set ODBCINI=/etc/odbc.ini

HS_FDS_CONNECT_INFO in the example above is set to ODBC data source name [Vertica].
HS_FDS_SHAREABLE_NAME point to the odbc library under the unixODBC driver manager installation directory.
HS_LANGUAGE a setting required to word the following connection issue around:

hgopoer, line 231: got native error 0 and sqlstate I; message follows…[
Exiting hgopoer, rc=0 at 2020/09/18-08:18:41
hgocont, line 2831: calling SqlDriverConnect got sqlstate I
Exiting hgocont, rc=28500 at 2020/09/18-08:18:41 with error ptr FILE:hgocont.c LINE:2851 FUNCTION:hgocont() ID:Something other than invalid authorization
Exiting hgolgon, rc=28500 at 2020/09/18-08:18:41 with error ptr FILE:hgolgon.c LINE:806 FUNCTION:hgolgon() ID:Calling hgocont
Entered hgoexit at 2020/09/18-08:18:41Exiting hgoexit, rc=0

Configure the gateway listener

Basic listener configuration was performed during gateway installation. In this step we need to perform additional configuraion.

SID_NAME is the name of the gateway instance dedicated to the connection to a particular data source. It is also used during initialization parameter file configuration.
ORACLE_HOME should point to the gateway oracle home.
LD_LIBRARY_PATH should include path to vertica odbc drivers libraries and lib directory under the gateway oracle home.

Add the following to $TNS_ADMIN/listener.ora

SID_LIST_GATEWAY= (SID_LIST= (SID_DESC= (SID_NAME=vertica) (ORACLE_HOME=/u01/gateway/oracle_base/oracle_home) (ENV="LD_LIBRARY_PATH=/opt/vertica/lib64:/u01/gateway/oracle_base/oracle_home/lib") (PROGRAM=dg4odbc) ) )

Restart the listener to apply the changes

lsnrctl stop GATEWAY lsnrctl start GATEWAY lsnrctl status GATEWAY

Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=discoverer-enginatics.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Services Summary... Service "vertica" has 1 instance(s). Instance "vertica", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Configure an Oracle EBS database

Configuring tnsnames.ora

A connect descriptor needs to be added for the Oracle database to know how to reach the gateway, which will then redirect the connection to Vertica. Add the following connect descriptor to tnsnames.ora or in case of EBS ifile for tnsnames.ora on the Oracle Database server:

vi /d01/oracle/VIS/db/tech_st/11.2.0.4/network/admin/VIS_r12/VIS_r12_ifile.ora

vertica= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=discoverer.enginatics.com) (PORT=1522) ) (CONNECT_DATA= (SID=vertica)) (HS=OK))

Use tnsping to verity that the connect descriptor is correct.

[oravis@r1213a VIS_r12]$ tnsping vertica

Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=discoverer.enginatics.com) (PORT=1522)) (CONNECT_DATA= (SID=vertica)) (HS=OK)) OK (10 msec)

Create a database link

Create a database link using the connect descriptor created in the previous step. In the following example I used Vertica dbadmin user with its default password.

CREATE PUBLIC DATABASE LINK vertica CONNECT TO "dbadmin" IDENTIFIED BY "password" USING 'vertica';

Test connection from Oracle to Vertica

Connect to the Oracle Instance and issue the following query using vertica db link:

select * from store.store_orders_fact@vertica

If everything is setup correcly you should get some rows selected.

If you want to select particular columns, use double quotes to specify their name in the lowercase because Oracle will try to convert them to upper case and an error will the thrown:

SQL> select product_key,product_version from store.store_orders_fact@vertica;

ERROR at line 1:
ORA-00904: "PRODUCT_VERSION": invalid identifier

The proper syntax would be:

select "product_key","product_version" from store.store_orders_fact@vertica;

Using Vertica as a data source in Blitz Report

If you need to access data from Vertica in your a Blitz Report it’s very easy to implement. Just include the vertica table name with the database link – the same syntax that you would use in any SQL access tool. Here’s the example of a simple Blitz Report selecting data from a vertica database:

Sample Blitz Report output from a Vertica database

Configure connection to PostgreSQL

Install and configure PostgreSQL for testing

This step is optional as you may already have PostgreSQL database running.

The below steps were executed in Ubuntu 18.

Install postgresql package

sudo apt-get install postgresql postgresql-contrib

After the installation postgresql service is automatically started.

Create a test user, a database and a table

sudo -u postgres createuser --login --pwprompt enginatics sudo -u postgres createdb --owner=enginatics enginatics_db

create table if not exists databases (name varchar(50)); insert into databases values ('Oracle'),('Postgres'),('MySQL'),('Vertica');

Modify a configuration file to allow remote connections

Add the following line to /etc/postgresql/10/main/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD host enginatics_db all 0.0.0.0/0 md5

The above example allows connections to enginatics_db test databases from all ip addresses.

Configure listen addresses

Modify a configuration file /etc/postgresql/10/main/postgresql.conf to allow remote network connections as by default postgresql listens only to a local address 127.0.0.1. Ensure that the following setting is present in the above file:

listen_addresses = '*'

You may adjust it to listen to only desired IP address as ‘*’ will make postgresql listen to all IP available addresses on a host.

Install PostgreSQL odbc drivers on the gateway server

Download the Repository package from yum.postgresql.org for your OS release. In my case it’s Oracle Linux 6.
Visit the following webpage
Choose PostgreSQL and OS version and architecture and the command to install the repository RPM will be provided.

Run the following command to install PostgreSQL odbc driver:

yum install postgresql12-odbc*

Edit odbc ini files on the gateway server

Add the following to /etc/odbcinst.ini

[postgres]
Description = postgres Driver = /usr/pgsql-12/lib/psqlodbc.so Setup = /usr/lib64/libodbcpsqlS.so Driver64 = /usr/pgsql-12/lib/psqlodbc.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1

Add the following to /etc/odbc.ini

[postgres]
Description = PostgreSQL test database Driver = postgres ServerName = 192.168.1.12 Username = enginatics Password = ****** Port = 5432 Database = enginatics_db Trace = yes TraceFile = /tmp/odbctrace.txt

Create an initialization parameter file on the gateway server

[oracle@gateway admin]$ cd $ORACLE_HOME/hs/admin
[oracle@gateway admin]$ cp initdg4odbc.ora initpostgres.ora

# This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC

# # HS init parameters # HS_FDS_CONNECT_INFO = postgres HS_FDS_TRACE_LEVEL = DEBUG HS_FDS_SHAREABLE_NAME =/usr/pgsql-12/lib/psqlodbc.so HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
set ODBCINSTINI=/etc/odbcinst.ini

# # Environment variables required for the non-Oracle system # #set =

Configure the gateway listener

Add a new entity for postgres to the gateway listener

vi $TNS_ADMIN/listener.ora

(SID_DESC= (SID_NAME=postgres) (ORACLE_HOME=/u01/gateway/oracle_base/oracle_home) (ENV="LD_LIBRARY_PATH=/u01/gateway/oracle_base/oracle_home/lib:/usr/lib") (PROGRAM=dg4odbc) )

Reload listener to pick up the changes.

lsnrctl reload GATEWAY

Check the satus of the listener to ensure that the new service is configured.

lsnrctl status GATEWAY

Service "postgres" has 1 instance(s). Instance "postgres", status UNKNOWN, has 1 handler(s) for this service...

Configuring tnsnames.ora for a EBS database

Add the following connect descriptor to tnsnames.ora or in case of EBS ifile for tnsnames.ora

vi /d01/oracle/VIS/db/tech_st/11.2.0.4/network/admin/VIS_r12/VIS_r12_ifile.ora

postgres= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=discoverer.enginatics.com) (PORT=1522) ) (CONNECT_DATA= (SID=postgres)) (HS=OK))

[oravis@r1213a VIS_r12]$ tnsping postgres

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=discoverer.enginatics.com) (PORT=1522)) (CONNECT_DATA= (SID=postgres)) (HS=OK)) OK (10 msec)

Create a database link in a EBS database

create public database link postgres connect to "enginatics" identified by "******" using 'postgres';

Test connection from an Oracle database to a PostgreSQL database

Execute the following statement from a database session

select * from "databases"@postgres;

You should get some rows returned.

name ---------- Oracle Postgres MySQL Vertica

Using PostgreSQL as a data source for Blitz Report

If you need to access data from PostgreSQL in your Blitz Report it’s very easy to implement. Just include the PostgreSQL table name with the database link – the same syntax that you would use in any SQL access tool. Here’s the example of a simple Blitz Report selecting data from a PostgreSQL database:

Accessing postgres from Blitz Report via Oracle gateway and database link

Sample Blitz Report output from a PostgreSQL database

Configure connection to MySQL

Install and configure a MySQL database for testing

This step is optional as you may already have MySQL database running.

The below steps were executed in Ubuntu 18.

Install MySQL package

apt install mysql-server

Configure remote connections

Change the following setting in the MySQL configuration file to allow connections from other servers as by default MySQL listens only the local address 127.0.0.1 /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 0.0.0.0

Create a test user, a database and a table

mysql create user 'enginatics'@'%' identified by 'enginatics'; grant all privileges on *.* to 'enginatics'@'%' with grant option; quit

mysql -u enginatics -p

create database enginatics_db; use enginatics_db;

create table databases_table (name varchar(50)); insert into databases_table (name) values ('Oracle'),('Postgres'),('MySQL'),('Vertica');

Edit odbc ini files on the gateway server

Ensure that the following lines were added automatically after MySQL driver installation to /etc/odbcinst.ini

[MySQL ODBC 8.0 Unicode Driver]
Driver = /usr/lib64/libmyodbc8w.so UsageCount = 1

Add the following to /etc/odbc.ini

[MySQL]
Description = MySQL test database Driver = MySQL ODBC 8.0 Unicode Driver Server = 192.168.1.12 User = enginatics Password = ***** Port = 3306 Database = enginatics_db Trace = yes TraceFile = /tmp/odbctrace.txt

Create an initialization parameter file on the gateway server

[oracle@gateway admin]$ cd $ORACLE_HOME/hs/admin

[oracle@gateway admin]$ cp initdg4odbc.ora initmysql.ora

# This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC

# # HS init parameters # HS_FDS_CONNECT_INFO = MySQL HS_FDS_TRACE_LEVEL = DEBUG HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so HS_NLS_NCHAR=UCS2 HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

# # ODBC specific environment variables # set ODBCINI=/etc/odbc.ini set ODBCINSTINI=/etc/odbcinst.ini

# # Environment variables required for the non-Oracle system # #set =

Configure the gateway listener

Add a new entity for postgres to the gateway listener

vi $TNS_ADMIN/listener.ora

(SID_DESC= (SID_NAME=mysql) (ORACLE_HOME=/u01/gateway/oracle_base/oracle_home) (ENV="LD_LIBRARY_PATH=/u01/gateway/oracle_base/oracle_home/lib:/usr/lib") (PROGRAM=dg4odbc) )

Reload listener to pick up the changes.

lsnrctl reload GATEWAY

Check the satus of the listener to ensure that the new service is configured.

lsnrctl status GATEWAY

Service "mysql" has 1 instance(s). Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...

Configuring tnsnames.ora for a EBS database

Add the following connect descriptor to tnsnames.ora or in case of EBS ifile for tnsnames.ora

vi /d01/oracle/VIS/db/tech_st/11.2.0.4/network/admin/VIS_r12/VIS_r12_ifile.ora

mysql= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=discoverer.enginatics.com) (PORT=1522) ) (CONNECT_DATA= (SID=mysql)) (HS=OK))

[oravis@r1213a VIS_r12]$ tnsping mysql

Create a database link in a EBS database

create public database link mysql connect to "enginatics" identified by "enginatics" using 'mysql';

Test connection from Oracle to PostgreSQL

Execute the following statement from a database session

select "name" from "databases_table"@mysql;

You should get some rows returned.

name -------------------------------------------------------------- Oracle Postgres MySQL Vertica

Using MySQL as a data source for Blitz Report

If you need to access data from MySQL in your Blitz Report it’s very easy to implement. Just include the MySQL table name with the database link – the same syntax that you would use in any SQL access tool. Here’s the example of a simple Blitz Report selecting data from a MySQL database:

Accessing MySQL from Blitz Report via Oracle gateway and database link

Sample Blitz Report output from a MySQL database

Configure connection to DB2

Install and configure a DB2 database for testing

This step is optional as you may already have DB2 database running.

DB2 community edition fow Windows can be downloaded at the following link. I tested the installation on Windows 10. Please find the installation screenshots below.

db2 community edition installation in Windows

Click on ‘Create sample database’

To test the successful installation and the database creation launch DB2 Command Line Processor Plus from the Windows Start menu.

Launching DB2 Command Line Processor Plus

Connect to the SAMPLE database using Windows account and password because DB2 used Windows account for creating SAMPLE database. List tables available in the SAMPLE database.

Execute a sample query from one of the tables

This database will be queried not only locally but also from a gateway server. For inbound connections from a gateway server a new rule for DB2 port 50000 needs to be added to the Windows firewall.

Install ODBC drivers for DB2

This step needs to be completed on the gateway server.

Go to the following link and provide details for the required DB2 version and the gateway operating system version.

Choose the required version of the driver for your operating system. In the example it’s driver version 11.1 for Linux 64 bit. At the time of this article preparation the latest driver version was 11.5, but some dependencies were missing for Oracle Linux 6, so I installed an older version and it worked fine with the SAMPLE database version 11.5.

Unzip the driver under a directory. I’ve chosen /opt/db2/ for the driver.

[root@discoverer-enginatics tmp]# cd /opt
[root@discoverer-enginatics opt]# mkdir db2
[root@discoverer-enginatics opt]# tar -xzvf /tmp/v11.1.4fp5_linuxx64_odbc_cli.tar.gz -C db2/

Check that libdb2.so doesn’t have missing dependencies

[root@discoverer-enginatics lib]# ldd libdb2o.so
linux-vdso.so.1 => (0x00007fff835fa000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fd413551000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fd41334d000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd413130000)
librt.so.1 => /lib64/librt.so.1 (0x00007fd412f27000)
libpam.so.0 => /lib64/libpam.so.0 (0x00007fd412d19000)
libxml2.so.2 => /usr/lib64/libxml2.so.2 (0x00007fd4129c6000)
libm.so.6 => /lib64/libm.so.6 (0x00007fd412741000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fd41243a000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fd412224000)
libc.so.6 => /lib64/libc.so.6 (0x00007fd411e8f000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007fd411c8c000)
/lib64/ld-linux-x86-64.so.2 (0x00005643924d1000)
libaudit.so.1 => /lib64/libaudit.so.1 (0x00007fd411a68000)
libz.so.1 => /lib64/libz.so.1 (0x00007fd411851000)

Edit odbc configuration files

Add a new DB2 entry to the driver manager configuration file with the following details:

vi /etc/odbcinst.ini

[DBTWO]
Description = DB2 Driver
Driver = /opt/db2/odbc_cli/clidriver/lib/libdb2o.so
FileUsage = 1
DontDLClose = 1

Also add an entry to the odbc.ini configuration file

vi /etc/odbc.ini

[DBTWO]
Description = Test to DB2
Driver = DBTWO

Unlike for the other databases it’s not enough and we need to modify a db2 specific configuration file located under the driver directory:

[root@discoverer-enginatics clidriver]# cat /opt/db2/odbc_cli/clidriver/cfg/db2cli.ini
[DBTWO]
Database = SAMPLE
Protocol = TCPIP
Hostname = 192.168.1.10
ServiceName = 50000

After the configuration is complete validate db2 driver installation using db2cli utility. Warnings about db2dsdriver.cfg can be ignored as this file is not required for successful ODBC connection.

[root@discoverer-enginatics bin]# cd /opt/db2/odbc_cli/clidriver/bin
[root@discoverer-enginatics bin]# ./db2cli validate -dsn dbtwo

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v11.1.4.5 (s1911120100/64-bit)
Client Platform : Linux/X8664
Install/Instance Path : ..
DB2DSDRIVER_CFG_PATH value:
db2dsdriver.cfg Path : ../cfg/db2dsdriver.cfg
DB2CLIINIPATH value :
db2cli.ini Path : ../cfg/db2cli.ini
db2diag.log Path : db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

Note: The validation utility could not find the configuration file
db2dsdriver.cfg. The file is searched at “../cfg/db2dsdriver.cfg”.

===============================================================================
db2cli.ini validation for data source name “dbtwo”:
===============================================================================

[ Keywords used for the connection ]

Keyword Value
—————————————————————————
DATABASE SAMPLE
PROTOCOL TCPIP
HOSTNAME 192.168.1.10
SERVICENAME 50000

===============================================================================
db2dsdriver.cfg validation for data source name “dbtwo”:
===============================================================================

Note: The validation utility could not find the configuration file
db2dsdriver.cfg. The file is searched at “../cfg/db2dsdriver.cfg”.

===============================================================================
The validation is completed.
===============================================================================

Create an initialization parameter file on the gateway server

Add an init file for the new db2 instance.

[oracle@gateway ~]$ . ./GATEWAY.env
[oracle@gateway ~]$ cd $ORACLE_HOME/hs/admin
[oracle@gateway admin]$ cp initdg4odbc.ora initdbtwo.ora

Place the following in the initdbtwo.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=DBTWO
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

#
# Environment variables required for the non-Oracle system
#
#set =

Configure the gateway listener

Add a new entry to the gateway listener.ora

(SID_DESC=
(SID_NAME=dbtwo)
(ORACLE_HOME=/u01/gateway/oracle_base/oracle_home)
(ENV=”LD_LIBRARY_PATH=/u01/gateway/oracle_base/oracle_home/lib:/usr/lib”)
(PROGRAM=dg4odbc)
)

Configuring tnsnames.ora for a EBS database

Add a connect descriptor to tnsnames.ora file on an Oracle Database side.

DBTWO=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=discoverer.enginatics.com)
(PORT=1522)
)
(CONNECT_DATA=
(SID=DBTWO))
(HS=OK))

Create a database link in a EBS database

create public database link dbtwo connect to admin identified by ***** using ‘DBTWO’;

Test connection from Oracle to PostgreSQL

From an Oracle Session execute a test query using the database link

select * from department@dbtwo;

Using DB2 as a data source for Blitz Report

If you need to access data from DB2 in your Blitz Report it’s very easy to implement. Just include the DB2 table name with the database link – the same syntax that you would use in any SQL access tool. Here’s the example of a simple Blitz Report selecting data from a DB2 database:

Sample Blitz Report output from a DB2 database

Conclusion

In this post I showed how you can easily connect your external databases to your E-Business Suite Reporting instance. Using Blitz Report gives you flexibility of getting the database from any databases system.

If you have any questions on this article, please ask them in the comments section – I’m always glad to help you.

Author: Alexander Pavlik

Find out more

Features

Discover how Blitz Report™ simplifies report creation out of the Oracle E-Business Suite.

Download

Blitz Report™ is completely free
up to 30 different reports

FAQ

What is Blitz Report™?
Why do I need Blitz Report™?