Lets an SQL DELETE statement be passed directly to a DBMS with pass-through.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Default: | none |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HAWQ, Impala, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Teradata, Vertica |
| Tip: | 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. |
| See: | DBIDIRECTEXEC system option |
Table of Contents
specifies that an SQL DELETE statement is passed directly to the DBMS for processing.
Use the DBIDIRECTEXEC system option to pass additional statements directly to the database.
libname x oracle user=myusr1 password=mypwd1
path=oraclev8 schema=testschema
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:
ORACLE_9: Executed: delete from dbi_dft