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 !

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. Camil Panaitescu

    Hello Muniraju,

    Thank you for your procedure. We already configured it all, in  the same way, using HANA Client ODBC 9.1. It works smooth for a “pure” select * from table@DBLINK” , but when addining a “WHERE” clause, table fiels values for Hana type “string”, “int”, etc. get truncated, the only one correct translated being these of type “RAW”. Does the select with WHERE clause work for you?

    Thank you,

    Camil

    (0) 
    1. Muniraju Hanumanthiah Post author

      Hello Camil,

      I tried to pull entries from T000@DBLINK, everything seems fine.

      All the fields of T000 on HANA side are of datatype STRING.

      Let me know if you want me to test something specific.

      I used below commands with different where conditions.

      ———————————————————

      set lines 250
      column MANDT format A3
      column MTEXT format A20
      column ORT01 format A20
      column MWAER format A3
      column ADRNR format A5
      column CHANGEUSER format A9
      column CHANGEDATE format A9
      column LOGSYS format A11

      select mandt, mtext, ORT01, MWAER, ADRNR, CCCATEGORY, CCCORACTIV, CCNOCLIIND, CCCOPYLOCK, CCNOCASCAD, CCSOFTLOCK, CCORIGCONT, CCIMAILDIS, CCTEMPLOCK, CHANGEUSER, CHANGEDATE, LOGSYS from t000@HANA;
      select mandt, mtext, ORT01, MWAER, ADRNR, CCCATEGORY, CCCORACTIV, CCNOCLIIND, CCCOPYLOCK, CCNOCASCAD, CCSOFTLOCK, CCORIGCONT, CCIMAILDIS, CCTEMPLOCK, CHANGEUSER, CHANGEDATE, LOGSYS from t000@HANA where mandt < 200;
      select mandt, mtext, ORT01, MWAER, ADRNR, CCCATEGORY, CCCORACTIV, CCNOCLIIND, CCCOPYLOCK, CCNOCASCAD, CCSOFTLOCK, CCORIGCONT, CCIMAILDIS, CCTEMPLOCK, CHANGEUSER, CHANGEDATE, LOGSYS from t000@HANA where mandt = 100;
      select mandt, mtext, ORT01, MWAER, ADRNR, CCCATEGORY, CCCORACTIV, CCNOCLIIND, CCCOPYLOCK, CCNOCASCAD, CCSOFTLOCK, CCORIGCONT, CCIMAILDIS, CCTEMPLOCK, CHANGEUSER, CHANGEDATE, LOGSYS from t000@HANA where mandt like ‘%00%’;
      select mandt, mtext, ORT01, MWAER, ADRNR, CCCATEGORY, CCCORACTIV, CCNOCLIIND, CCCOPYLOCK, CCNOCASCAD, CCSOFTLOCK, CCORIGCONT, CCIMAILDIS, CCTEMPLOCK, CHANGEUSER, CHANGEDATE, LOGSYS from t000@HANA where mtext = ‘SAP AG’;

      spool off

      ——————————————-

      The output is as below

      ——————————————-

      MAN MTEXT                ORT01                MWA ADRNR CCC CCC CCN CCC CCN CCS CCO CCI CCT CHANGEUSE CHANGEDAT LOGSYS
      — ——————– ——————– — —– — — — — — — — — — ——— ——— ———–
      000 SAP AG               Walldorf             INR       S   1       X                       CMC56614  20141112
      066 Early Watch          Walldorf             INR       S   2   3   X   X                   TESTD     20081212
      200 BW Production        Bangalore            INR       T   1                               CMC56614  20141112  BWP200

      MAN MTEXT                ORT01                MWA ADRNR CCC CCC CCN CCC CCN CCS CCO CCI CCT CHANGEUSE CHANGEDAT LOGSYS
      — ——————– ——————– — —– — — — — — — — — — ——— ——— ———–
      000 SAP AG               Walldorf             INR       S   1       X                       CMC56614  20141112
      066 Early Watch          Walldorf             INR       S   2   3   X   X                   TESTD     20081212

      MAN MTEXT                ORT01                MWA ADRNR CCC CCC CCN CCC CCN CCS CCO CCI CCT CHANGEUSE CHANGEDAT LOGSYS
      — ——————– ——————– — —– — — — — — — — — — ——— ——— ———–
      000 SAP AG               Walldorf             INR       S   1       X                       CMC56614  20141112

      MAN MTEXT                ORT01                MWA ADRNR CCC CCC CCN CCC CCN CCS CCO CCI CCT CHANGEUSE CHANGEDAT LOGSYS
      — ——————– ——————– — —– — — — — — — — — — ——— ——— ———–
      000 SAP AG               Walldorf             INR       S   1       X                       CMC56614  20141112
      200 BW Production        Bangalore            INR       T   1                               CMC56614  20141112  BWP200

      MAN MTEXT                ORT01                MWA ADRNR CCC CCC CCN CCC CCN CCS CCO CCI CCT CHANGEUSE CHANGEDAT LOGSYS
      — ——————– ——————– — —– — — — — — — — — — ——— ——— ———–
      000 SAP AG               Walldorf             INR       S   1       X                       CMC56614  20141112

      Kind Regards,

      Muniraju

      (0) 
      1. Anni Pang

        hi Muniraju,

        I have problems when test the dblink.

        (1) It works fine when execute a simple query like select mandt from sap_qas.afko_qas@sapqas where rownum<3,

        (2) But no results returned after added where condition like select * from sap_qas.afko_qas@sapqas where aufnr=’000009029498′ and mandt=’800′, it just kept hanging.

        (3) It took very long time to return 390 entries (interrupted it..)  if I executed select mandt,aufnr,dispo from sap_qas.afko_qas@sapqas where mandt=’800′ and dispo=’C13′;

        could you suggest?

        thanks,

        Anni

        (0) 
        1. M. H from NXP

          Hello Anni,

           

          Please read all the comments below. You may find a solution.

          To me it seems something is not correct in your configuration.

           

          Please check below points in specific.

           

          Its important to understand that Oracle Gateway will go through unixODBC and do not directly interface with HDB odbc driver.

           

          LD_LIBRARY_PATH should be pointed to /usr/lib64

           

          See in step 6 above, the HS_FDS_SHAREABLE_NAME is pointing to 64bit unixODBC library /usr/lib64/libodbc.so

           

           

          Kind Regards,

          Muniraju

          (0) 
    2. Muniraju Hanumanthiah Post author

      I have also tested with STRING, INT, FIXED with where conditions and I get complete data, without truncation.

      SQL> select ohrequid, conf_qty, doc_number, “/BIC/SSS_DORN” from “/BIC/OHSSS_OI20″@HANA where OHREQUID = 682475 and DOC_NUMBER=1723794;

        OHREQUID   CONF_QTY DOC_NUMBER                     /BIC/SSS_DORN
      ———- ———- —————————— ————-
         682475       3000 001723794                             .0096
         682475             0 001723794                            0

      (0) 
  2. Camil Panaitescu

    Dear Muniraju, Thank you so much for your fast reply. SAP answered to our incident indicating to use (HANA) revision higer then 8.3. We do use HANA Client/ODBC 9.1 (tried also with 8.5), but the “where clause” trucates values, e.g. mandt get “4” instead of “421”.  We do have the Hana Database on 8.2, and we will request its revison upgrade to >=8.5. Don’t know if this will work, since the error log points to the HANA driver, not to the HANA DB. The remote native HANA hdbsql works OK with the where clause. Thus, your Oracle DB and HANA client are installed on RHEL 6.x X64. Would you please let me know which HANA Client / ODBC do you use? I assume it is “Revision 9.1 or 8.5 for HANA Client 1.0″ for Linux on x86_64bit”, right? Thank you, Kind Regards, Camil Panaitescu

    (0) 
    1. Muniraju Hanumanthiah Post author

      Hello Camil,

      We use HANA server at patch level 08 revision 85.

      HANA client 90.

      1. Can you paste here your odbc.ini ?

      2. Paste the LD_LIBRARY_PATH of the user running oracle gateway?

      3. Also test as per step-9 above is working?

      Kind Regards,

      Muniraju

      (0) 
  3. Camil Panaitescu

    Hello Muniraju, As per your info and SAP’s, we will “wait” until our HANA db revision will get upgraded from 8.2 to 8.51 which is scheduled to happen by the end of January. Then, we will try again the select * with a “where” clause, and hopefully that will solve our current data truncation problem. We will keep you posted. /etc/odbc.ini: [SID] Driver = /u01/app/oracle/sap/hdbclient/libodbcHDB.so ServerNode = .:315 Thank you so much for your help. Kind Regards, Camil

    (0) 
    1. Muniraju Hanumanthiah Post author

      Hello Camil,

      I also had the data truncation problem and opened case with SAP. They did ask me to patch HANA server to revision 85. Whereas that did not solve the problem.

      The basic configuration was the problem.

      Its important to understand that Oracle Gateway will go through unixODBC and do not directly interface with HDB odbc driver.

      I then reconfigured and got into another error. That was 32bit unixODBC vs. 64bit. Then I pointed LD_LIBRARY_PATH to /usr/lib64

      If your isql (as per step 9 above) works correctly (without data truncation) then HDB odbc drivers are fine.

      See in step 6 above, the HS_FDS_SHAREABLE_NAME is pointing to 64bit unixODBC library /usr/lib64/libodbc.so

      Let me know if you need more clarifications.

      Kind Regards,

      Muniraju

      (0) 
  4. Camil Panaitescu

    Hello Muniraju, We  had to recycle our Linux host and that solved our issue. Therefore, think that the final problem we encountered was a memory / hanging process matter. The main two config changes we’ve performed, before running into the memory problem, which fixed our initial “select *” and “select *” with “where clause” issue, were: (1) In the Oracle Gateway profile init.ora, the parameter “HS_FDS_SHAREABLE_NAME”  instead of pointing to the HANA ODBC Driver, pointing to the UNIX ODBC Driver Manager, thus instead of “/usr/sap/hdbclient/libodbcHDB.so” it should point to “/usr/lib64/libodbc.so”. (2) Also, the parameter “HS_LANGUAGE” in the same profile, should have the NLS Character Set of the target database, thus HANA, not Oracle (which in our case is not UNICOE): “HS_LANGUAGE=AMERICAN_AMERICA.UTF8” (note: “HS_NLS_NCHAR=UCS2”). We thank you so much for sharing the config procedure. Kind regards, Camil Panaitescu

    (0) 
  5. manuel perez

    Hi guys, as you know, is not easy to find information like this, so thanks for share it!

    In my case, I’m trying to consume a HANA procedure from Oracle database.

    DBLINKS are working fine when I query tables in the HANA side, but calling a procedure looks like a different challenge…

    Any idea or suggestion?

    (0) 
  6. Consultoria Deloitte

    Hi there
    We’re experiencing same problem, but our Oracle DB is installed on a Windows Server, so no Unix ODBC driver. Should we set the HANA ODBC driver location on HS_FDS_SHAREABLE_NAME param instead?
    Furthermore, is it a must to set the HS_LANGUAGE param? The HANA DB installation have been performed by someone else, how can we check the installation language?
    Thanks in advance.

    (0) 

Leave a Reply