Threaded Reads Options That Affect Threaded Reads 43
is a SAS libref that “points” to DBMS data, and DbTable is a DBMS table. Here are
sample read-only librefs for which threaded reads can be turned on:
libname lib oracle user=scott password=tiger;
proc print data=lib.dbtable;
run;
data local;
set lib.families;
where gender="F";
run;
An eligible SAS step can require user assistance in order to actually perform
threaded reads. If SAS is unable to automatically generate a partitioning WHERE
clause or to otherwise perform threaded reads, then the user can code an option that
supplies partitioning. To determine if SAS can automatically generate a partitioning
WHERE clause, use “SASTRACE= System Option” on page 222 and “SASTRACELOC=
System Option” on page 224.
Threaded read can be turned off altogether. This reduces Version 9 performance to
that of previous SAS versions, but eliminates additional DBMS activity associated with
SAS threaded reads, such as additional DBMS connections and multiple SQL
statements.
Threaded reads are not supported for the Pass-Through Facility, in which you code
your own DBMS-specific SQL that is passed directly to the DBMS for processing.
Options That Affect Threaded Reads
SAS/ACCESS provides two options precisely for threaded reads from DBMSs:
“DBSLICE= Data Set Option” on page 186 and “DBSLICEPARM= Data Set Option” on
page 188.
DBSLICE= is a data set option, applicable only to a table reference. It permits you to
code your own WHERE clauses to partition table data across threads, and is useful
when you are familiar with your table data. For instance, if your DBMS table has a
CHAR(1) column Gender, and your clients are approximately half female, Gender
equally partitions the table into two parts. Therefore, an example DBSLICE= might be:
proc print data=lib.dbtable (dbslice=("gender=’f’" "gender=’m’"));
where dbcol>1000;
run;
SAS creates two threads and about half of the data is delivered in parallel on each
connection.
When applying DBSLICEPARM=ALL instead of DBSLICE=, SAS attempts to
"autopartition" the table for you. Threaded reads are automatically attempted only for
SAS threaded applications (fully threaded SAS procedures). DBSLICEPARM=ALL
extends threaded reads to more SAS procedures, specifically, steps with a read-only
libref. Or, DBSLICEPARM=NONE turns it off entirely. It can be specified as a data set
option, LIBNAME option, or as a global SAS option.
The first argument to DBSLICEPARM= is required and extends or restricts threaded
reads. The second, optional argument is not commonly used and limits the number of
DBMS connections. The following examples demonstrate the different uses of
DBSLICEPARM=:
Unix or Windows SAS invocation option that turns on threaded reads for all
read-only libref.
