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 |
|---|---|
| Categories: | Files: External Files |
| System Administration: Performance | |
| Default: | DBIDIRECTEXEC |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC,Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick |
| Notes: | Beginning in SAS 9.4M8, the default for all listed data sources became DBIDIRECTEXEC. Prior to SAS 9.4M8, the default for these data sources had been NODBIDIRECTEXEC: DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, Impala, Informix, Microsoft SQL Server, MySQL, Netezza, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Teradata, Vertica. |
| Support for Spark and Yellowbrick was added in SAS 9.4M7. | |
| Support for ODBC was added in SAS 9.4M8. | |
| Tip: | Use the SQLIPONEATTEMPT system option, SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT environment variable, or both to further control how often you attempt to pass SQL statements to the database. See Control Attempts to Pass SQL to the Database. |
| See: | SQLIPONEATTEMPT system option |
Table of Contents
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, INSERT INTO TABLE, INSERT INTO VALUES, UPDATE, and DELETE statements.
indicates that the SQL pass-through facility does not optimize handling of SQL statements.
You can specify DBIDIRECTEXEC to significantly improve CPU, input, and output performance. The DBIDIRECTEXEC system option applies to specific SQL statements that you specify when using PROC SQL.
Certain database-specific criteria must be met to pass 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 Fails.
When the required criteria for passing SQL to the database 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). That is, when you enable DBIDIRECTEXEC, PROC SQL sends the CREATE TABLE AS SELECT statement to the database.
Note that when DBIDIRECTEXEC is enabled, any LENGTH= column modifier that is specified in the CREATE TABLE table-name AS SELECT statement is ignored. To use the LENGTH= column modifier, you must set NODBIDIRECTEXEC.
You can also send a DELETE, INSERT INTO TABLE, INSERT INTO VALUES, or UPDATE statement directly to the database for execution, which can improve CPU, input, and output performance.
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 a table by selecting from a SAS table is not sent to the database for execution. The reason is that the data sources are not compatible. The libref must also use the same database server for all compatible data sources.
If multiple librefs point to different data sources, the statement is processed as if NODBIDIRECTEXEC were specified, regardless of how you specified this option.
Once a system administrator specifies the default for this option globally, users can override it within their own configuration file.
Use the SQLIPONEATTEMPT system option force PROC SQL to stop additional attempts to pass part of an SQL query to the database if the first attempt to pass a query fails. If NOSQLIPONEATTEMPT is specified, then PROC SQL attempts to break a query into smaller parts that are then passed to the database. PROC SQL can attempt to pass SQL statements to the database until the statements are accepted by the database or until the query cannot be broken down further. Data might be passed between the database and SAS for each attempt to break a query into smaller parts.
The SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT environment variable prevents multiple attempts to pass an SQL query to the database during run time. When you set SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT=YES, you improve performance by preventing the transfer of potentially large amounts of data into SAS for further processing. The SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT environment variable is applicable to the CREATE TABLE AS … SELECT, INSERT INTO TABLE, INSERT INTO VALUES, UPDATE, and DELETE statements.
The SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT environment variable complements the SQLIPONEATTEMPT system option. SQLIPONEATTEMPT stops processing a query if it detects at compile time that the full query would not pass successfully to the database. SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT stops processing if a query fails to pass through to the database during run time. By preventing transfer of data into SAS, you can then modify your query so that it can pass successfully to the database for faster processing.
To set the SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT environment variable, specify SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT=YES as required for your operating environment. Alternatively, you can use the SET system option to set SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT in a SAS session, as shown here:
options set= SQL_DBIDIRECTEXEC_IP_ONE_ATTEMPT=YES;This example creates a temporary table from a SELECT statement using the DBIDIRECTEXEC system option.
libname lib1 db2 user=myusr1 password=mypwd1 datasrc=sample connection=global;
libname lib2 db2 user=myusr2 password=mypwd2 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 example, two librefs point to the same database server but use different schemas.
libname lib1 db2 user=myusr1 password=mypwd1 datasrc=sample;
libname lib2 db2 user=myusr2 password=mypwd2 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=myusr1 pw=mypwd1 path=mydb;
proc sql;
create table company.hr_tab as
select * from company.emp
where deptid = 'HR';
quit;