Facing issue while Configuring SAS/Access interface to Redshift

[karthic2612]

Hi All,

I have licensed SAS/Access interface to Amazon redshift. I have installed in our environment.

Allso I have configured Teradata using odbc drivers. and for that I have installed unixODBC driver manager. I am able to connect Successfully

My doubt is, where i need to update the Data source name and connection details for Redshift. Is it in common odbc.ini file where I used for Teradata connection details or do i need to update in SAS Access Redshift driver install path. for ex /sas/sashome/Accessclients/9.4/Redshift under that I have odbc.ini.

I have updated all the connection details of Teradata and Redshift in single odbc.ini file and configured.

Tried to execute Libname statement for those connection entries. For teradata it is successfull. but for Redshift I am receiving Libname statement error. Please see the libname statement error below

libname redshift sasiorst server="redshiftserver.us-east-1.redshift.aws.com" DB=database name user=username password=pass;

ERROR: CLI error trying to establish connection: 523 630
ERROR: Error in the LIBNAME statement.

I think above error indicates that I am missing to set environment variable. I couldnt figure out the root cause of the issue.

[sas@ip-10-61-100-9 Redshift]$ echo $LD_LIBRARY_PATH
:/usr/lib64/libodbcinst.so:/app/sashome/AccessClients/9.4/Redshift/lib/:/app/sashome/AccessClients/9.4/Redshift/lib/S0rsft27.so:/app/sashome/AccessClients/9.4/Redshift/:/app/sashome/AccessClients/9.4/Redshift/lib:/usr/lib64
[sas@ip-10-61-100-9 Redshift]$

Someone please help me to figure out the issue. 

[SimonDawson]

When you use SAS/ACCESS Interface to Redshift in combination with SAS/ACCESS Interface to ODBC you'll need to setup your LD_LIBRARY_PATH to load libodbc.so from inside the Redshift installation directory.

In your error you only have error numbers that arn't resolved to error strings. This typically means your ODBC variables in both the environment and the odbc*.ini are not setup correctly. Please review them.

[karthic2612]

Hi Simon,

As per your advice, I have setup the LD_LIBRARY_PATH to load libodbc.so from inside the Redshift installation directory.

:/usr/lib64/libodbcinst.so:/app/sashome/AccessClients/9.4/Redshift/lib/:/app/sashome/AccessClients/9.4/Redshift/lib/S0rsft27.so:/app/sashome/AccessClients/9.4/Redshift/:/app/sashome/AccessClients/9.4/Redshift/lib:/usr/lib64:/app/sashome/AccessClients/9.4/Redshift/lib/libodbc.so

Default path for the drivers (odbcinst and odbc.ini)

[sas@ip-xx-xx-xx-xx odbc_shared]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /app/SAS_Software_Depot/odbc_shared/odbcinst.ini
SYSTEM DATA SOURCES: /app/SAS_Software_Depot/odbc_shared/odbc.ini
FILE DATA SOURCES..: /app/SAS_Software_Depot/odbc_shared/ODBCDataSources
USER DATA SOURCES..: /app/SAS_Software_Depot/odbc_shared/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

I am confused by using both SAS Access interface to Amazon redshift and ODBC engine. Could you please give me a clarity to use SAS/Access interface to Amazon redshift for Redshift data source connections. Do i need to use 2 separate odbc.ini file for ODBC engine and Redshift interface. Or we can use it as single odbc.ini and feed all the connection details in single odbc.ini for both odbc and Redshift native interface.

Thanks,

Vishal

[SimonDawson · Accepted solution]

You only have a single odbc.ini. The driver manager that gets used is the one shipped with SAS/ACCESS Interface to Redshift. There is a special interaction between SAS and the driver manager that allows the driver manager to load other drivers.

This is all explained in the last paragraph of the SAS/ACCESS Interface to Redshift section of the Configuration Guide for SAS 9.4 Foundation for UNIX Environments.

I'd not use odbcinst, or any of the other tools shipped with the unixODBC driver manager in this case because the driver manager shipped within SAS/ACCESS Interface to Redshift is not based on unixODBC.