SAS Data Integration Studio 4.9

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