DBIDIRECTEXEC= System Option

Lets the SQL pass-through facility optimize handling of SQL statements by passing them directly to the database for execution.

Valid in:	configuration file, SAS invocation, OPTIONS statement, SAS System Options window
Category:	Files: External files, System administration: Performance
Default:	NODBIDIRECTEXEC
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:	DIRECT_EXE= LIBNAME option

Syntax

Details

Examples

Example 1: Create a Temporary Table

Example 2: Reference One Database, Use Different Schemas

Example 3: Pass a Statement Directly to the Database

Syntax

DBIDIRECTEXEC | NODBIDIRECTEXEC

Syntax Description

DBIDIRECTEXEC: indicates that the SQL pass-through facility optimizes handling of SQL statements by passing them directly to the database for execution, which optimizes performance. Using this option, you can process CREATE TABLE AS SELECT and DELETE statements.

NODBIDIRECTEXEC: indicates that the SQL pass-through facility does not optimize handling of SQL statements.

Details

This option applies to all hosts and all SAS/ACCESS engines. You can use it to significantly improve CPU, input, and output performance.

Certain database-specific criteria exist for passing SQL statements to the DBMS. These criteria are the same as the criteria that exist for passing joins. For details for your DBMS, see Passing Joins to the DBMS and When Passing Joins to the DBMS Will Fail.

When these criteria are met, a database can process the

CREATE TABLE table-name AS
                     SELECT

statement in a single step instead of as three separate statements (CREATE, SELECT, and INSERT). For example, if multiple librefs point to different data sources, the statement is processed normally, regardless of how you set this option. However, when you enable it, PROC SQL sends the CREATE TABLE AS SELECT statement to the database.

You can also send a DELETE statement directly to the database for execution, which can improve CPU, input, and output performance.

Once a system administrator sets the default for this option globally, users can override it within their own configuration file.

When you specify DBIDIRECTEXEC=, PROC SQL can pass this statement directly to the database:

CREATE TABLE table-name AS SELECT query

Before an SQL statement can be processed, all librefs that are associated with the statement must reference compatible data sources. For example, a CREATE TABLE AS SELECT statement that creates an Oracle table by selecting from a SAS table is not sent to the database for execution because the data sources are not compatible. The libref must also use the same database server for all compatible data sources.

Examples

Example 1: Create a Temporary Table

This example creates a temporary table from a SELECT statement using the DBIDIRECTEXEC system option.

libname lib1 db2 user=andy password=andypwd datasrc=sample connection=global;
libname lib2 db2 user=mike password=mikepwd datasrc=sample
    connection=global dbmstemp=yes;
data lib1.tab1;
     a=1;
     b='one';
run;
options dbidirectexec sastraceloc=saslog;
proc sql;
     create table lib2.tab1 as
     select * from lib1.tab1;
quit;

Example 2: Reference One Database, Use Different Schemas

In this example, two librefs point to the same database server but use different schemas.

libname lib1 db2 user=henry password=henrypwd datasrc=sample;
libname lib2 db2 user=scott password=scottpwd datasrc=sample;
data lib1.tab1;
  a=1;
  b='one';
run;
options dbidirectexec sastraceloc=saslog;
proc sql;
  create table lib2.tab2 as
  select * from lib1.t1;
quit;

Example 3: Pass a Statement Directly to the Database

This example shows how a statement can be passed directly to the database for execution, if you specify DBIDIRECTEXEC.

libname company oracle user=scott pw=tiger path=mydb;
proc sql;
	 create table company.hr_tab as
	 select * from company.emp
  where deptid = 'HR';
quit;