Data Set Options for Relational Databases

BL_DATAFILE= Data Set Option

Identifies the file that contains DBMS data for bulk load.

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

Syntax
Syntax Description
Details
See Also

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

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

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. The default is that 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.