Data Set Options for Relational Databases

BL_DISCARDFILE= Data Set Option

Identifies the file that contains records that were filtered from bulk load because they did not match the criteria as specified in the CONTROL file.

Default value:	creates a file in the current directory or with the default file specifications
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Oracle

Syntax
Syntax Description
Details
See Also

Syntax

BL_DISCARDFILE=path-and-discard-filename

Syntax Description

path-and-discard-filename

is an SQL*Loader discard file containing rows that did not meet the specified criteria. On most platforms, the default filename takes the form BL_<table>_<unique-ID>.dsc:

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.

Details

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

SQL*Loader creates the file of discarded rows only if there are discarded rows and if a discard file is requested. If you do not specify this option and a discard file does not exist, a discard file is created in the current directory (or with the default file specifications). If you do not specify this option and a discard file already exists, the Oracle bulk loader reuses the existing file and replaces the contents with discarded rows from the new load.

On most operating systems, the discard file has the same format as the data file, so the discarded records can be loaded after corrections are made.

Operating Environment Information: On z/OS operating systems, the discard file is created with default DCB attributes. For information about how to overcome such a case, see the section about SQL*Loader file attributes in the SQL*Loader chapter in the Oracle user's guide for z/OS. [cautionend]

Use BL_BADFILE= to set the name and location of the file that contains rejected rows.