Previous Page | Next Page

Data Set Options for Relational Databases

MULTILOAD= Data Set Option

Specifies whether Teradata insert and append operations should use the Teradata MultiLoad utility.
Default value: NO
Valid in: DATA and PROC steps (when creating and appending to DBMS tables using SAS/ACCESS software)
DBMS support: Teradata

Syntax Description
Bulk Loading
Data Buffers
Temporary Tables
Restarting MultiLoad
See Also



Syntax Description


uses the Teradata MultiLoad utility, if available, to load Teradata tables.


sends inserts to Teradata tables one row at a time.


Bulk Loading

The SAS/ACCESS MultiLoad facility provides a bulk-loading method of loading both empty and existing Teradata tables. Unlike FastLoad, MultiLoad can append data to existing tables.

To determine whether threaded reads are actually generated, turn on SAS tracing by setting OPTIONS SASTRACE=",,,d" in your program.

Data Buffers

Two data set options are available for tuning the number and the size of data buffers that are used for transferring data from SAS to Teradata. Data is transferred from SAS to Teradata using shared memory. The default shared memory buffer size is 64K. The default number of shared memory buffers used for the transfer is 2. You can use BUFFERS= to vary the number of buffers for data transfer from 1 to 8. You can use MBUFSIZE= to vary the size of the shared memory buffers from the size of each data row up to 1MB.

Temporary Tables

The Teradata MultiLoad utility uses four different temporary tables when it performs the bulk-load operation. It uses a log table to track restart information, two error tables to track errors, and a work table to hold data before the insert operation is made.

By default, the SAS/ACCESS MultiLoad facility generates names for these temporary tables, where randnum represents a random number. To specify a different name for these tables, use ML_RESTART=, ML_ERROR1=, ML_ERROR2=, and ML_WORK=, respectively.

Temporary Table Table Name
Restart table SAS_ML_RS_randnum
Acquisition error table SAS_ML_ET_randnum
Application error table SAS_ML_UT_randnum
Work table SAS_ML_WT_randnum

You can use ML_LOG= to specify a prefix for the temporary table names that MultiLoad uses.

Here is the order that is used for naming the temporary tables that MultiLoad uses:

  1. If you set ML_LOG=, the prefix that you specified is used when naming temporary tables for MultiLoad.

  2. If you do not specify ML_LOG=, the values that you specified for ML_ERROR1, ML_ERROR2, ML_WORK, ML_RESTART are used.

  3. If you do not specify any table naming options, temporary table names are generated by default.

Note:   You cannot use ML_LOG with any of these options: ML_ERROR1, ML_ERROR2, ML_WORK, and ML_RESTART.  [cautionend]

Restarting MultiLoad

The MultiLoad bulk-load operation (or MultiLoad job) works in phases. The first is the acquisition phase, during which data is transferred from SAS to Teradata work tables. The second is the application phase, during which data is applied to the target table.

If the MultiLoad job fails during the acquisition phase, you can restart the job from the last successful checkpoint. The exact observation from which the MultiLoad job must be restarted displays in the SAS log. If the MultiLoad job fails in the application phase--when data is loaded onto the target tables from the work table--restart the MultiLoad job outside of SAS. The MultiLoad restart script displays in the SAS log. You can run the generated MultiLoad script outside of SAS to complete the load.

You can use ML_CHECKPOINT= to specify the checkpoint rate. Specify a value for ML_CHECKPOINT= if you want restart capability. If checkpoint tracking is not used and the MultiLoad fails in the acquisition phase, the load needs to be restarted from the beginning. ML_CHECKPOINT=0 is the default, and no checkpoints are recoded if you use the default.

If ML_CHECKPOINT is between 1 and 59 inclusive, checkpoints are recorded at the specified interval in minutes. If ML_CHECKPOINT is greater than or equal to 60, then a checkpoint occurs after a multiple of the specified rows are loaded.

ML_CHECKPOINT= functions very much like the Teradata MultiLoad utility checkpoint, but it differs from the DBCOMMIT= data set option.

These restrictions apply when you restart a failed MultiLoad job.


This example uses MultiLoad to load SAS data to an alternate database. Note that it specifies database=mloaduser in the LIBNAME statement.

libname trlib teradata user=testuser pw=testpass server=dbc database=mloaduser;  
/*MultiLoad 20000 observations into alternate database mloaduser */ 

data trlib.trmload14(DBCREATE_TABLE_OPTS="PRIMARY INDEX(IDNUM)" MultiLoad=yes
 set permdata.BIG1MIL(drop=year obs=20000); 

This example extracts data from one table using FastExport and loads data into another table using MultiLoad.

libname trlib teradata user=testuser pw=testpass server=dbc; 
/*  Create data to load  */ 
data trlib.trodd(DBCREATE_TABLE_OPTS="PRIMARY INDEX(IDNUM)" MultiLoad=yes);   
  set permdata.BIG1MIL(drop=year obs=10000);   
where mod(IDNUM,2)=1; 

/*  FastExport from one table and MultiLoad into another  */ 
proc append data=trlib.treven(dbsliceparm=all)  base=trlib.trall(MultiLOAD=YES); 

See Also

For information about specifying how long to wait before retrying a logon operation, see the SLEEP= Data Set Option.

For information about specifying how many hours to continue to retry a logon operation, see the TENACITY= Data Set Option

For information about specifying a prefix for the temporary table names that MultiLoad uses, see the ML_LOG= Data Set Option.

BUFFERS= Data Set Option


BULKLOAD= Data Set Option


DBCOMMIT= Data Set Option


Maximizing Teradata Load Performance

MBUFSIZE= Data Set Option

ML_CHECKPOINT= Data Set Option

ML_ERROR1= Data Set Option

ML_ERROR2= Data Set Option

ML_RESTART= Data Set Option

ML_WORK= Data Set Option


QUERY_BAND= Data Set Option

Using MultiLoad

Previous Page | Next Page | Top of Page