Data Set Options for Relational Databases DBSLICE= Data Set Option 187
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 ODBC and DB2 UNIX/PC.
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, the following
DBSLICE 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 the following example, DBSLICE creates an incorrect set by duplicating SALES
with value zero:
DBSLICE=(‘‘SALES<=0 or SALES=NULL’’ ‘‘SALES>=0’’)
server
identifies a particular server node in a DB2 partitioned database or in an SQL server
partitioned view. Used for the best possible read performance, this enables your SAS
thread to directly connect to the node containing the data partition corresponding to
your WHERE clause. See the DBMS-specific documentation for more details:
DB2 UNIX/PC
ODBC
Details
If your table reference is eligible for threaded read (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
DBMS tables. A well-tuned DBSLICE= will usually outperform SAS automatic
partitioning. For example, a well-tuned DBSLICE= 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 UNIX and Microsoft SQL Server.
Conversely, DBSLICE= can degrade performance compared to automatic partitioning.
For example, Teradata invokes the FastExport Utility for automatic partitioning. If this
is overridden with DBSLICE=, WHERE clauses are generated instead. Even with well
planned WHERE clauses, performance is degraded because FastExport is innately
faster.
CAUTION:
When using DBSLICE=, you are responsible for data integrity. If your DBSLICE= clauses
omit rows from the result set or retrieve the same row on more than one thread, your input
DBMS result set will be incorrect and will cause your SAS step to generate incorrect
results.
Examples
In the following example, DBSLICE= partitions on the column GENDER which can
only have the values
m, M, f, and F. This DBSLICE= clause will 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 read threads are created.
