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) |
|---|---|
| Categories: | Bulk Loading |
| Data Set Control | |
| Default: | DBMS-specific |
| Requirements: | To specify this option, you must first specify BULKLOAD=YES. |
| You must separate multiple options with commas (except in DB2) and enclose the entire string of options in single quotation marks. | |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Microsoft SQL Server, Netezza, OLE DB, Oracle, SAP IQ, Snowflake |
| Note: | Support for Microsoft SQL Server was added in SAS 9.4M7. |
| See: | BL_OPTIONS= LIBNAME option, BULKLOAD= LIBNAME option, BULKLOAD= data set option |
| Example: | For Amazon Redshift, this option adds the ROUNDEC
option to the end of the COPY command that is passed to the S3 tool.
|
Table of Contents
specifies an option from the available options that are specific to each SAS/ACCESS interface.
specifies an option and the value to assign to that option. The available options are specific to each SAS/ACCESS interface.
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.
Amazon Redshift: By default, no options are specified. Any options that you specify are added to the end of the COPY command that is executed by the S3 bulk load tool.
Aster: 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.
Microsoft SQL Server: This option is used to support bulk loading with the COPY command when you access Azure Synapse Analytics (SQL DW) using an Azure Data Lake Gen2 account.
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 specifies the sort order of data in the data file. 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=.
SAP 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-loading process.
Snowflake: By default, no options are specified.
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'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;