160 BL_PRESERVE_BLANKS= Data Set Option Chapter 10
Syntax Description
option
is among the available options that are specific to each SAS/ACCESS interface. See
details below.
Details
BL_OPTIONS= enables you to pass options to the DBMS bulk load facility when it is
invoked, thereby affecting how data is loaded and processed. You must separate
multiple options with commas and enclose the entire string of options in quotation
marks.
DB2 UNIX/PC Details: 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 “DB2 Data Movement Utilities Guide and Reference.”
OLE DB Details: By default, no options are specified. This option is only valid when
you are using the 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, and can be used to improve performance if the file is
sorted according to the clustered index on the table. Refer to the Microsoft SQL Server
documentation for a complete list of supported bulk copy options.
Oracle Details: This option allows you to specify the SQL*Loader options ERRORS=
and LOAD=. The ERRORS= option specifies the number of insert errors that will
terminate 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. Refer to the Oracle utilities documentation for a complete list of
SQL*Loader options that you can specify in BL_OPTIONS=.
Example
In the following Oracle example, BL_OPTIONS= specifies the number of errors that are
permitted during a load of 2,000 rows of data. Notice that the entire listing of options is
enclosed in quotation marks.
bl_options=’ERRORS=999,LOAD=2000’
See Also
To assign this option to a group of relational DBMS tables or views, see the
LIBNAME option “BL_OPTIONS= LIBNAME Option” on page 72.
BL_PRESERVE_BLANKS= Data Set Option
Determines how the SQL*Loader handles requests to insert blank spaces into CHAR/VARCHAR2
columns with the NOT NULL constraint
Valid in: DATA and PROC steps (when accessing DBMS data using SAS/ACCESS
software)
