Controls the scope of DBMS threaded Reads and the number of DBMS connections.
| 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) |
|---|---|
| Category: | Data Set Control |
| Default: | NONE [DB2 under UNIX and PC Hosts, Greenplum, Microsoft SQL Server, Vertica] |
| THREADED_APPS, none [HAWQ] | |
| THREADED_APPS,2 [DB2 under z/OS, Oracle] | |
| THREADED_APPS,2 or THREADED_APPS,3 [Informix, ODBC, SAP ASE, SAP HANA, SAP IQ] | |
| none (no default value) [Google BigQuery, Teradata] | |
| Restriction: | Teradata: This option is not supported for Teradata on z/OS. Instead, you can set FASTLOAD=YES. |
| Interaction: | Google BigQuery: To use this option, you must also set READ_MODE=STORAGE (or MODE=PERFORMANCE, which sets READ_MODE=STORAGE). Otherwise, this option is ignored. |
| Data source: | DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, HAWQ, Informix, Microsoft SQL Server, ODBC, Oracle, SAP ASE, SAP HANA, SAP IQ, Teradata, Vertica |
| Note: | Support for Google BigQuery was added in SAS 9.4M9. Support for Google BigQuery on SAS 9.4M8 was added in December 2025. |
| See: | DBSLICE= data set option, DBSLICEPARM= LIBNAME option, DBSLICEPARM= system option, FASTLOAD= LIBNAME option, SLEEP= LIBNAME option, SLEEP= data set option, TENACITY= LIBNAME option, TENACITY= data set option |
Table of Contents
disables DBMS threaded Reads. SAS reads tables on a single DBMS connection.
makes fully threaded SAS procedures (threaded applications) eligible for threaded Reads.
makes all read-only librefs eligible for threaded Reads. It includes SAS threaded applications, the SAS DATA step, and numerous SAS procedures.
| Restriction | Teradata: Starting in SAS 9.4M9, do not specify ALL for this option. |
|---|
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.
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.
You can use DBSLICEPARM= in numerous locations. The usual rules of option precedence apply: A table (data set) option has the highest precedence, followed by 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 value.
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, Informix, Microsoft SQL Server, ODBC, SAP ASE, SAP 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.
Greenplum, HAWQ: There is no default value for the maximum number of connections per table read. This value depends on the number of partitions in a table and the arguments that are used with the MOD function in a WHERE clause. For more information, see Autopartitioning Techniques in SAS/ACCESS.
Teradata: Starting in SAS 9.4M9, the value ALL is not supported. This option is not supported for Teradata on z/OS. Instead, you can specify FASTLOAD=YES.
Here is how to use DBSLICEPARM= in a SAS configuration file entry in Windows to turn off threaded Reads for all SAS users.
–dbsliceparm NONEHere 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)In this example, you can use DBSLICEPARM= as a SAS global option to increase maximum threads to three for SAS threaded applications. Most likely, you would use it as one of the first statements in your SAS code.
option dbsliceparm=(threaded_apps,3);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=myusr1 password=mypwd1 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;