The LIBNAME Statement for Relational Databases

DIRECT_EXE= LIBNAME Option

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

Syntax
Syntax Description
Details
Examples

Syntax

DIRECT_EXE=DELETE

Syntax Description

DELETE: specifies that an SQL delete statement is passed directly to the DBMS for processing.

Details

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.

Examples

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

Top of Page