Data Set Options for Relational Databases

BL_DELETE_DATAFILE= Data Set Option

Specifies whether to delete only the data file or all files that the SAS/ACCESS engine creates for the DBMS bulk-load facility.

Alias:	BL_DELETE_FILES= [Oracle]
Default value:	YES
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Netezza, Oracle, Sybase IQ

Syntax

BL_DELETE_DATAFILE=YES | NO

YES: deletes all (data, control, and log) files that the SAS/ACCESS engine creates for the DBMS bulk-load facility.
NO: does not delete these files.

To specify this option, you must first set BULKLOAD=YES or BULKEXTRACT=YES.

DB2 under UNIX and PC Hosts: Setting BL_DELETE_DATAFILE=YES deletes only the temporary data file that SAS/ACCESS creates after the load completes.

Greenplum: When BL_DELETE_DATAFILE=YES, the external data file is deleted after the load completes.

HP Neoview, Netezza: You can use this option only when BL_USE_PIPE=NO.

Oracle: When BL_DELETE_DATAFILE=YES, all files (DAT, CTL, and LOG) are deleted.

The default is YES in this example, so all files are deleted:

libname x oracle &connopts
proc delete data=x.test1;
run;

data x.test1 ( bulkload=yes );
c1=1;
run;

x dir BL_TEST1*.*;

No files are deleted in this example:

libname x oracle &connopts
proc delete data=x.test2;
run;

data x.test2 ( bulkload=yes bl_delete_files=no );
c1=1;
run;

x dir BL_TEST2*.*;