The LIBNAME Statement for Relational Databases

MULTI_DATASRC_OPT= LIBNAME Option

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

Default value:	NONE
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	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

Syntax
Syntax Description
Details
Examples
See Also

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 are used to retrieve the matching values in a larger table based on a key column 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, in the event that the SAS data set is larger than that 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.

Currently, the IN clause 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. 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 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

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;

The next 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;