The LIBNAME Statement for Relational Databases

READBUFF= LIBNAME Option

Specifies the number of rows of DBMS data to read into the buffer.

Alias:	ROWSET_SIZE= (DB2 under UNIX and PC Hosts, DB2 under z/OS, HP Neoview, Microsoft SQL Server, Netezza, ODBC, OLE DB, Sybase)
Default value:	DBMS-specific
Valid in:	SAS/ACCESS LIBNAME statement and some DBMS-specific connection options. See the DBMS-specific reference section for details.
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ

Syntax
Syntax Description
Details
See Also

Syntax

READBUFF=integer

Syntax Description

integer: is the positive number of rows to hold in memory. SAS allows the maximum number that the DBMS allows.

Details

This option improves performance by specifying a number of rows that can be held in memory for input into SAS. Buffering data reads can decrease network activities and increase performance. However, because SAS stores the rows in memory, higher values for READBUFF= use more memory. In addition, if too many rows are selected at once, rows that are returned to the SAS application might be out of date. For example, if someone else modifies the rows, you do not see the changes.

When READBUFF=1, only one row is retrieved at a time. The higher the value for READBUFF=, the more rows the DBMS engine retrieves in one fetch operation.

DB2 under UNIX and PC Hosts: If you do not specify this option, the buffer size is automatically calculated based on the row length of your data and the SQLExtendedFetch API call is used (this is the default).

DB2 under z/OS: For SAS 9.2 and above, the default is 1 and the maximum value is 32,767.

Microsoft SQL Server, ODBC: If you do not specify this option, the SQLFetch API call is used and no internal SAS buffering is performed (this is the default). When you set READBUFF=1 or greater, the SQLExtendedFetch API call is used.

HP Neoview, Netezza: The default is automatically calculated based on row length.

OLE DB: The default is 1.

Oracle: The default is 250.

Sybase: The default is 100.