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:

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.

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.
Copyright © SAS Institute Inc. All rights reserved.