Data Set Options for Relational Databases DBSLICEPARM= Data Set Option 189
Increasing the number of connections instead redistributes the same result set across
more connections.
There are diminishing returns when increasing the number of connections. With
each additional connection, more burden is placed on the DBMS, and a smaller
percentage of time is saved on the SAS step. Therefore, you should consult the
DBMS-specific documentation for threaded reads before using this parameter.
Details
DBSLICEPARM= can be used in numerous locations, and the usual rules of option
precedence apply. A table option has the highest precedence, then a LIBNAME option,
and so on. A SAS configuration file option has the lowest precedence because
DBSLICEPARM= in any of the other locations overrides that configuration setting.
DBSLICEPARM=ALL and DBSLICEPARM=THREADED_APPS make SAS steps
eligible for threaded reads. To see if threaded reads are actually generated, turn on
SAS tracing and run a step, as shown in the following example:
options sastrace=’’,,,d’’ sastraceloc=saslog nostsuffix;
proc print data=lib.dbtable(dbsliceparm=(ALL));
where dbcol>1000;
run;
If you want to directly control the threading behavior, use the DBSLICE= data set
option.
Examples
The following code demonstrates how to use DBSLICEPARM= in a PC SAS
configuration file entry to turn off threaded reads for all SAS users:
--dbsliceparm NONE
The following code demonstrates how to use DBSLICEPARM= as an OS/390
invocation option to turn on threaded reads for read-only references to DBMS tables
throughout a SAS job:
sas o(dbsliceparm=ALL)
The following code demonstrates how to use DBSLICEPARM= as a SAS global
option, most likely as one of the first statements in your SAS code, to increase
maximum threads to three for SAS threaded apps:
option dbsliceparm=(threaded_apps,3);
The following code demonstrates how to use DBSLICEPARM= as a LIBNAME option
to turn on threaded reads for read-only table references that use this particular libref:
libname dblib oracle user=scott password=tiger dbsliceparm=ALL;
The following code demonstrates how to use DBSLICEPARM= as a table level option
to turn on threaded reads for this particular table, requesting up to four connections:
proc reg SIMPLE;
data=dblib.customers (dbsliceparm=(all,4));
var age weight;
where years_active>1;
run;
