Skip to Content

This blog describes how to use SAP Data Services 4.1/4.2 Connection Manager on UNIX to create  a new  ODBC DSN data source  for Hana db.

The following configuration steps have been performed on:

  • SAP Data Servers (SDS) 4.2 SP1
  • SUSE Linux Enterprise Server 11 (x86_64) VERSION = 11 PATCHLEVEL = 2
  • HANA DB 1.0 SP7
  • Windows Server 2008 R2 as Client

A: Checks to perform before adding the new DSN entry:

  1. On the SDS job server machine, make sure Hana db client is installed and its version matches that of the Hana server.
  2. Test the database connectivity using the native hana db client driver:
    • cd to the Hana client install directory ie: cd /usr/sap/hdbclient
    • Execute:  ./hdbsql -n hana_servername:port -u username -p password
    • Run a simple command “ \s” for status check, and if no error produced  exit and   proceed.
    • Good test results should look like the figure below:

Capture1.PNG

   3. Set the SDS environment variables:

$ cd $LINK_DIR/bin       (<LINK_DIR> is the SDS installation directory).
$ . ./al_env.sh                (there is a space between the dots)

   4. Verify the location of the odbc.ini file where the DSN entry will be added. The $ODBCINI would have been set by the above script ./al_env.sh

echo $ODBCINI  (The output should be:  <$LINK_DIR>/DataDirect/odbc/odbc.ini)

B: Add a new DSN Entry

  1. cd $LINK_DIR/bin
    Run ./DSConnectionManager.sh and select “1” to configure Data Sources. All existing DSN entries should also be displayed:

/wp-content/uploads/2014/03/capture2_407073.png

    2.  Add a new database source and select SAP HANA:

Capture3.png

  3. Enter the DSN name,  Hana server details  and logon values:

Capture4.png

  4.  After a successful test new HANA DSN entry should be displayed:

Capture5.PNG

  5. Run: view $ODBCINI to confirm the new “[HANA DSN 1]” entry in the odbc.ini:

Capture6.png

  6.  Test the odbc connectivity from the same server by issuing this command from hanadb client path :

./odbcreg DSNNAME  username password

For example: ./odbcreg ‘HANA DSN 1’ username  password

Successful test result should look like this:

Capture7.PNG

C: Add DSN and SDS Data Store on Windows client

   1. On the Windows SDS designer client machine, install Hana db client,  with the same version as the server.

   2. Create a new DSN source, using ODBC Data Source Administrator:

capture9.PNG

capture8.PNG

capture.10PNG.png

  3. In SDS Designer create a new Data Store:

/wp-content/uploads/2014/03/capture_11_407092.png

That’s it! Please let me know if you have any questions

Nawfal Tazi

To report this post you need to login first.

8 Comments

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

  1. Sylvain Chevallereau

    Dear Nawfal,

    Thank you this is a good step by step guide. I just wanted to comment the fact that SAP Data Services supports DSN-less and TSN-less conectivity for Hana and a couple of other DB as repository, data source and data target.

    Since version 4.1 this may represent a big advantage especially for big deployment with lots of designers… or do you see any pros and cons that may prefer a DSN instead a DSN-less on HANA.

    Thank you again and kind regards,

    Sylvain

    (0) 
    1. Nawfal Tazi Post author

      Hi Sylvian,

      That’s right DNS-less/TNS-less connection is another (the common) way of connecting to Hana. I don’t see any significant advantages, but there could be some specific requirements by which a developer may want to use one connection as opposed to the other..

      Regards

      (0) 
  2. Erik Iversen

    This is a very good guide, but I have a question. Hope you can answer.

    After this setup, the ./odbcreg test went fine on Linux, but when running a job from the Dataservice, it complains about a missing entry in $LINK_DIR/bin/ds_odbc.ini

    I added the DSN name and where the HANA driver can be found like:

    [HANA HZ2]

    Driver=/usr/sap/hdbclient/libodbcHDB.so

    I have done similar for the MSSQL ODBC connections I created. After that HANA entry the job looks like running connecting to HANA. The job in dataservice is not failing anymore. The problem is just that the job does not find any data in a HANA database table.

    If I create a DSN on Windows like in your chapter C, using same username password, then the designer find data in HANA.

    What could be possible wrong?

    (0) 
  3. Narasimha Murthy

    Hi All,

     

    We have BODS XI 3.2 SP3. Can we create New Datastore with ODBC for HCP on this version as Target?

    Thanks in Advance for your replies.

     

    Regards,

    K.Narasimha Murthy

    (0) 

Leave a Reply