Installing & Configuring Simba Redshift ODBC driver with Linux based BOBJ server

Introduction

As a Basis administrator came across a scenario where WEBI reports were created using Amazon redshift ODBC connection. The connection failed with the error message “Invalid RDBMS type”

Root cause was identified as the drivers were not installed properly. I observed that there was no specific documentation or information available to configure it. I am writing the short blog on the configuration which my help the Basis Community to get fair idea on what needs to be done if Simba ODBC drivers need to be used for WEBI reports.

Below steps described installing and configuring Simba Redshift ODBC driver on SBOP 4.2 SP3 PL5 which was updated from 4.1 with UnixODBC driver manager on RHEL 6.x.

Pre Checks

• Disk space: /opt should have at least 100 MB free space ( if UnixODBC not installed) , /usr/sap/<SID> have at least 200MB free space
• Operating System administrator authorization on each BOBJ servers, which have Connection server.
• You have access as “root” or authorization to execute OS administration tasks
• You have access as “<sid>adm”
• Access to CMC as administrator.
• You have IDT installed and working, connected to repository.
• You have ability to execute WEBI reports to check after configuration if this working or not.
• Please check if server can connect to Internet and download.
• You have AWS Redshift cluster details which includes server name, database name, username and password
• Authorization to execute ./ modifyOrRemoveProducts.sh from terminal.

Installation of SIMBA ODBC using BOBJ installer

1. Login to server with <sid>adm and change directory /usr/sap/<SID>
2. Execute ./ modifyOrRemoveProducts.sh
3. Choose the Base installation and hit enter to go to next screen

• Please note:(i) You can’t modify or add remove on update installation, only base installation will allow to perform modify, in below screen selection 8 indicate it is base as it is don’t have any “update” .(ii) The next screen may takes longer time depend on server available resources.Select “Modify” and hit Enter to continue

English will be auto selected, no changes, hit enter to continue

Hit space bar and select Database Access and again hit Space bar

Using down arrow, reach to “Amazon Redshift” and hit the X button to select as per below

If CMS is running on different server(cluster environment) you may prompted for credential

 All set, hit enter

It will take few min to complete the installation

Once installation done, modify the below:

1. Add the below path on User environment variable LD_LIBRARY_PATH, where <SID> need to change as appropriate /usr/sap/<SID>/sap_bobj/enterprise_xi40/linux_x64/odbc/simbaamazon/1.0/lib

Note: based on you base version installer installed version 1.0, it may different if the base version changed.

2. Exit from <sid>adm, it will activate the new LD_LIBRARY_PATH variable, now restart BOBJ server (./stopservers & ./startservers)

# Use IDT to check if drivers are available on selection

# Check the ConnectionServer Metrics, driver status should loaded in status

If status shows failed, check SAP Note 2578692 and proceed with UnixODBC installation.

Configuration

A .Configuring SIMBA ODBC

1. Login to terminal using <sid>adm and change directory to /usr/sap/<SID>/sap_bobj/enterprise_xi40/simbaconf
2. Copy file “simba.amazonodbc.ini.sample” to /usr/sap/<SID>/sap_bobj/enterprise_xi40/linux_x64/odbc/simbaamazon/1.0/lib

$ cp /usr/sap/<SID>/sap_bobj/enterprise_xi40/simbaconf/simba.amazonodbc.ini.sample /usr/sap/<SID>/sap_bobj/enterprise_xi40/linux_x64/odbc/simbaamazon/1.0/lib/

3. Rename the file simba.amazonodbc.ini.sample to sap.redshiftodbc.ini

$ mv /usr/sap/<SID>/sap_bobj/enterprise_xi40/linux_x64/odbc/simbaamazon/1.0/lib /sap.redshiftodbc.ini  /usr/sap/<SID>/sap_bobj/enterprise_xi40/linux_x64/odbc/simbaamazon/1.0/lib /sap.redshiftodbc.ini

4. Open the file sap.redshiftodbc.ini on vi editor and modify the below parameter as per belowDriverManagerEncoding=UTF-16ErrorMessagesPath=/usr/sap/<SID>/sap_bobj/enterprise_xi40/linux_x64/odbc/simbaamazon/1.0/help/ErrorMessagesODBCInstLib=/usr/lib64/libodbcinst.soB. Configuring ODBC INI
   1. Check the odbc.ini path using below command ( this should be declared on user environment of <sid>adm, if not please declare)

   $echo $ODBCINI

   Edit the odbc.ini and add below lines to create Redshift DSN

[DSN NAME]

Driver=/usr/sap/<SID>/sap_bobj/enterprise_xi40/linux_x64/odbc/simbaamazon/1.0/lib/libsimbaredshiftodbc64.so

Description=Amazon Redshift ODBC

Host= <your AWS redshift cluster server address>

Port=5439

Database=<AWS DB name>

locale=en-US

Username= <AWS user>

Password=<AWS password>

For more details on how to obtain the AWS information, visit the below link

https://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-connection.html#obtain-odbc-url

All done !

Do $isql [DSN] username and Password

If everything good, you should get sql prompt.

Conclusion

At the time of preparing this, have understanding SAP always suggest to use SBOP installer provided Redshift driver due to compatibility and license complexity.

Also don’t forget to check help.sap.com/SAP Note 2578692 , 2441086, 2286522, for latest updates and guides.