Setting up Oracle Gateway to access HANA via ODBC

[muniraju_h]

Firstly thanks to my customer for the situation that forced me to explore this scenario.

I was unable to find a simple document on this explaining such a setup. Hence this blog.

The scenario is to access HANA tables from within Oracle via database links.

Source database : SAP HANA SP 08 Rev 85

Target database : Oracle 11.2.0.3 running on RHEL 6.x X64

In middle Oracle gateway running on RHEL 6.x X64

Target oracle database will migrated to HANA in 6 months, but in the interim period we need to access HANA data sitting inside oracle seamlessly to refresh few materialized views.

After evaluating options such as CSV file export/import, ETL tools, SAP SLT, Data Services, etc, the favorable option was Oracle Gateway.

To get this Oracle Gateway running was quite a work for the first time. Therefore this blog, to help others.

The way it should works is :-

From within oracle database if a sql statement like SELECT * FROM DUMMY@H1X; is fired, it should bring the data from SAP HANA database (H1X).

First some basics, which is important to understand.

How does it work?

SQL commands are fired from a oracle database (sqlplus), which will reach out for Oracle Gateway via DBLINK > tnsnames.ora

Oracle Gateway will have parameter in its init<sid>.ora file and loads unixODBC libraries from its LD_LIBRARY_PATH.

unixODBC will load HANA odbc drivers and goes through DSN setting to read data from 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.

This is the most important to understand, else you will not be able to setup this correctly.

Step by step - How to setup the above scenario

Step 1 - First step is to make unixODBC working

Installed unixODBC rpms (both 32 and 64 bit) on 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 RHEL machine where you will run Oracle Gateway. Please refer to the HANA client installation guide.

Step 3 - Create /etc/odbc.ini   contents looks 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 environment variable of user running oracle gateway. In my case it is

     LD_LIBRARY_PATH=/usr/lib64:/usr/sap/hdbclient:/oracle/BW1/112_64/lib

Its 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 as dg4odbc (initdg4odbc.ora). The content should like 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 - Lets first test unixODBC is working

Login as user which will run oracle gateway and check LD_LIBRARY_PATH (refer step 4 above) and use 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 output you are half way through. unixODBC is working.

Now its time to work on 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 below.

dg4odbc  =

  (DESCRIPTION=

   (ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551))

   (CONNECT_DATA=(SID=dg4odbc))

   (HS=OK)

  )

And test it with tnsping

Step 11 - Create DB link in oracle database and run a SELECT command.    Commands looks like this.

CREATE PUBLIC DATABASE LINK H1X CONNECT TO

"SYSTEM" IDENTIFIED BY "password" USING 'dg4odbc';

SQL> select * from dummy@H1X;

DUMMY

--------

X

Hope this helps some one in need !