MULTI_DATASRC_OPT= LIBNAME Statement Option

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

Valid in:	LIBNAME statement
Default:	NONE
Supports:	DB2 for UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Syntax Description

Details

Examples

Example 1: Building and Passing an IN Clause for a Join

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

Syntax

MULTI_DATASRC_OPT= NONE | IN_CLAUSE

Syntax Description

NONE

turns off the functionality of the option.

IN_CLAUSE

specifies that an IN clause containing the values read from a smaller table will be used to retrieve the matching values in a larger table based on a key column designated in an equi-join.

Details

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

When SAS is processing a join between two DBMS tables, SELECT COUNT (*) is issued to determine which table is smaller and if it qualifies for an IN clause.

Currently, the IN clause has a limit of 4,500 unique values.

Setting the DBKEY=data set option overrides MULTI_DATASRC_OPT=.

DIRECT_SQL= can affect 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 Details: Oracle can process an IN clause of only 1,000 values. Therefore, it 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 that each contain 1,000 values. A single result will be produced, as if all 4,000 values were processed as a whole.

Examples

Example 1: Building and Passing an IN Clause for a Join

The following 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 tab1.deptno = tab2.deptno
   using libname dblib fedsvr server="d1234.us.company.com"  
      port=2171 user=user1 pwd=pass1
      dsn=oradsn multi_datasrc_opt=in_clause;
quit;

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

The following example prevents the building and passing of the IN clause to the DBMS, requiring all rows from the DBMS table to be brought into SAS for processing the join:

libname dblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn multi_datasrc_opt=none;
proc sql;
   select tab2.deptno, tab2.dname from
     work.table1 tab1,
     dblib.table2 tab2
   where tab1.deptno=tab2.deptno;
quit;