SAS/ACCESS Interface to Teradata

Maximizing Teradata Load Performance

To significantly improve performance when loading data, SAS/ACCESS Interface to Teradata provides these facilities. These correspond to native Teradata utilities.

FastLoad
MultiLoad
Multi-Statement

SAS/ACCESS also supports the Teradata Protocol Transporter application programming interface (TPT API), which you can also use with these facilities.

Using FastLoad

FastLoad Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports a bulk-load capability called FastLoad that greatly accelerates insertion of data into empty Teradata tables. For general information about using FastLoad and error recovery, see the Teradata FastLoad documentation. SAS/ACCESS examples are available.

Note: Implementation of SAS/ACCESS FastLoad facility will change in a future release of SAS. So you might need to change SAS programming statements and options that you specify to enable this feature in the future. [cautionend]

The SAS/ACCESS FastLoad facility is similar to the native Teradata FastLoad Utility. They share these limitations:

FastLoad can load only empty tables; it cannot append to a table that already contains data. If you attempt to use FastLoad when appending to a table that contains rows, the append step fails.
Both the Teradata FastLoad Utility and the SAS/ACCESS FastLoad facility log data errors to tables. Error recovery can be difficult. To find the error that corresponds to the code that is stored in the error table, see the Teradata FastLoad documentation.
FastLoad does not load duplicate rows (rows where all corresponding fields contain identical data) into a Teradata table. If your SAS data set contains duplicate rows, you can use the normal insert (load) process.

Starting FastLoad

If you do not specify FastLoad, your Teradata tables are loaded normally (slowly). To start FastLoad in the SAS/ACCESS interface, you can use one of these items:

the BULKLOAD=YES data set option in a processing step that populates an empty Teradata table
the BULKLOAD=YES LIBNAME option on the destination libref (the Teradata DBMS library where one or more intended tables are to be created and loaded)
the FASTLOAD= alias for either of these options

FastLoad Data Set Options

Here are the data set options that you can use with the FastLoad facility.

BL_LOG= specifies the names of error tables that are created when you use the SAS/ACCESS FastLoad facility. By default, FastLoad errors are logged in Teradata tables named SAS_FASTLOAD_ERRS1_randnum and SAS_FASTLOAD_ERRS2_randnum, where randnum is a randomly generated number. For example, if you specify BL_LOG=my_load_errors , errors are logged in tables my_load_errors1 and my_load_errors2. If you specify BL_LOG=errtab , errors are logged in tables name errtab1 and errtab2.

Note: SAS/ACCESS automatically deletes the error tables if no errors are logged. If errors occur, the tables are retained and SAS/ACCESS issues a warning message that includes the names of the error tables.
DBCOMMIT=n causes a Teradata "checkpoint" after each group of n rows is transmitted. Using checkpoints slows performance but provides known synchronization points if failure occurs during the loading process. Checkpoints are not used by default if you do not explicitly set DBCOMMIT= and BULKLOAD=YES. The Teradata alias for this option is CHECKPOINT=.

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

Using MultiLoad

MultiLoad Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports a bulk-load capability called MultiLoad that greatly accelerates insertion of data into Teradata tables. For general information about using MultiLoad with Teradata tables and for information about error recovery, see the Teradata MultiLoad documentation. SAS/ACCESS examples are available.

Unlike FastLoad, which only loads empty tables, MultiLoad loads both empty and existing Teradata tables. If you do not specify MultiLoad, your Teradata tables are loaded normally (inserts are sent one row at a time).

The SAS/ACCESS MultiLoad facility loads both empty and existing Teradata tables. SAS/ACCESS supports these features:

You can load only one target table at a time.
Only insert operations are supported.

Because the SAS/ACCESS MultiLoad facility is similar to the native Teradata MultiLoad utility, they share a limitation in that you must drop the following items on the target tables before the load:

unique secondary indexes
foreign key references
join indexes

Both the Teradata MultiLoad utility and the SAS/ACCESS MultiLoad facility log data errors to tables. Error recovery can be difficult, but the ability to restart from the last checkpoint is possible. To find the error that corresponds to the code that is stored in the error table, see the Teradata MultiLoad documentation.

MultiLoad Setup

Here are the requirements for using the MultiLoad bulk-load capability in SAS.

The native Teradata MultiLoad utility must be present on your system. If you do not have the Teradata MultiLoad utility and you want to use it with SAS, contact Teradata to obtain the utility.
SAS must be able to locate the Teradata MultiLoad utility on your system.
The Teradata MultiLoad utility must be able to locate the SASMlam access module and the SasMlne exit routine. They are supplied with SAS/ACCESS Interface to Teradata.
SAS MultiLoad requires Teradata client TTU 8.2 or later.

If it has not been done so already as part of the post-installation configuration process, see the SAS configuration documentation for your system for information about how to configure SAS to work with MultiLoad.

MultiLoad Data Set Options

Call the SAS/ACCESS MultiLoad facility by specifying MULTILOAD=YES. See the MULTILOAD= data set option for detailed information and examples on loading data and recovering from errors during the load process.

Here are the data set options that are available for use with the MultiLoad facility. For detailed information about these options, see Data Set Options for Relational Databases.

MBUFSIZE=
ML_CHECKPOINT=
ML_ERROR1= lets the user name the error table that MultiLoad uses for tracking errors from the acquisition phase. See the Teradata MultiLoad reference for more information about what is stored in this table. By default, the acquisition error table is named SAS_ML_ET_randnum where randnum is a random number. When restarting a failed MultiLoad job, you need to specify the same acquisition table from the earlier run so that the MultiLoad job can restart correctly. Note that the same log table, application error table, and work table must also be specified upon restarting, using ML_RESTART, ML_ERROR2, and ML_WORK data set options. ML_ERROR1 and ML_LOG are mutually exclusive and cannot be specified together.
ML_ERROR2=
ML_LOG= specifies a prefix for the temporary tables that the Teradata MultiLoad utility uses during the load process. The MultiLoad utility uses a log table, two error tables, and a work table while loading data to the target table. These tables are named by default as SAS_ML_RS_randnum, SAS_ML_ET_randnum, SAS_ML_UT_randnum, and SAS_ML_WT_randnum where randnum is a randomly generated number. ML_LOG= is used to override the default names used. For example, if you specify ML_LOG=MY_LOAD the log table is named MY_LOAD_RS . Errors are logged in tables MY_LOAD_ET and MY_LOAD_UT . The work table is named MY_LOAD_WT .
ML_RESTART= lets the user name the log table that MultiLoad uses for tracking checkpoint information. By default, the log table is named SAS_ML_RS_randnum where randnum is a random number. When restarting a failed MultiLoad job, you need to specify the same log table from the earlier run so that the MultiLoad job can restart correctly. Note that the same error tables and work table must also be specified upon restarting the job, using ML_ERROR1, ML_ERROR2, and ML_WORK data set options. ML_RESTART and ML_LOG are mutually exclusive and cannot be specified together.
ML_WORK= lets the user name the work table that MultiLoad uses for loading the target table. See the Teradata MultiLoad reference for more information about what is stored in this table. By default, the work table is named SAS_ML_WT_randnum where randnum is a random number. When restarting a failed MultiLoad job, you need to specify the same work table from the earlier run so that the MultiLoad job can restart correctly. Note that the same log table, acquisition error table and application error table must also be specified upon restarting the job using ML_RESTART, ML_ERROR1, and ML_ERROR2 data set options. ML_WORK and ML_LOG are mutually exclusive and cannot be specified together.
SLEEP= specifies the number of minutes that MultiLoad waits before it retries a logon operation when the maximum number of utilities are already running on the Teradata database. The default value is 6. SLEEP= functions very much like the SLEEP run-time option of the native Teradata MultiLoad utility.
TENACITY= specifies the number of hours that MultiLoad tries to log on when the maximum number of utilities are already running on the Teradata database. The default value is 4. TENACITY= functions very much like the TENACITY run-time option of the native Teradata MultiLoad utility.

Be aware that these options are disabled while you are using the SAS/ACCESS MultiLoad facility.

The DBCOMMIT= LIBNAME and data set options are disabled because DBCOMMIT= functions very differently from CHECKPOINT of the native Teradata MultiLoad utility.
The ERRLIMIT= data set option is disabled because the number of errors is not known until all records have been sent to MultiLoad. The default value of ERRLIMIT=1 is not honored.

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

Using the TPT API

TPT API Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports the TPT API for loading data. The TPT API provides a consistent interface for Fastload, MultiLoad, and Multi-Statement insert. TPT API documentation refers to Fastload as the load driver, MultiLoad as the update driver, and Multi-Statement insert as the stream driver. SAS supports all three load methods and can restart loading from checkpoints when you use the TPT API with any of them.

TPT API Setup

Here are the requirements for using the TPT API in SAS for loading SAS.

Loading data from SAS to Teradata using the TPT API requires Teradata client TTU 8.2 or later. Verify that you have applied all of the latest Teradata eFixes.
This feature is supported only on platforms for which Teradata provides the TPT API.
The native TPT API infrastructure must be present on your system. Contact Teradata if you do not already have it but want to use it with SAS.

The SAS configuration document for your system contains information about how to configure SAS to work with the TPT API. However, those steps might already have been completed as part of the post-installation configuration process for your site.

TPT API LIBNAME Options

The TPT= LIBNAME option is common to all three supported load methods. If SAS cannot use the TPT API, it reverts to using Fastload, MultiLoad, or Multi-Statement insert, depending on which method of loading was requested without generating any errors.

TPT API Data Set Options

These data set options are common to all three supported load methods:

SLEEP=
TENACITY=
TPT=
TPT_CHECKPOINT_DATA=
TPT_DATA_ENCRYPTION=
TPT_LOG_TABLE=
TPT_MAX_SESSIONS=
TPT_MIN_SESSIONS=
TPT_RESTART=
TPT_TRACE_LEVEL=
TPT_TRACE_LEVEL_INF=
TPT_TRACE_OUTPUT=

TPT API FastLoad Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports the TPT API for FastLoad, also known as the load driver, SAS/ACCESS works by interfacing with the load driver through the TPT API, which in turn uses the Teradata Fastload protocol for loading data. See your Teradata documentation for more information about the load driver.

This is the default FastLoad method. If SAS cannot find the Teradata modules that are required for the TPT API or TPT=NO, then SAS/ACCESS uses the old method of Fastload. SAS/ACCESS can restart Fastload from checkpoints when FastLoad uses the TPT API. The SAS/ACCESS FastLoad facility using the TPT API is similar to the native Teradata FastLoad utility. They share these limitations.

FastLoad can load only empty tables. It cannot append to a table that already contains data. If you try to use FastLoad when appending to a table that contains rows, the append step fails.
Data errors are logged in Teradata tables. Error recovery can be difficult if you do not TPT_CHECKPOINT_DATA= to enable restart from the last checkpoint. To find the error that corresponds to the code that is stored in the error table, see your Teradata documentation. You can restart a failed job for the last checkpoint by following the instructions in the SAS error log.
FastLoad does not load duplicate rows (those where all corresponding fields contain identical data) into a Teradata table. If your SAS data set contains duplicate rows, you can use other load methods.

Starting FastLoad with the TPT API

See the SAS configuration document for instructions on setting up the environment so that SAS can find the TPT API modules.

You can use one of these options to start FastLoad in theSAS/ACCESS interface using the TPT API:

the TPT=YES data set option in a processing step that populates an empty Teradata table
the TPT=YES LIBNAME option on the destination libref (the Teradata DBMS library where one or more tables are to be created and loaded)

FastLoad with TPT API Data Set Options

These data set options are specific to FastLoad using the TPT API:

TPT_BUFFER_SIZE=
TPT_ERROR_TABLE_1=
TPT_ERROR_TABLE_2=

TPT API MultiLoad Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports the TPT API for MultiLoad, also known as the update driver. SAS/ACCESS works by interfacing with the update driver through the TPT API. This API then uses the Teradata Multiload protocol for loading data. See your Teradata documentation for more information about the update driver.

This is the default MultiLoad method. If SAS cannot find the Teradata modules that are required for the TPT API or TPT=NO, then SAS/ACCESS uses the old method of MultiLoad. SAS/ACCESS can restart Multiload from checkpoints when MultiLoad uses the TPT API.

The SAS/ACCESS MultiLoad facility loads both empty and existing Teradata tables. SAS/ACCESS supports only insert operations and loading only one target table at time.

The SAS/ACCESS MultLoad facility using the TPT API is similar to the native Teradata MultiLoad utility. A common limitation that they share is that you must drop these items on target tables before the load:

unique secondary indexes
foreign key references
join indexes

Errors are logged to Teradata tables. Error recovery can be difficult if you do not set TPT_CHECKPOINT_DATA= to enable restart from the last checkpoint. To find the error that corresponds to the code that is stored in the error table, see your Teradata documentation. You can restart a failed job for the last checkpoint by following the instructions in the SAS error log.

Starting MultiLoad with the TPT API

See the SAS configuration document for instructions on setting up the environment so that SAS can find the TPT API modules.

You can use one of these options to start MultiLoad in the SAS/ACCESS interface using the TPT API:

the TPT=YES data set option in a processing step that populates an empty Teradata table
the TPT=YES LIBNAME option on the destination libref (the Teradata DBMS library where one or more tables are to be created and loaded)

MultiLoad with TPT API Data Set Options

These data set options are specific to MultiLoad using the TPT API:

TPT_BUFFER_SIZE=
TPT_ERROR_TABLE_1=
TPT_ERROR_TABLE_2=

TPT API Multi-Statement Insert Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports the TPT API for Multi-Statement insert, also known as the stream driver. SAS/ACCESS works by interfacing with the stream driver through the TPT API, which in turn uses the Teradata Multi-Statement insert (TPump) protocol for loading data. See your Teradata documentation for more information about the stream driver.

This is the default Multi-Statement insert method. If SAS cannot find the Teradata modules that are required for the TPT API or TPT=NO, then SAS/ACCESS uses the old method of Multi-Statement insert. SAS/ACCESS can restart Multi-Statement insert from checkpoints when Multi-Statement insert uses the TPT API.

The SAS/ACCESS Multi-Statement insert facility loads both empty and existing Teradata tables. SAS/ACCESS supports only insert operations and loading only one target table at time.

Starting Multi-Statement Insert with the TPT API

See the SAS configuration document for instructions on setting up the environment so that SAS can find the TPT API modules.

You can use one of these options to start Multi-Statement in the SAS/ACCESS interface using the TPT API:

the TPT=YES data set option in a processing step that populates an empty Teradata table
the TPT=YES LIBNAME option on the destination libref (the Teradata DBMS library where one or more tables are to be created and loaded)

Multi-Statement Insert with TPT API Data Set Options

These data set options are specific to Multi-Statement insert using the TPT API.

TPT_PACK=
TPT_PACKMAXIMUM=

Examples

This example starts the FastLoad facility.

libname fload teradata user=testuser password=testpass;
data fload.nffloat(bulkload=yes);
     do x=1 to 1000000;
        output;
     end;
run;

This next example uses FastLoad to append SAS data to an empty Teradata table and specifies the BL_LOG= option to name the error tables Append_Err1 and Append_Err2. In practice, applications typically append many rows.

/* Create the empty Teradata table. */
proc sql;
     connect to teradata as tera(user=testuser password=testpass);
     execute (create table performers 
             (userid int, salary decimal(10,2), job_desc char(50))) 
              by tera;
     execute (commit) by tera;
quit;

/* Create the SAS data to load. */
data local;
     input userid 5. salary 9. job_desc $50.;
           datalines;
             0433 35993.00 grounds keeper
             4432 44339.92 code groomer
             3288 59000.00 manager
             ;

/* Append the SAS data & name the Teradata error tables. */
libname tera teradata user=testuser password=testpass;

proc append data=local base=tera.performers
     (bulkload=yes bl_log=append_err);
run;

This example starts the MultiLoad facility.

libname trlib teradata user=testuser pw=testpass server=dbc;

/* Use MultiLoad to load a table with 2000 rows. */
data trlib.mlfloat(MultiLoad=yes);
     do x=1 to 2000;
        output;
     end;
run;

/* Append another 1000 rows. */
data work.testdata;
  do x=2001 to 3000;
     output;
  end;
run;

/* Append the SAS data to the Teradata table. */
proc append data=work.testdata base=trlib.mlfload
     (MultiLoad=yes);
run;

This example loads data using TPT FastLoad.

/* Check the SAS log for this message to verify that the TPT API was used. 
NOTE:  Teradata connection:  TPT Fastload has inserted 100 rows. 
*/
data trlib.load(TPT=YES FASTLOAD=YES);
        do x=1 to 1000;
                output;
        end;
run;

This example restarts a MultiLoad step that recorded checkpoints and failed after loading 2000 rows of data.

proc append data=trlib.load(TPT=YES MULTILOAD=YES 
    TPT_RESTART=YES TPT_CHECKPOINT_DATA=2000)
data=work.inputdata(FIRSTOBS=2001);
run;

Top of Page