When you use the SAS/ACCESS Interface to Greenplum option READBUFF= with any value over 10,000 in conjunction with option DBSLICEPARM=ALL, data might be lost during a Read operation. However, no error is shown.

If you set a value greater than 10,000 for READBUFF=, you must also set a driver option called "MaxRowsetSize" to match the value of READBUFF=. If you do not set this driver option when using values greater than 10,000, and you specify DBSLICEPARM= in the LIBNAME statement, the problem occurs. An inaccurate count of rows occurs in the final result. You can see the issue by tracing.

Code:

option sastrace=",,t,d" sastraceloc=saslog nostsuffix;

libname mygp greenplm server="server-name" database=db port=5432
                      user=user-ID password="password" schema=public
                      readbuff=32000 dbsliceparm=ALL;

data work.test1;
set mygp.bigdata;
run;

Log:

GREENPLUM:  Thread 3 contains 205 obs.
GREENPLUM:  Thread 2 contains 2041240 obs.
GREENPLUM:  Thread 1 contains 2040162 obs.
GREENPLUM:  Threaded read enabled. Number of threads created: 3
NOTE: There were 4081607 observations read from the data set
MYGP.BIGDATA.
NOTE: The data set WORK.TEST1 has 4081607 observations and 12 variables.

In the example above, the resulting data set should contain more than 6 million rows, but you can observe that this data set contains only a little more than 4 million. Tracing shows that "Thread 3" read only 205 observations.

You can circumvent this issue as follows:

• By not using DBSLICEPARM=
• By specifying a value less than 10,000 for READBUFF=
• Or by setting the driver option MaxRowsetsize to be equal to the READBUFF value

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

Applying the hot fix or upgrading your SAS software enables you to specify DBSLICEPARM= and READBUFF= values greater than 10,000. Setting READBUFF= in the LIBNAME statement automatically sets MaxRowsetSize to the same value as READBUFF= in the code passed on the connection. Setting READBUFF= to a value greater than 10,000 as a data set option automatically truncates the value to 10,000 and sets MaxRowsetSize to the same.

For more information about MaxRowsetSize, see SAS Note 47859: "READBUFF values that are set to more than 10,000 are truncated to 10,000 by DataDirect drivers."

Operating System and Release Information

A fix for this issue for SAS/ACCESS Interface to Greenplum 9.31_M1 is available at: