DBSLICEPARM= LIBNAME Option

Controls the scope of DBMS threaded Reads and the number of threads.

Valid in:	SAS/ACCESS LIBNAME statement (also available as a SAS configuration option, SAS invocation option, global SAS option, or data set option)
Default:	THREADED_APPS,2 (DB2 under z/OS, Oracle, Teradata), THREADED_APPS,2 or 3 (DB2 under UNIX and PC Hosts, HP Neoview, Informix, Microsoft SQL Server, ODBC, Sybase, Sybase IQ)
Data source:	DB2 under UNIX and PC Hosts, DB2 under z/OS, HP Neoview, Informix, Microsoft SQL Server, ODBC, Oracle, Sybase, Sybase IQ, Teradata
See:	DBSLICE= data set option, DBSLICEPARM= data set option, DBSLICEPARM= system option, SLEEP= LIBNAME option, SLEEP= data set option, TENACITY= LIBNAME option, TENACITY= data set option

Syntax

Details

Examples

Example 1: Disable Threaded Read for All SAS Users

Example 2: Enable Threaded Reads for Read-Only References

Example 3: Increase Maximum Threads (as a SAS Global Option)

Example 4: Enable Threaded Reads for References Using a Particular Libref

Example 5: Enable Threaded Reads as a Table-Level Option

Syntax

DBSLICEPARM=NONE | THREADED_APPS | ALL

DBSLICEPARM=( NONE | THREADED_APPS | ALL< max-threads> )

DBSLICEPARM=( NONE | THREADED_APPS | ALL<, max-threads> )

Syntax Description

NONE: disables DBMS threaded Read. SAS reads tables on a single DBMS connection, as it did with SAS 8 and earlier.

THREADED_APPS: makes fully threaded SAS procedures (threaded applications) eligible for threaded Reads.

ALL: makes all read-only librefs eligible for threaded Reads. This includes SAS threaded applications, as well as the SAS DATA step and numerous SAS procedures.

max-threads: a positive integer value that specifies the maximum number of connections per table read. The second parameter of the option determines the number of threads to read the table in parallel. The number of partitions on the table determine the number of connections made to the Oracle server for retrieving rows from the table. A partition or portion of the data is read on each connection. The combined rows across all partitions are the same regardless of the number of connections. That is, changes to the number of connections do not change the result set. Increasing the number of connections instead redistributes the same result set across more connections.

If the database table is not partitioned, SAS creates max-threads number of connections with WHERE MOD()… predicates and the same number of threads.

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 saved on the SAS step. See the DBMS-specific reference section for details about partitioned reads before using this parameter.

Details

You can use DBSLICEPARM= in numerous locations. The usual rules of option precedence apply: A table option has the highest precedence, then a LIBNAME option, and so on. 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 programs eligible for threaded Reads. To see whether threaded Reads are actually generated, turn on SAS tracing and run a program, as shown in this example.

options sastrace=”,,t” 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.

DB2 under UNIX and PC Hosts, Informix, Microsoft SQL Server, ODBC, Sybase, Sybase IQ: The default thread number depends on whether an application passes in the number of threads (CPUCOUNT=) and whether the data type of the column that was selected for purposes of data partitioning is binary.

Examples

Example 1: Disable Threaded Read for All SAS Users

Here is how to use DBSLICEPARM= in a SAS configuration file entry in Windows to turn off threaded Reads for all SAS users.

-dbsliceparm NONE

Example 2: Enable Threaded Reads for Read-Only References

Here is how you can use DBSLICEPARM= as a z/OS invocation option to turn on threaded Reads for read-only references to DBMS tables throughout a SAS job.

sas o(dbsliceparm=ALL)

Example 3: Increase Maximum Threads (as a SAS Global Option)

In this example, you can 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 applications.

option dbsliceparm=(threaded_apps,3);

Example 4: Enable Threaded Reads for References Using a Particular Libref

You can use DBSLICEPARM= as a LIBNAME option to turn on threaded Reads for read-only table references that use this particular libref, as shown in this example.

libname dblib oracle user=scott password=tiger dbsliceparm=ALL;

Example 5: Enable Threaded Reads as a Table-Level Option

Here is 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;