Data Set Options for Relational Databases

DBSLICE= Data Set Option

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

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

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

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

Two syntax diagrams are shown here to highlight the simpler version. In many cases, the first, simpler syntax is sufficient. The optional server= form is valid only for DB2 under UNIX and PC Hosts, Netezza, and ODBC.

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 an incorrect result set:

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

A correct form is:

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, partitioning the table with the WHERE clauses 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

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;

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;

DBSLICE= Data Set Option

Syntax

Syntax Description