BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BobD
Fluorite | Level 6

odDoes anyone have any advice or infomation about a new product in SAS 9.4 called "SAS/Access to Vertica"?  In particular, what are the advantages to using this new product when compared to plain old "SAS/Access to ODBC"?  I'm sure there must be some differences but I cannot find anything in the new documentation.  The :'system requirements" say that a vendor-supplied ODBC driver is required for this product.

We have a new requirement to begin accesses a "Vertica" database and need to understand which SAS/Access product to license.  Since there are other databases out there that we may need to access in the future using ODBC, I'm inclined to only license the Access to ODBC product.  But if there is something special about this SAS product, I'm eager to understand.

Thanks in advance,

Bob

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

Hi Bob,

I am the SAS Product Manager for the SAS/ACCESS family of products.

Here are the primary differences between SAS/ACCESS Interface to ODBC and SAS/ACCESS Interface to Vertica. The SAS/ACCESS interface to Vertica supports:

Extended Data Type Integration – The SAS/ACCESS Interface to ODBC does not support Vertica specific data types.

Extended Vertica Function Support – The SAS/ACCESS Interface to Vertica will pass down more functions to the Vertica server. The result is that more SQL will be seamlessly passed to the Vertica server. This may result in better query performance.

INSERT/UPDATE/DELETE statements – Our ACCESS developers went to great lengths to add support for INSERT, UPDATE, and DELETE statements. The Vertica ODBC driver does not support updateable cursors. Therefore, SAS/ACCESS to ODBC does not support UPDATE/DELETE when used with the Vertica ODBC driver.

I18N – The Vertica ODBC driver will support I18N. Unfortunately, the ACCESS to ODBC interface cannot make use of this property. If you need I18N support, then you need ACCESS to Vertica.

Best wishes,
Jeff

Slight correction - the Vertica ODBC driver supports INSERT statements. Message was edited by: Jeff Bailey

View solution in original post

23 REPLIES 23
JBailey
Barite | Level 11

Hi Bob,

I am the SAS Product Manager for the SAS/ACCESS family of products.

Here are the primary differences between SAS/ACCESS Interface to ODBC and SAS/ACCESS Interface to Vertica. The SAS/ACCESS interface to Vertica supports:

Extended Data Type Integration – The SAS/ACCESS Interface to ODBC does not support Vertica specific data types.

Extended Vertica Function Support – The SAS/ACCESS Interface to Vertica will pass down more functions to the Vertica server. The result is that more SQL will be seamlessly passed to the Vertica server. This may result in better query performance.

INSERT/UPDATE/DELETE statements – Our ACCESS developers went to great lengths to add support for INSERT, UPDATE, and DELETE statements. The Vertica ODBC driver does not support updateable cursors. Therefore, SAS/ACCESS to ODBC does not support UPDATE/DELETE when used with the Vertica ODBC driver.

I18N – The Vertica ODBC driver will support I18N. Unfortunately, the ACCESS to ODBC interface cannot make use of this property. If you need I18N support, then you need ACCESS to Vertica.

Best wishes,
Jeff

Slight correction - the Vertica ODBC driver supports INSERT statements. Message was edited by: Jeff Bailey

BobD
Fluorite | Level 6

Perfect response, exactly what I needed to know!

I'm especially grateful for the comment that the Vertica ODBC driver does not support INSERT, UPDATE, and DELETE.  That will probably explain away a Tech Support ticket I have regarding an error I'm getting trying to create a new table in Vertica from SAS.  I had no idea that that was not possible.

Thanks very much!

JBailey
Barite | Level 11

My pleasure!

mnietz
Calcite | Level 5

Hi,

I have a question somehow related to the above discussion - we are talking to a potential customer which is interested in SAS working with Vertica. He is currently runing SAS 9.2 (with no plans to upgrate it anyhow soon), and is curious if SAS Base 9.2 would work with SAS/ACCESS(R) 9.4 for Relational Databases (for Vertica) . So the main question is if SAS 9.2 would work with SAS/Access 9.4 for Vertica?

I'm getting 2 opposite opinions on that from SAS partners - Can you have an official statement clarifying this matter?

Best Regards,

Mikolaj

LinusH
Tourmaline | Level 20

I'm not a product manager, but I never heard of that SAS supports a mixture of versions in the same SAS installation, would be very surprised if would work at all. But, there are good chances that a 9.2 client could talk to a 9,4 server (e.g. using SAS/CONNECT) which in turn have the desired SAS/ACCESS product licensed and installed.

Data never sleeps
JBailey
Barite | Level 11

Hi,

Your intuition is absolutely correct. Mixing different versions of SAS/ACCESS engines in a SAS install is not a good idea.

Best wishes,

Jeff

JBailey
Barite | Level 11

Hi Mikolaj,

I am the product manager for the SAS/ACCESS family of products.

SAS/ACCESS Interface to Vertica is only available with SAS 9.4. With SAS 9.2 you can use the SAS/ACCESS Interface to ODBC.

Best wishes,

Jeff

mnietz
Calcite | Level 5

Hi Jeff,

Thanks for the clear statement on that. I have yet another question - pretty soon HP is releasing Vertica 7 - would the SAS/Access 9.4 for Vertica work with Vertica 7?

Regards,

Mikolaj

RohitV
Calcite | Level 5

Hello Jeff,

I am trying to connect to Vertica thru SAS, but getting below error, do you know what can be the reason for this?

 

ERROR: Could not load /XXXXX/sas94/SASFoundation/9.4/sasexe/sasvrt (100 images loaded)
ERROR: libverticaodbc.so: cannot open shared object file: No such file or directory
24         libname VZPRD vertica user="XXXXXXX" password=XXXXXXXXXXXXXXXX server="XXXXXXXXX" database="XXXXXX"
24       !  port=5XXX;
ERROR: The SAS/ACCESS Interface to VERTICA cannot be loaded. The SASVRT   code appendage could not be loaded.
ERROR: Error in the LIBNAME statement.

mklybor
Calcite | Level 5

Where can I find documentation about installing/conffiguring SAS/Access 9.4 for Vertica? In the general 9.4 install manual, SAS/Access section page 58, it only talks about Vertica ODBC. Could you direct me to documentation for the native vertica SAS/Access? I have a new 9.4 installation that we are just starting to use. SAS/Access for Vertica was supposed to be included and I an trying to validate that we have everything installed. Attempts to access Vertica content give me an error the looks suspiciously like it was trying to use ODBC, which is not set up.

DaveR_SAS
SAS Employee

Mklbor, here is the chapter for SAS/ACCESS to Vertica: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Third Edition

mklybor
Calcite | Level 5

thanks, but I'm looking for installation / configuration documentation, not usage. in the SAS 9.4 install manual, the section on SAS/Access for Vertica talks about installing ODBC drivers and the like. As I understand it, that is not what we have. We should have  "direct" access to Vertica, not ODBC (as was the case for 9.3 and earlier).

specifically, pg. 58 in Configuration Guide for SAS 9.4 Foundation for UNIX Environments says that for Vertica access, an ODBC driver manager etc. must be installed. It was my understanding that ODBC was no longer necessary since we now have native Vertrica SAS/Access. could you clarify whether Vertica access is still ODBC for 9.4? thanks.

JBailey
Barite | Level 11

SAS/ACCESS Interface to Vertica is ODBC based.

mklybor
Calcite | Level 5

in my 9.4 config manual, the vertica config steps consist of setting some shell variables to point to the .ini files. where should these be set? in the unix logon scripts for each of my users? in some SAS startup script? do any servers need to be restarted after setting these up? we have all of the odbc pieces installed and configured. thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 23 replies
  • 6338 views
  • 1 like
  • 8 in conversation