DIRECT_SQL= LIBNAME Option

Specifies whether generated SQL is passed to the DBMS for processing.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	YES
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:	SQL_FUNCTIONS= LIBNAME option

Syntax

Details

Examples

Example 1: Prevent a DBMS from Processing a Join

Example 2: Prevent a DBMS from Processing a SAS Function

Syntax

Syntax Description

YES: specifies that generated SQL from PROC SQL is passed directly to the DBMS for processing.

NO: specifies that generated SQL from PROC SQL is not passed to the DBMS for processing. This is the same as specifying the value NOGENSQL.

NONE: specifies that generated SQL is not passed to the DBMS for processing. This includes SQL that is generated from PROC SQL, SAS functions that can be converted into DBMS functions, joins, and WHERE clauses.

NOGENSQL: prevents PROC SQL from generating SQL to be passed to the DBMS for processing.

NOWHERE: prevents WHERE clauses from being passed to the DBMS for processing. This includes SAS WHERE clauses and PROC SQL generated or PROC SQL specified WHERE clauses.

NOFUNCTIONS: prevents SQL statements from being passed to the DBMS for processing when they contain functions.

NOMULTOUTJOINS: specifies that PROC SQL does not attempt to pass any multiple outer joins to the DBMS for processing. Other join statements might be passed down however, including portions of a multiple outer join.

Details

By default, processing is passed to the DBMS whenever possible, because the database might be able to process the functionality more efficiently than SAS does. In some instances, however, you might not want the DBMS to process the SQL. For example, the presence of null values in the DBMS data might cause different results depending on whether the processing takes place in SAS or in the DBMS. If you do not want the DBMS to handle the SQL, use DIRECT_SQL= to force SAS to handle some or all SQL processing.

If you specify DIRECT_SQL=NOGENSQL, PROC SQL does not generate DBMS SQL. This means that SAS functions, joins, and DISTINCT processing that occur within PROC SQL are not passed to the DBMS for processing. (SAS functions outside PROC SQL can still be passed to the DBMS.) However, if PROC SQL contains a WHERE clause, the WHERE clause is passed to the DBMS, if possible. Unless you specify DIRECT_SQL=NOWHERE, SAS attempts to pass all WHERE clauses to the DBMS.

If you specify more than one value for this option, separate the values with spaces and enclose the list of values in parentheses. For example, you could specify DIRECT_SQL=(NOFUNCTIONS, NOWHERE).

DIRECT_SQL= overrides the SQL_FUNCTIONS= LIBNAME option. If you specify SQL_FUNCTIONS=ALL and DIRECT_SQL=NONE, no functions are passed.

Examples

Example 1: Prevent a DBMS from Processing a Join

This example prevents the DBMS from processing a join between two tables by setting DIRECT_SQL=NOGENSQL. SAS processes the join instead.

proc sql;
create view work.v as
   select tab1.deptno, dname from
          mydblib.table1 tab1,
          mydblib.table2 tab2
   where tab1.deptno=tab2.deptno
   using libname mydblib oracle user=testuser
          password=testpass path=myserver direct_sql=nogensql;

Example 2: Prevent a DBMS from Processing a SAS Function

libname mydblib oracle user=testuser password=testpass direct_sql=nofunctions;
proc print data=mydblib.tab1;
   where lastname=soundex ('Paul');