The LIBNAME Statement for Relational Databases SPOOL= LIBNAME Option 125
SPOOL= LIBNAME Option
Specifies whether SAS creates a utility spool file during read transactions that read data more
than once
Valid in: the SAS/ACCESS LIBNAME statement
DBMS support:
DB2 OS/390, DB2 UNIX/PC, Informix, ODBC, OLE DB, Oracle,
Microsoft SQL Server, SYBASE, Teradata
Default value:
YES
Syntax
SPOOL= YES | NO | DBMS
Syntax Description
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 the row set is the same for every pass through the data. This setting causes
the 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, the CONNECTION option must be set to UNIQUE. If
not, an error occurs.
Details
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.
Teradata Details: 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.
