Configuring a HANA ODBC Connection for Smart Data Streaming in SPS09
|NOTE: These instructions apply to HANA Smart Data Streaming SPS09. With HANA Smart Data Streaming SPS10, an ODBC driver manager is installed as part of the software installation and does not need to be configured separately. You may still need to configure network proxy settings in the STREAMING.sh file as mentioned in step 2 below.|
A key post install step for configuring the Smart Data Streaming component as part of your HANA system is to configure an ODBC connection for Smart Data Streaming projects to use when connecting to the HANA database. The ODBC connection will be used by:
- HANA Reference Tables for event driven look ups against table data residing in the HANA database
- HANA Output Adapter for publishing data to HANA database tables
- Generic DB Input Adapter for executing queries or stored procedures in HANA to provide input to a Streaming project
This ODBC connection is made from the Streaming node to the HANA master node, which means that the ODBC configuration takes place on the Streaming node using the HANA ODBC driver. Since Smart Data Streaming runs on Linux platforms, you will likely also need to install an ODBC Driver Manager as is not a standard component for most Linux systems. Instructions on installing an ODBC Driver Manager and configuring and ODBC data source for your HANA database are provided in the following sections of the documentation:
Set up an ODBC Driver Manager
Set up an ODBC Data Source for the SAP HANA Database
The documentation does a good job of describing the process so the purpose of this document is to simply provide some more visibility and a little hands on insight.
Configuration Steps on the Streaming Node:
The basics steps required to configure the ODBC Driver Manager and ODBC connection from the Streaming node are:
- Install an ODBC Driver Manager that must be unixODBC 2.3.1 or higher
- Update the STREAMING.sh file
- Update LD_LIBRARY_PATH to include the ODBC Driver Manager
- May need to specify "no_proxy"
- Create the .odbc.ini file and data source definition
- Restart the Streaming host so that it can see the above configuration changes
- Add an ODBC Service from HANA Studio
1. Install an ODBC Driver Manager
The recommended approach, as per the documentation, is to download and compile the unixODBC 2.3.2 source from:
The download page at unixODBC.org includes instructions for compiling the source. While Linux systems don't come with a standard ODBC Driver Manager, they do come with a standard compiler and on my SLES 11.3 (SP3) systems. the unixODBC 2.3.2 built cleanly just by cutting and pasting the build commands into my SSH session.
Note that it doesn't matter what directory you download and unzip the unixODBC 2.3.2 source into. The "make install" is set up to always output the compiled libodbc.so.2.0.0 to the /usr/local/lib destination directory.
Now the Streaming server is specifically looking for an ODBC Driver Manager with the file name "libodbc.so.1". Rather than renaming the libodbc.so.2.0.0 file that is generated when building the driver manager, you can just create a symbolic link to assign "libodbc.so.1" as an alias by navigating to the /usr/local/lib directory and running:
ln -s libodbc.so.2.0.0 libodbc.so.1
2. Update STREAMING.sh
The STREAMING.sh shell script sets the environment variables used by the Streaming server. This file will be located in the /hana/shared/<SID>/streaming directory where <SID> is the System ID that you specified when installing your HANA system. You can edit this file as the Linux root user and will need to add the path to the ODBC Driver Manager library.
Depending on the network proxy settings on the Linux host, you may need to specify that connections to the HANA master node network domain are an exception that should not be proxied. This can be done by adding the domain to the $no_proxy list:
where <sub.domain> is the sub domain that you want to bypass the proxy settings. For example, if the fully qualified domain name of the HANA master node is 'hanaserver.region.mycompany.com', then it may be appropriate to have a no_proxy setting for the 'mycompany.com' subdomain.
Network routing including proxy settings can be complicated so you may need to work with your Network Administrator to determine the appropriate settings for your environment.
Note: Upgrading your HANA system, at least between revisions of SPS09 (eg: Rev90 to Rev 93) will overwrite the STREAMING.sh file. This means that a required upgrade step is to review the STREAMING.sh file after running the upgrade to verify the above edits and restore them if necessary.
3. Create .odbc.ini file and data source definition
ODBC connections rely on a data source definition that provides connection details for a specific database and server. The connection details make up an ODBC data source and are normally specified in an .odbc.ini file on Linux systems. The ODBC Driver Manager will look for .odbc.ini files in several locations, however the recommended location when working with a Smart Data Streaming server is:
Again, remember to replace <SID> with the 3 character System ID of your HANA system. Also note that this directory path is on the Streaming node. You do not need to create a .odbc.ini file on the HANA master node.
Within the .odbc.ini file you will create a data source definition that will look like this:
The pieces of the ODBC data source definition are:
- The data source name in square brackets - [ ...]
- The path and file name of the HANA ODBC driver. This driver is installed as part of the HANA database client installation and since it is under the HANA shared storage directory path it is already accessible from the Streaming node and does not require a separate installation process. Again, remember to replace <SID> with your HANA System ID.
- The address of the HANA database server you will be connecting to. You will need to specify the fully qualified domain name ("FQDN") of the HANA node to connect to. Make sure that you are connecting to the HANA master node and not trying to connect to the Streaming node.The port number uses the HANA Instance # to uniquely connect to a specific HANA instance. For example if your HANA Instance # is "00" then you would specify port # 30015. (Note that the ODBC data service connection uses a different port than used when defining the Streaming server connection in the SAP HANA Streaming Run-Test Perspective. The Streaming server connection in the Run-Test perspective uses port 3XX26 as opposed to port 3XX15 for the ODBC connection.)
4. Restart the Streaming Host
Now that you have made the required configuration changes and updates, it is time to restart your Streaming host. To do this you will need to log on to the Streaming node as the Linux <sid>adm user where <sid> is the lower case version of the HANA System ID. For example if the HANA System ID is "HA0", then <sid> will be "ha0".
Once logged in, you can stop and restart the Streaming node by running:
from the <sid>adm users home directory.
Note that since you have restarted the Streaming node, you will need to reconnect to the Streaming server in the SAP HANA Streaming Run-Test perspective in HANA Studio.
If you do not reconnect to the Streaming server, then you will get an error later on when you try to Discover the schema of the ODBC Data Service that you will be creating in the next step.
5. Add ODBC Service from HANA Studio
You are now ready open up HANA Studio and define the ODBC data service for use in your Smart Data Streaming projects. This HANA Academy video will walk you through that process in detail.