Macro Variables and System Options for Relational Databases

DBIDIRECTEXEC= System Option

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

Default value:	NODBIDIRECTEXEC
Valid in:	configuration file, SAS invocation, OPTIONS statement, SAS System Options window
DBMS support:	Aster n Cluster, 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
Example

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

You can significantly improve CPU and input/output performance by using this option, which applies to all hosts and all SAS/ACCESS engines.

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.

Example

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;

In this next 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;

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;

Top of Page