topic Re: Facing issue while Configuring SAS/Access interface to Redshift in Administration and Deployment

Facing issue while Configuring SAS/Access interface to Redshift

karthic2612 — Mon, 20 Aug 2018 07:17:54 GMT

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.

Re: Facing issue while Configuring SAS/Access interface to Redshift

SimonDawson — Mon, 20 Aug 2018 10:04:49 GMT

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.

Re: Facing issue while Configuring SAS/Access interface to Redshift

karthic2612 — Tue, 21 Aug 2018 09:09:24 GMT

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

Re: Facing issue while Configuring SAS/Access interface to Redshift

SimonDawson — Tue, 21 Aug 2018 10:42:07 GMT

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.