Data Set Options for Relational Databases

DBSLICEPARM= Data Set Option

Controls the scope of DBMS threaded reads and the number of DBMS connections.

Default value:	THREADED_APPS,2 [DB2 under z/OS, Oracle, and Teradata] THREADED_APPS,2 or 3 [DB2 under UNIX and PC Hosts, HP Neoview, Informix, Microsoft SQL Server, ODBC, and Sybase, Sybase IQ]
Valid in:	DATA and PROC Steps (when accessing DBMS data using SAS/ACCESS software) (also available as a SAS configuration file option, SAS invocation option, global SAS option, and LIBNAME option)
DBMS support:	DB2 under UNIX and PC Hosts, DB2 under z/OS, HP Neoview, Informix, Microsoft SQL Server, ODBC, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

DBSLICEPARM=NONE | THREADED_APPS | ALL

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

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

Syntax Description

Two syntax diagrams are shown here in order to highlight the simpler version. In most cases, the simpler version suffices.

NONE

disables DBMS threaded reads. 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. It includes SAS threaded applications, the SAS DATA step, and numerous SAS procedures.

max-threads

specifies with a positive integer value the maximum number of connections per table read. A partition or portion of the data is read on each connection. The combined rows across all partitions are the same irrespective 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.

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 in SAS. See the DBMS-specific reference section about threaded reads for your interface 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. 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 programs eligible for threaded reads. To determine whether threaded reads are actually generated, turn on SAS tracing and run a program, as shown in this 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.

DB2 under UNIX and PC Hosts, HP Neoview, 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

This code shows how you can use DBSLICEPARM= in a PC SAS configuration file entry to turn off threaded reads for all SAS users:

-dbsliceparm NONE

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)

You can use this code to set DBSLICEPARM= as a SAS global option to increase maximum threads to three for SAS threaded applications. It would most likely be one of the first statements in your SAS code:

option dbsliceparm=(threaded_apps,3);

This code uses 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;

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;