Identifies the file that contains DBMS data for bulk loading.
| Valid in: | DATA and PROC steps (when accessing data using SAS/ACCESS software) |
|---|---|
| Categories: | Bulk Loading |
| Data Set Control | |
| Default: | DBMS-specific |
| Requirement: | To specify this option, you must first specify BULKEXTRACT= YES or BULKLOAD=YES. |
| Data source: | Aster, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Impala, Informix, Netezza, Oracle, PostgreSQL, SAP IQ, Yellowbrick |
| Notes: | Support for Yellowbrick was added in SAS 9.4M7. |
| Support for Informix was added in SAS 9.4M9. | |
| See: | BL_DATAFILE= [Teradata], 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, BULKLOAD= data set option |
Table of Contents
specifies the file that contains the rows of data to load or append into a DBMS table during bulk loading. On most platforms, the default file name takes the form BL_<table>_<unique-ID>.ext:
specifies the table name.
specifies a number that is used to prevent collisions in the event of two or more simultaneous bulk loadings of a particular table. The SAS/ACCESS engine generates the number.
specifies the file extension (.DAT or .IXF) for the data file.
DB2 under UNIX and PC Hosts: The default is the temporary file directory that is specified by the UTILLOC= system option.
Greenplum, HAWQ: 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 file name is generated automatically. When you specify the file name with a full path, the path overrides the value of the GPLOAD_HOME environment variable. However, bulk loading might fail if the path does not match the base directory that the gpfdist utility used.
Informix: There is no default value for Informix. If you specify a value for this option, then the value is used to generate the bulk loading data file. If you specify a value for BL_DATAFILE=, then SAS does not use the value that you might have set for BL_DEFAULT_DIR=.
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 temporary file directory that is specified by the UTILLOC= system option or by using the default file specifications before SAS/ACCESS calls 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 temporary file directory that is specified by the UTILLOC= system option or in another directory that is specified by your 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 loading to fail instead of reusing a data file.
SAP IQ: By default, the SAS/ACCESS engine creates a data file with a .dat file extension in the temporary file directory that is specified by the UTILLOC= system option or in another directory that is specified by your 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.