BL_DATAFILE= Data Set Option

Identifies the file that contains DBMS data for bulk load.

Valid in:	DATA and PROC steps (when accessing data using SAS/ACCESS software)
Default:	DBMS-specific
Requirement:	To specify this option, you must first set BULKEXTRACT= YES or BULKLOAD=YES.
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Netezza, Oracle, Sybase IQ
See:	BL_CONTROL= data set option, BL_DELETE_DATAFILE= data set option, BL_DELETE_ONLY_DATAFILE= data set option, BL_PROTOCOL= data set option, BL_USE_PIPE= data set option, BULKEXTRACT= data set option, BULKLOAD= data set option

Syntax

Details

Syntax

BL_DATAFILE=path-and-data-filename

Syntax Description

path-and-data-filename

specifies the file that contains the rows of data to load or append into a DBMS table during bulk load. On most platforms, the default filename takes the form BL_<table>_<unique-ID>.ext:

table: specifies the table name.

unique-ID: specifies a number that is used to prevent collisions in the event of two or more simultaneous bulk loads of a particular table. The SAS/ACCESS engine generates the number.

ext: specifies the file extension (.DAT or .IXF) for the data file.

Details

DB2 under UNIX and PC Hosts: The default is the current directory.

Greenplum: This option specifies the name of the external file to load. It is meaningful only when BL_PROTOCOL= is set to gpfdist or file. If you do not specify this option, the filename is generated automatically. When you specify the filename with a full path, the path overrides the value of the GPLOAD_HOME environment variable. However, bulk load might fail if the path does not match the base directory that the gpfdist utility used.

HP Neoview, Netezza: You can use this option only when BL_USE_PIPE=NO. By default the SAS/ACCESS engine creates a data file from the input SAS data set in the current directory or with the default file specifications before calling the bulk loader. The data file contains SAS data that is ready to load into the DBMS. By default, the data file is deleted after the load is completed. To override this behavior, specify BL_DELETE_DATAFILE=NO.

Oracle: The SAS/ACCESS engine creates this data file from the input SAS data set before calling the bulk loader. The data file contains SAS data that is ready to load into the DBMS. By default, the data file is deleted after the load is completed. To override this behavior, specify BL_DELETE_DATAFILE=NO. If you do not specify this option and a data file does not exist, the file is created in the current directory or with the default file specifications. If you do not specify this option and a data file already exists, SAS/ACCESS reuses the file, replacing the contents with the new data. SAS/ACCESS Interface to Oracle on z/OS is the exception: The data file is never reused because the interface causes bulk load to fail instead of reusing a data file.

Sybase IQ: By default, the SAS/ACCESS engine creates a data file with a .DAT file extension in the current directory or with the default file specifications. Also, by default, the data file is deleted after the load is completed. To override this behavior, specify BL_DELETE_DATAFILE=NO.