The LIBNAME Statement for Relational Databases |
Specifies
whether SAS creates a utility spool file during read
transactions that read data more than once.
Default value: |
YES
|
Valid in: |
SAS/ACCESS LIBNAME
statement
|
DBMS support: |
Aster nCluster, DB2 under
UNIX and PC Hosts, DB2 under z/OS,
Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC,
OLE DB, Oracle, Sybase, Sybase IQ, Teradata
|
-
YES
-
specifies that SAS creates a utility spool
file into which it writes the rows that are read the first time. For subsequent
passes through the data, the rows are read from the utility spool file rather
than being re-read from the DBMS table. This guarantees that the row set
is the same for every pass through the data.
-
NO
-
specifies that the required rows for all
passes of the data are read from the DBMS table. No spool file is written.
There is no guarantee that the row set is the same for each pass through the
data.
-
DBMS
-
is valid for Oracle only. The required rows
for all passes of the data are read from the DBMS table but additional enforcements
are made on the DBMS server side to ensure that the row set is the same for
every pass through the data. This setting causes SAS/ACCESS Interface
to Oracle to satisfy the two-pass requirement by starting a read-only transaction.
SPOOL=YES and SPOOL=DBMS have comparable performance results for Oracle. However,
SPOOL=DBMS does not use any disk space. When SPOOL is set to DBMS, you must
set CONNECTION=UNIQUE or an error occurs.
In some cases, SAS processes data in more
than one pass through the same set of rows. Spooling is the process of writing
rows that have been retrieved during the first pass of a data read to a spool
file. In the second pass, rows can be reread without performing I/O to
the DBMS a second time. When data must be read more than once, spooling improves
performance. Spooling also guarantees that the data remains the same between
passes, as most SAS/ACCESS interfaces
do not support member-level locking.
MySQL: Do not use SPOOL=NO with the MySQL interface.
Teradata: SPOOL=NO requires
SAS/ACCESS to
issue identical SELECT statements to Teradata twice. Additionally, because
the Teradata table can be modified between passes, SPOOL=NO can cause data
integrity problems. Use SPOOL=NO with
discretion.
CONNECTION= LIBNAME Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.