About the DBIDIRECTEXEC System Option

When the DBIDIRECTEXEC system option is enabled, PROC SQL generates database-specific queries for CREATE TABLE AS SELECT, INSERT INTO TABLE ... VALUES, INSERT INTO TABLE ... SELECT, and DELETE FROM TABLE statements. This option can greatly improve PROC SQL query performance.

Note: The DBIDIRECTEXEC system option applies only to queries that are generated by using PROC SQL. DBIDIRECTEXEC has no impact on code that is passed to a database from the DATA step.

This example uses the SASHELP.CLASS data set and shows how PROC SQL generates a database-specific CREATE TABLE AS SELECT query.

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;
options dbidirectexec;

libname mynetdb netezza server='myserver.com' database=mydb user=myuser 
        password=mypwd;

/* clean up */
proc delete data=mynetdb.class; run;
proc delete data=mynetdb.class2; run;

/* create test data */
data mynetdb.class;
   set sashelp.class;
run;

proc sql;
   create table mynetdb.class2 as select * from mynetdb.class;
quit;

Here is the generated SQL in the SAS log.

CREATE TABLE MYDB..class2 as ( select TXT_1."NAME", TXT_1."SEX",
TXT_1."AGE", TXT_1."HEIGHT", TXT_1."WEIGHT" from MYDB..class TXT_1 )

When NODBIDIRECTEXEC is specified (DBIDIRECTEXEC is disabled), the CREATE TABLE AS SELECT operation is decomposed into separate CREATE, SELECT, and INSERT operations.

Here is the resulting SQL code for the same program when NODBIDIRECTEXEC is specified.

SELECT * FROM MYDB..class

CREATE TABLE MYDB..class2 (NAME VARCHAR(8),SEX VARCHAR(1),AGE DOUBLE,
HEIGHT DOUBLE,WEIGHT DOUBLE)

NETEZZA_21: Prepared: on connection 3
INSERT INTO MYDB..class2 (NAME,SEX,AGE,HEIGHT,WEIGHT) VALUES ( ? , ? ,
? , ? , ? )

If a query fails when DBIDIRECTEXEC is enabled, SAS retries the query as if NODBIDIRECTEXEC were specified.

Last updated: February 3, 2026