Lets an SQL delete statement be passed directly
to a DBMS with pass-through.
Valid in:
SAS/ACCESS LIBNAME statement
Default:
none
Data source:
Aster n, 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
specifies that an SQL
delete statement is passed directly to the DBMS for processing.
Details
Performance improves
significantly by using DIRECT_EXE=. This is because the SQL delete
statement is passed directly to the DBMS instead of having SAS read
the entire result set and delete one row at a time.
Example: Empty a Table from a Database
libname x oracle user=scott password=tiger
path=oraclev8 schema=dbitest
direct_exe=delete; /* Create an Oracle table of 5 rows. */
data x.dbi_dft;
do col1=1 to 5;
output;
end;
run;
options sastrace=",,,d" sastraceloc=saslog nostsuffix;
proc sql;
delete * from x.dbi_dft;
quit;
By turning trace on,
you should see something similar to this: