When you specify a value larger than 10,000 for the LIBNAME option READBUFF=, the value is truncated to 10,000 by DataDirect drivers.

Both SAS/ACCESS^® Interface to Greenplum and SAS/ACCESS^® Interface to Microsoft SQL ship DataDirect drivers as a part of the product.

This issue can be seen in the log file with code similar to the following:

options sastrace='d,,t,sa' sastraceloc=saslog nostsuffix;
options msglevel=i;

libname mygp greenplm dsn=gplum user=gpuser password=gppass schema=public readbuff=15000;

data work.mytest;
set mygp.lg_base_gp;
run;

The resulting log file has several reads of 10,000 instead of 15,000:

GREENPL: Fetch time in seconds for 10000 rows is 0.148824
GREENPL: EXIT SQLFetchScroll with return code 0 (SQL_SUCCESS)

Workaround

If you determine that you need a value greater than 10,000, you can override the limit by setting an odbc.ini variable called MaxRowsetSize to the value that you require. MaxRowsetSize can be set in the odbc.ini or passed by the LIBNAME statement. When you are setting MaxRowsetSize, be sure you set READBUFF to the same value.

Set this value in the odbc.ini by editing the odbc.ini and adding MaxRowsetSize under the data source name for which you want to have an increased value.

ODBC.INI entry:

[gplum]
Driver=/tmp/grnplm_drivers/lib/S0gplm24.so
Description=SAS ACCESS to Greenplum
...
MaxRowsetSize=15000

SAS Code:

libname mygp greenplm dsn=gplum user=gpuser password=gppass readbuff=15000 schema=public;

It is also possible to pass this option in the LIBNAME statement or in the explicit pass-through code if you do not wish to set it in the odbc.ini file.

libname mygp greenplm dsn=gplum user=gpuser password=gppass schema=public readbuff=15000 conopts="MaxRowsetSize=15000;" ;

proc sql;
connect to greenplm(dsn=gplum user=gpuser password=gppass readbuff=15000 conopts="MaxRowsetSize=15000;");
create table work.mytest1 as select * from connection to greenplm
(select * from public.lg_base_gp);
disconnect from greenplm;
quit;

Note: Setting this value higher does not always result in improved performance. The setting for this value is subject to the same restrictions as for READBUFF. It is heavily dependent on the size of the data and the amount of memory available to SAS.

Operating System and Release Information