Hi Everyone,
A newbie here.
May i ask for your help on this. The setup is as follows.
Client Application (SAS) <-> ODBC Driver Manager <-> ODBC Driver <-> Datasource (Vertica)
Server A (Windows Platform)
1. Client Application
Server B (Unix Platform)
1. ODBC Driver Manager (unixODBC)
2. ODBC Driver (installed with Vertica DB)
3. DSN (Vertica)
Notes:
1. I have a working SAS base foundation 9.2 working at my local machine with working SAS/CONNECT for ODBC interface.
2. Vertica DB is setup at the unix platform end together with the driver.
3. unixODBC - still to be configured.
4. Plan to use LIBNAME statement and/or SQL Pass-through facility to access Vertica DB in SAS app.
Sample ODBC configuration files that would be configured in unix env:
ODBC.INI
Location: /opt/etc
[VerticaDSN]
Description = VerticaDSN ODBC driver
Driver = /opt/vertica/lib/libverticaodbc_unixodbc.so
Database = <databasename>
Servername = localhost
UserName = dbadmin
Password =
Port = 5433
ConnSettings =
SSLKeyFile = /home/dbadmin/client.key
SSLCertFile = /home/dbadmin/client.crt
Locale = en_US
ODBCINST.INI
Location: /opt/etc
[VerticaDSN]
Description = ClickStream example database
Driver = /opt/vertica/lib/libverticaodbc_unixodbc.so
[ODBC]
Threading = 1
Questions/Concerns:
1. With the assumption that the unix platform end is configured successfully, i.e. Vertica DB data is accessible through ODBC but locally, is there a way that SAS (in Server A) can discover the ODBC data source in Server B without any configuring needed on Server A side? Though i haven't tried the direct approach, say below, since i haven't configured unixODBC just yet.
libname vertica odbc dsn=verticaDSN user=dbadmin Password=;
2. Would there be any other setup in case the above configuration is not feasible?
Any help is appreciated. Thank you!
My belief is that you need to specify, or configure, a data source entry in the local host ODBC manager. I'm not aware if there are any other setups available.
I think that OLEDB might be more flexible...?
Hi LinusH,
Thank you for your reply.
Yes, I should agree with you with the configuration on the local host ODBC manager (unixODBC for unix platform). However, the question that still lingers in my mind is that how will the SAS application from windows platform be able to 'talk' or 'find' the ODBC configured setup in the unix platform.
On the OLE DB, i'm not familiar as well on the configuration. As i've checked the SAS Help Facility, I saw from the featured host that unix is not supported by such configuration.
About finding the UNIX host in ODBC - that's the role for the data source definition (in the local Windows host). So SAS itself has no idea where data is coming from.
About support on different platforms, the SAS/ACCESS support is usually based on what platform the client application is running, not where the external database is executing. You might want to talk to a SAS representative about different set-up possibilities.
For your first comment, would it mean that the setup can be ODBC(windows) to ODBC(unix) connection, somewhat like that? Or the setup should be Server A includes the SAS app, ODBC Manager and the driver?
For the second one, that somehow clarifies a bit about the SAS/ACCESS. I'll try to contact one as you recommended.
Well, you obviously need the driver for your database server.
Then you need to define a data source in the ODBC Manager.
And then you define a libref in SAS.
All this takes place on server A, and assuming that the necessary preparations already have been done on Server B.
An overview:
Thanks Linush.. Will look for the Vertica ODBC driver for windows and try that kind of setup.
Done with the setup and the connection works perfectly when assigned a LIBNAME statement.
However, I wasn't able to view the tables using the explorer in SAS 9.2. I used the libname statement:
libname vertica odbc dsn=VerticaDSN user=dbadmin;
and the note was:
1 libname vertica odbc dsn=VerticaDSN user=dbadmin;
NOTE: Libref VERTICA was successfully assigned as follows:
Engine: ODBC
Physical Name: VerticaDSN
Using SQL Pass-through facility, I was able to query the contents with this code:
proc sql;
connect to odbc (dsn=VerticaDSN=dbadmin);
create table test as select * from connection to odbc(ODBC::SQLTables);
disconnect from odbc;
quit ;
Would you have an idea on how I could make the tables visible in the Explorer? I tried adding SCHEMA= option, but it didn't work.
Thanks again!
Maybe this usage note applies to your situation?
http://support.sas.com/kb/2/479.html
I didn't resolve my issue.
However, using SQL Pass through facility, I was able to retrieve the tables from Vertica DB.
Maybe this would be enough for the current requriement.
Thanks for all your advise/help!
Hi,
I faced the similar issue with teradata database.I was able to connect to database successfully but unable to see the tables in SAS editor.
So I have changed the user name and pwd to admin user of teradata(DBC) and checked then it worked perfectly(I was able to see the tables)..this is because of the role and permissions issue with the user which I was using earlier.
So check with administrator user....
Thanks,
Shiva
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.
Find more tutorials on the SAS Users YouTube channel.