DBSLICE= Data Set Option

Specifies user-supplied WHERE clauses to partition a DBMS query for threaded Reads.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	none
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:	DBSLICEPARM= LIBNAME option, DBSLICEPARM= data set option

Syntax

Details

Examples

Example 1: Partition a Column (Two Threads)

Example 2: Partition a Column (Three Threads)

Syntax

DBSLICE=("WHERE-clause-1" "WHERE-clause-2" <…" WHERE-clause-n">)

DBSLICE=(<server=> "WHERE-clause-1" <server=> "
WHERE-clause-2" < … <server=> " WHERE-clause-n"> )

Syntax Description

WHERE-clause

The WHERE clauses in the syntax signifies DBMS-valid WHERE clauses that partition the data. The clauses should not cause any omissions or duplications of rows in the results set. For example, if EMPNUM can be null, this DBSLICE= specification omits rows, creating anincorrect result set:

DBSLICE=("EMPNUM<1000" "EMPNUM>=1000")

Here is a correct form:

DBSLICE=("EMPNUM<1000" "EMPNUM>=1000" "EMPNUM IS NULL")

In this example, DBSLICE= creates an incorrect set by duplicating SALES with a value of 0.

DBSLICE=(“SALES<=0 or SALES=NULL” “SALES>=0”)

server

identifies a particular server node in a DB2 partitioned database or in a Microsoft SQL Server partitioned view. Use this to obtain the best possible read performance so that your SAS thread can connect directly to the node that contains the data partition that corresponds to your WHERE clause. See the DBMS-specific reference section for your interface for details.

Details

If your table reference is eligible for threaded Reads (that is, if it is a read-only LIBNAME table reference), DBSLICE= forces a threaded Read to occur. This partitions the table with the WHERE clauses that you supply. Use DBSLICE= when SAS is unable to generate threaded Reads automatically, or if you can provide better partitioning.

DBSLICE= is appropriate for experienced programmers familiar with the layout of their DBMS tables. A well-tuned DBSLICE= specification usually outperforms SAS automatic partitioning. For example, a well-tuned DBSLICE= specification might better distribute data across threads by taking advantage of a column that SAS/ACCESS cannot use when it automatically generates partitioning WHERE clauses.

DBSLICE= delivers optimal performance for DB2 under UNIX and for Microsoft SQL Server. Conversely, DBSLICE= can degrade performance compared to automatic partitioning. For example, Teradata starts the FastExport Utility for automatic partitioning. If DBSLICE= overrides this action, WHERE clauses are generated instead. Even with well planned WHERE clauses, performance is degraded because FastExport is considerably faster.

CAUTION:

When using DBSLICE=, you are responsible for data integrity. If your WHERE clauses omit rows from the result set or retrieves the same row on more than one thread, your input DBMS result set is incorrect and your SAS program generates incorrect results.

Examples

Example 1: Partition a Column (Two Threads)

In this example, DBSLICE= partitions on the GENDER column can have only the values m, M, f, and F. This DBSLICE= clause does not work for all DBMSs due to the use of UPPER and single quotation marks. Some DBMSs require double quotation marks around character literals. Two threads are created.

proc reg SIMPLE
data=lib.customers(DBSLICE="UPPER(GENDER)='M'" "UPPER(GENDER)='F'"));
var age weight;
where years_active>1;
run;

Example 2: Partition a Column (Three Threads)

The next example partitions on the non-null column CHILDREN, the number of children in a family. Three threads are created.

data local;
set lib.families(DBSLICE=("CHILDREN<2" "CHILDREN>2" "CHILDREN=2"));
where religion="P";
run;