Data Set Options for Relational Databases

BULKLOAD= Data Set Option

Loads rows of data as one unit.

Alias:	BL_DB2LDUTIL= [DB2 under z/OS], FASTLOAD= [Teradata]
Default value:	NO
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
See Also

Syntax

BULKLOAD=YES | NO

Syntax Description

YES: calls a DBMS-specific bulk-load facility to insert or append rows to a DBMS table.
NO: uses the dynamic SAS/ACCESS engine to insert or append data to a DBMS table.

Details

Using BULKLOAD=YES is the fastest way to insert rows into a DBMS table.

See SAS/ACCESS documentation for your DBMS interface for details.

When BULKLOAD=YES, the first error encountered causes the remaining rows (including the erroneous row) in the buffer to be rejected. No other errors within the same buffer are detected, even if the ERRLIMIT= value is greater than one. In addition, all rows before the error are committed, even if DBCOMMIT= is larger than the number of the erroneous row.

Sybase: When BULKLOAD=NO, insertions are processed and rolled back as expected according to DBCOMMIT= and ERRLIMIT= values. If the ERRLIMIT= value is encountered, all uncommitted rows are rolled back. The DBCOMMIT= data set option determines the commit intervals. For details, see the DBMS-specific reference section for your interface.