Options That Affect Threaded Reads

For threaded reads from DBMSs, SAS/ACCESS provides these data set options: DBLICE= and DBICEPARM=.

DBSLICE= applies only to a table reference. You can use it to code your own WHERE clauses to partition table data across threads, and it is useful when you are familiar with your table data. For example, 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. Here is an example:

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. With the default DBSLICEPARM=THREADED_APPS setting, SAS automatically attempts threaded reads only for SAS threaded applications, which are SAS procedures that thread I/O and numeric operations. DBSLICEPARM=ALL extends threaded reads to more SAS procedures, specifically steps that only read tables. Or, DBSLICEPARM=NONE turns it off entirely. You can specify it as a data set option, a LIBNAME option, or 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.

  -dbsliceparm ALL

• Global SAS option that turns off threaded reads.

  option dbsliceparm=NONE;

• LIBNAME option that restricts threaded reads to just SAS threaded applications.

  libname lib oracle user=scott password=tiger dbsliceparm=THREADED_APPS;

• Table option that turns on threaded reads (with a maximum of three connections, in the example below)

  proc print data=lib.dbtable(dbsliceparm=(ALL,3));
  where dbcol>1000;
  run;

DBSLICE= and DBSLICEPARM= apply only to DBMS table reads. THREADS= and CPUCOUNT= are additional SAS options that apply to threaded applications. For more information about these options, see the SAS Language Reference: Dictionary.

Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.