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 UNIX/PC, Greenplum, MySQL, ODBC, Oracle, Teradata

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 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 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, 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 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 Details: Oracle can handle 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 will produce 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: Build and Pass 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: Prevent 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;