MULTI_DATASRC_OPT= LIBNAME Option

Used in place of DBKEY to improve performance when processing a join between two data sources.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	NONE
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	DBMASTER= data set option

Syntax

Details

Examples

Example 1: Build and Pass an IN Clause for a Join

Example 2: Prevent Build and Pass of an IN Clause for a Join

Syntax

MULTI_DATASRC_OPT=NONE | IN_CLAUSE

Syntax Description

NONE: turns off option functionality.

IN_CLAUSE: specifies use of an IN clause that contains values that were read from a smaller table. The clause is used to retrieve matching values in a larger table based on a key column that was designated in an equijoin.

Details

When processing a join between a SAS data set and a DBMS table, the SAS data set should be smaller than the DBMS table for optimal performance. However, if the SAS data set is larger than the DBMS table, the SAS data set is still used in the IN clause.

When SAS processes a join between two DBMS tables, SELECT COUNT (*) is issued to determine which table is smaller and if it qualifies for an IN clause. You can use the DBMASTER= data set option to prevent the SELECT COUNT (*) from being issued.

The IN clause currently has a limit of 4,500 unique values.

Setting DBKEY= automatically overrides MULTI_DATASRC_OPT=.

DIRECT_SQL= can impact this option as well. If DIRECT_SQL=NONE or NOWHERE, the IN clause cannot be built and passed to the DBMS, regardless of the value of MULTI_DATASRC_OPT=. These settings for DIRECT_SQL= prevent a WHERE clause from being passed.

Oracle: Oracle can handle an IN clause of only 1,000 values. It therefore divides larger IN clauses into multiple smaller IN clauses. The results are combined into a single result set. For example, if an IN clause contained 4,000 values, Oracle produces 4 IN clauses, each of which contains 1,000 values. A single result is produced, as if all 4,000 values were processed as a whole.

OLE DB: OLE DB restricts the number of values allowed in an IN clause to 255.

Examples

Example 1: Build and Pass an IN Clause for a Join

This example builds and passes an IN clause from the SAS table to the DBMS table, retrieving only the necessary data to process the join.

proc sql;
create view work.v as
select tab2.deptno, tab2.dname from
work.sastable tab1, dblib.table2 tab2
where tab12.deptno = tab2.deptno
using libname dblib oracle user=testuser password=testpass
  multi_datasrc_opt=in_clause;
quit;

Example 2: Prevent Build and Pass of an IN Clause for a Join

This example prevents the building and passing of the IN clause to the DBMS. It requires all rows from the DBMS table to be brought into SAS to process the join.

libname dblib oracle user=testuser password=testpass multi_datasrc_opt=none;
proc sql;
   select tab2.deptno, tab2.dname from
     work.table1 tab1,
     dblib.table2 tab2
   where tab1.deptno=tab2.deptno;
quit;