The LIBNAME Statement for Relational Databases |
Allows
an SQL delete statement to be passed directly to a DBMS
with pass-through.
Default value: |
none
|
Valid in: |
SAS/ACCESS LIBNAME
statement
|
DBMS support: |
Aster nCluster, 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
|
-
DELETE
-
specifies that an SQL delete statement is
passed directly to the DBMS for processing.
Performance improves significantly by
using DIRECT_EXE=, because the SQL delete statement is passed directly to
the DBMS, instead of SAS reading the entire result set and deleting one row
at a time.
The following example demonstrates the use of DIRECT_EXE=
to 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:
SAS Log Output
ORACLE_9: Executed:
delete from dbi_dft
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.