SAS Data Integration Studio 4.9

The Oracle Bulk Table Loader contains several tabs to define the bulk loading method to

use. The Load Technique tab and Table Options tab are specific to Oracle. Other

loader options can be set on the Options tab.

The Oracle Bulk Table Loader functions like other loaders, but it also provides

additional options available on the Load Technique tab. These options enable users to

select the best method to load their data. The default bulk load method is Insert, and

other options include Append, Replace, and Truncate. Additional options on the Load

Technique tab allow users to drop and recreate indexes and constraints and to gather

table statistics after the table has been bulk loaded.

In order for the Oracle Bulk Table Loader functionality to work properly, follow these

data and configuration considerations:

• Oracle does not support table names with spaces in the name, so any table created in

metadata with this name will not load properly.

• When an index is dropped or created, the index must be unique to the target table.

The index cannot be used on any other table without causing a failure when trying to

create the index because the index already exists in the database.

• The SQL loader must be installed as part of the Oracle client.

About the DB2 Bulk Table Loader

The DB2 Bulk Table Loader transformation can be added to a process flow to take large

amounts of data from SAS and most DBMS source tables and bulk load it to a DB2

target. The DB2 Bulk Table Loader functions like other loaders. However, it loads only

UDB (Linux, UNIX, and Windows), not z/OS. Note that it does not support ODBC to

DB2 or OLE/DB to DB2.

The DB2 Bulk Table Loader contains several tabs to define the bulk loading method to

use. These tabs include the Load Technique tab, the Table Options tab, and the Loader

pane in the Options tab.

The options on the Load Technique tab enable users to gather table statistics after the

table has been bulk loaded and to select the best method to load their data. The default

bulk load method is CliLoad, and other options include Import, Load, and CliLoad with

truncate.

The other bulk load methods require certain privileges. To use the Load or CliLoad

method, a user must have system administrator authority, database administrator

authority, or load authority on the database. The user must also have Insert privileges on

the table being loaded. The Import method does not offer the same level of performance

as the Load method. However, it is available to all users who have Insert privileges on

the tables being loaded.

After the bulk load is processed, code is saved by the DB2 loader to retain statistics for

quicker execution the next time the table is loaded. The user can set a value for the

number of frequent values that are used in the generated code. This value is entered on

the Options tab of the Properties window.

Note: If indexes or constraints exist in metadata for a table that does not already exist at

load time, then indexes registered in metadata will be used at create time. This is the

only time that the metadata is read when creating indexes for a table using the DB2

Bulk Table Loader.

426 Chapter 19 • Working with Loader Transformations