BL_OPTIONS= Data Set Option

Passes options to the DBMS bulk-load facility, which affects how it loads and processes data.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	DBMS-specific
Requirements:	To specify this option, you must first set BULKLOAD=YES. You must separate multiple options with commas and enclose the entire string of options in single quotation marks.
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, Netezza, OLE DB, Oracle, Sybase IQ
See:	BL_OPTIONS= LIBNAME option, BULKLOAD= LIBNAME option, BULKLOAD= data set option

Syntax

Details

Examples

Example 1: Specify the Number of Permitted Errors

Example 2: Specify External Table Options

Syntax

BL_OPTIONS='<option…,option>' [DB2 under UNIX and PC Hosts, OLE DB, Oracle]

BL_OPTIONS='<<option> > <<value> … '> [Aster nCluster, Netezza, Sybase IQ]

Syntax Description

option: specifies an option from the available options that are specific to each SAS/ACCESS interface. See the details in this section.

Details

You can use BL_OPTIONS= to pass options to the DBMS bulk-load facility when it is called, which affects how data is loaded and processed.

Aster nCluster: By default, no options are specified.

DB2 under UNIX and PC Hosts: This option passes DB2 file-type modifiers to DB2 LOAD or IMPORT commands to affect how data is loaded and processed. Not all DB2 file type modifiers are appropriate for all situations. You can specify one or more DB2 file type modifiers with .IXF files. For a list of file type modifiers, see the description of the LOAD and IMPORT utilities in the IBM DB2 Universal Database Data Movement Utilities Guide and Reference.

Netezza: Any text that you enter for this option is appended to the USING clause of the CREATE EXTERNAL TABLE statement—namely, any external_table_options in the Netezza Database User's Guide.

OLE DB: By default, no options are specified. This option is valid only when you are using the Microsoft SQL Server provider. This option takes the same values as the -h HINT option of the Microsoft BCP utility. For example, the ORDER= option sets the sort order of data in the data file; you can use it to improve performance if the file is sorted according to the clustered index on the table. See the Microsoft SQL Server documentation for a complete list of supported bulk copy options.

Oracle: This option lets you specify the SQL*Loader options ERRORS= and LOAD=. The ERRORS= option specifies the number of insert errors that terminates the load. The default value of ERRORS=1000000 overrides the default value for the Oracle SQL*Loader ERRORS= option, which is 50. LOAD= specifies the maximum number of logical records to load. If the LOAD= option is not specified, all rows are loaded. See your Oracle utilities documentation for a complete list of SQL*Loader options that you can specify in BL_OPTIONS=.

Sybase IQ: By default, no options are specified. Any text that you enter for this option is appended to the LOAD TABLE command that the SAS/ACCESS interface uses for the bulk-load process.

Examples

Example 1: Specify the Number of Permitted Errors

In this Oracle example BL_OPTIONS= specifies the number of errors that are permitted during a load of 2,000 rows of data, where all listed options are enclosed in quotation marks.

bl_options='ERRORS=999,LOAD=2000'

Example 2: Specify External Table Options

This Netezza example shows you how to use BL_OPTIONS= to specify two different external table options, ctrlchars and logdir:

data netlib.mdata(bulkload=yes bl_options="ctrlchars true logdir 'c:\temp'");
set saslib.transdata;
run;