SAS/ACCESS^® Interface to Vertica for SAS^®9 requires that customers source their own ODBC driver.   

The Vertica ODBC client versions 9.2 and later drivers no longer appear to support SQL_ATTR_MAX_ROWS as a way of limiting how many rows are returned to the client application over the network. In SAS terms, all rows are returned to SAS, and only then is OBS= used to limit the result set.

For example, you can check the behavior when using OBS=10 by completing steps similar to the following: 

1. Enable SASTRACE. 

2. Run a query that uses OBS=10. The SAS log shows that the ODBC SQL_ATTR_MAX_ROWS attribute is correctly set by SAS.

The Real Time for the step will be much higher than the CPU Time. In addition, on UNIX based operating systems, if you also enable OPTIONS FULLSTIMER, unexpectedly high Voluntary Context Switches will occur. 

Workaround 

The only workaround for this issue is to source a version 9.1 driver from Vertica.  

Note: This older driver might not be supported with the later version of the Vertica Server that you are connecting to. 

Click the Hot Fix tab in this note to access the hot fix for this issue.

After you install the hot fix, SAS will circumvent issue by pushing a "LIMIT n" to Vertica when OBS= is used. You can use SASTRACE to confirm that the limit was pushed down. 

The following is displayed in the SAS log for OBS=10: 

Note that SASTRACE should be used for debugging only as directed by SAS Technical Support.  To turn off SASTRACE use the following statement:  

Operating System and Release Information

A fix for this issue for SAS/ACCESS Interface to Vertica 9.4_M8 is available at:

A fix for this issue for SAS/ACCESS Interface to Vertica 9.4_M7 is available at: