How does it work?
SQL commands are fired from an oracle database (sqlplus), which will reach out to Oracle Gateway via DB LINK > tnsnames.ora
Oracle Gateway will have a parameter in its init<sid>.ora file and load unixODBC libraries from its LD_LIBRARY_PATH.
unixODBC will load HANA odbc drivers and goes through DSN setting to read data from the HANA database.
Meaning Oracle DB > DBLINK > tnsnames.ora > Oracle Gateway > unixODBC drivers > Data source DSN (odbc.ini) > HANA odbc drivers > HANA DB
If you notice above Oracle Gateway and HANA odbc do not talk to each other directly. Instead, talk through unixODBC drivers.
Step 1 –
The first step is to make unixODBC working
Installed unixODBC RPMs (both 32 and 64-bit) on the RHEL machine where you will run Oracle Gateway.
unixODBC-2.2.14-11.el6.x86_64
unixODBC-devel-2.2.14-11.el6.i686
unixODBC-devel-2.2.14-11.el6.x86_64
unixODBC-2.2.14-11.el6.i686
Step 2 –
Install SAP HANA client (64bit) on the RHEL machine where you will run Oracle Gateway. Please refer to the HANA client installation guide.
Step 3 –
Create /etc/odbc.ini contents look like below
[H1X]
Driver=/usr/sap/hdbclient/libodbcHDB.so
ServerNode=serverhana:30015
Step 4 –
Install Oracle Gateway software. You may please google to find step-by-step on how to install using Oracle Universal Installer.
Step 5 –
Set the environment variable of the user running the oracle gateway. In my case it is
LD_LIBRARY_PATH=/usr/lib64:/usr/sap/hdbclient:/oracle/BW1/112_64/lib
It is very important to have 64bit unixODBC libraries path (/usr/lib64/libodbc.so) in LD_LIBRARY_PATH because we run RHEL x64 and Oracle 64-bit software.
Step 6 –
Create init<sid>.ora. In my case, I will call this dg4odbc (initdg4odbc.ora). The content should be like below.
HS_DB_NAME = H1X
HS_FDS_CONNECT_INFO = H1X <===== This is the DSN name that comes from step 2 /etc/odbc.ini
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR=UCS2
HS_FDS_TRANSACTION_MODEL=READ_ONLY
set ODBCINI=/etc/odbc.ini
Step 7 –
Create listener.ora
LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551)) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=dg4odbc) (ORACLE_HOME=/oracle/BW1/112_64) (PROGRAM=dg4odbc) (ENV=”LD_LIBRARY_PATH=/usr/lib64:/oracle/BW1/112_64/lib:”) ) )
Step 8 –
Start listener
lsnrctl start
Step 9 –
Let’s first test if unixODBC is working
Log in as the user which will run oracle gateway and check LD_LIBRARY_PATH (refer to step 4 above) and use the below commands.
isql -v <DSN name from step 2 /etc/odbc.ini> <hana user name> <password>
For example isql -v H1X SYSTEM password
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> select * from dummy
+——+
| DUMMY|
+——+
| X |
+——+
SQLRowCount returns 1
1 rows fetched
If you see these outputs you are halfway through. unixODBC is working.
Now it’s time to work on the oracle database from where data will be read with SELECT statements.
Step 10 –
Add entries in tnsnames.ora In my case it will look like the below.
dg4odbc = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK) )
And test it with tnsping
Step 11 –
Create a DB link in the oracle database and run a SELECT command. Commands look like this.
CREATE PUBLIC DATABASE LINK H1X CONNECT TO “SYSTEM” IDENTIFIED BY “password” USING ‘dg4odbc’; SQL> select * from dummy@H1X; DUMMY ——– X
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
