Considering a Bulk Load
Problem
You want to load large data volumes into a relational database.
Solution
You should consider using the optimized SAS/ACCESS engine bulk loaders to bulk load
the data into database tables. Many of the SAS/ACCESS engines for DBMS support the
BULKLOAD option, and this loading capability is one of the fastest ways to insert large
data volumes into a relational database.
By default, the SAS/ACCESS engines load data into tables by preparing an SQL
INSERT statement, executing the INSERT statement for each row, and periodically
issuing a COMMIT. If you specify BULKLOAD=YES as a data set or a LIBNAME
option, a database bulk-load method is used. This can significantly enhance
performance, especially when database tables are indexed.
Consult SAS documentation to determine whether the BULKLOAD option is supported
for your target database type and whether it can be specified as a LIBNAME or a data
set option. For each database there are additional options to specify behavior of the
bulkload option. These options can be found in the SAS/ACCESS documentation for the
specific database. The names of these options normally start with BL_.
Perform one of the following tasks to specify the BULKLOAD option:
“Set the BULKLOAD Option for a DBMS Library” on page 433
“Set the BULKLOAD Option for a DBMS Table” on page 434
Tasks
Set the BULKLOAD Option for a DBMS Library
Some SAS/ACCESS engines allow you to specify the BULKLOAD option on the
library. The LIBNAME statement enables you to assign a libref to a relational DBMS.
This feature lets you reference a DBMS object directly in a DATA step or SAS
procedure. You can use it to read from and write to a DBMS object as if it were a SAS
data set. You can associate a SAS libref with a relational DBMS database, schema,
server, or group of tables and views.
The following DBMSs support BULKLOAD on the library level:
ODBC
OLE DB
Teradata
Perform the following tasks to set the BULKLOAD= LIBNAME option:
1. Open the Properties window on the library icon, and select the Options tab.
2. Click on the Advanced Options button and select the Output tab.
3. Select Yes for the field labeled Whether to use DBMS's bulk load.
Considering a Bulk Load 433
Set the BULKLOAD Option for a DBMS Table
You can specify the BULKLOAD option to load on an individual table level by using
the data set option. This data set option applies only to the data set on which it is
specified, and it remains in effect for the duration of the DATA step or procedure.
The DBMSs that support BULKLOAD on the table level are:
DB2 UNIX for PC
DB2 for z/OS
Neoview
Netezza
ODBC
OLE DB
Oracle
Sybase
Teradata
Perform the following tasks to set the BULKLOAD= data set option:
1. Open the Properties window on the table icon and select the Options tab.
2. Click on the Table Options tab.
3. Enter BULKLOAD=YES in the field labeled Additional Table options.
434 Chapter 19 Working with Loader Transformations

Get SAS Data Integration Studio 4.9 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.