How to create a new HANA ODBC DSN entry for SAP Data Services on Unix Platforms
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:
- On the SDS job server machine, make sure Hana db client is installed and its version matches that of the Hana server.
- 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:
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
- cd $LINK_DIR/bin
Run ./DSConnectionManager.sh and select “1” to configure Data Sources. All existing DSN entries should also be displayed:
2. Add a new database source and select SAP HANA:
3. Enter the DSN name, Hana server details and logon values:
4. After a successful test new HANA DSN entry should be displayed:
5. Run: view $ODBCINI to confirm the new “[HANA DSN 1]” entry in the odbc.ini:
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:
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:
3. In SDS Designer create a new Data Store:
That’s it! Please let me know if you have any questions