Threaded Reads Summary of Threaded Reads 47
utilize DBSLICE= to augment autopartitioning. For particulars on autopartitioning, see
the documentation for your DBMS:
Oracle
DB2 UNIX/PC
ODBC
SYBASE
Teradata
Data Ordering in SAS/ACCESS
The order in which table rows are delivered to SAS varies each time a step is rerun
with threaded reads. Most DBMS editions, especially increasingly popular parallel
editions, do not guarantee consistent ordering. If you require consistent ordering, use a
SAS BY statement. The BY statement generates ORDER BY as part of your SQL, and
also disables threaded reads. Thus your rows will always be consistently ordered.
Two Pass Processing for SAS Threaded Applications
Two Pass Processing occurs when a SAS threaded application requests that data be
made available for multiple pass reading (that is, more than one pass through the
dataset). In the context of DBMS engines, this requires that as the data is read from
the database, temporary spool files are written containing the read data. There is one
temporary spool file per thread, and each spool file will contain all the data read on that
thread. If three threads are specified for threaded reads, then three temporary spool
files are written.
As the application requests subsequent passes of the data, the data is read from the
temporary spool files, not re-read from the database. The temporary spool files can be
written on different disks, reducing any disk read contention, and enhancing
performance. To accomplish this, the SAS option UTILLOC= is used to define different
disk devices and directory paths when creating temporary spool files. There are several
different ways to specify this option:
In the SAS config file, add the line:
--utilloc("C:\path" "D:\path" "E:\path")
Specify the UTILLOC= option on the SAS command line:
on Windows:
sas --utilloc(c:\path d:\path e:\path)
on UNIX:
sas --utilloc ’(\path \path2 \path3)’
For more information about the UTILLOC= SAS option see the SAS Language
Reference: Dictionary.
Summary of Threaded Reads
For large reads of table data, Version 9 threaded reads can increase performance.
They can be particularly useful when you understand the autopartitioning technique
specific to your DBMS and use DBSLICE= to manually partition only when
appropriate. Look for enhancements in future SAS releases.
