Maximizing Teradata Load and Read Performance

Overview

SAS/ACCESS Interface to Teradata supports the TPT API for loading and reading data. The TPT API provides a consistent interface for FastLoad, FastExport, MultiLoad, and Multi-Statement insert. TPT API documentation refers to FastLoad as the Load operator, FastExport as the Export operator, MultiLoad as the Update operator, and Multi-Statement insert as the Stream operator. The ability to restart is supported by FastLoad, MultiLoad, and Multi-Statement insert.

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT FastExport, FastLoad, and MultiLoad facilities. Documentation for these legacy methods remains for users who have not upgraded to SAS 9.4M9.

Using the TPT API

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 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.
  • These Teradata privileges are needed to load a Teradata table using the TPT API. For more information, see the Teradata TPT API documentation.
    • CREATE TABLE
    • DROP TABLE
    • CREATE MACRO (Multi-Statement Stream operator)
    • DROP MACRO (Multi-Statement Stream operator)
    • INSERT
    • DELETE
    • SELECT

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

These LIBNAME options are common to all three supported load methods:

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports FastLoad and MultiLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

For releases prior to SAS 9.4M9, 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:

You can use the DBCOMMIT= LIBNAME option and the CHECKPOINT= data set option to specify checkpoint frequency with TPT FastLoad, TPT MultiLoad, and TPT Multi-Statement insert. These options are not relevant with TPT FastExport. SAS sets TPT_MAX_SESSIONS to 4. For releases prior to SAS 9.4M9, these options are disabled for the non-TPT MultiLoad interface.

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT FastExport, FastLoad, and MultiLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

Processing Large Response Rows

SAS/ACCESS automatically verifies that your Teradata environment is configured to process response row sizes up to 1MB. Typically, the required versions of the Teradata client libraries, Teradata TTU, and Teradata Vantage SQL Engine all support row sizes of 1MB.

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports the legacy utilities FastExport, FastLoad, or MultiLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

The Teradata legacy utilities—FastLoad, MultiLoad, MultiStatement, and FastExport—all support row sizes up to 1MB as long as the LIBNAME option TPT= is set to YES (the default).

If you use the default values for TPT-related options, then the maximum row size is 1MB. You can restrict the maximum row size to 64K by setting the TD_1MB_ROW environment variable to OFF. For more information, see TD_1MB_ROW Environment Variable in SAS Global Statements: Reference.

TPT API FastLoad Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports the TPT API for FastLoad, also known as the Load operator. SAS/ACCESS works by interfacing with the Load operator 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 operator.

SAS/ACCESS uses the TPT API to implement the TPT FastLoad utility. SAS/ACCESS can restart TPT FastLoad from checkpoints. The TPT FastLoad utility has 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 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.
  • 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 TPT FastLoad

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

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

  • Set the FASTLOAD= or BULKLOAD= data set options to YES in a processing step that populates an empty Teradata table.
  • Set the FASTLOAD= or BULKLOAD= LIBNAME options to YES 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 MultiLoad Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports the TPT API for MultiLoad, also known as the Update operator. SAS/ACCESS works by interfacing with the Update operator 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 operator.

SAS/ACCESS can restart MultiLoad from checkpoints when it 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 a time.

To use the TPT MultiLoad facility, you must specify these items on target tables before the load operation:

  • 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 these options to start MultiLoad in the SAS/ACCESS interface using the TPT API:

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

MultiLoad with TPT API LIBNAME Options

These LIBNAME options are specific to MultiLoad using the TPT API:

MultiLoad with TPT API Data Set Options

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

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 operator. SAS/ACCESS works by interfacing with the Stream operator 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 operator.

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.

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 on the SAS error log.

Starting TPT Multi-Statement Insert

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

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

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

These LIBNAME options also work with the Multi-Statement Insert:

Multi-Statement Insert with TPT API Data Set Options

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

Legacy Load and Read Utilities

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT FastExport, FastLoad, and MultiLoad facilities. Documentation remains for users who have not upgraded to SAS 9.4M9.

If the TPT API is not available, SAS/ACCESS Interface to Teradata provides the following facilities. Although these are legacy facilities, they do improve performance when loading data. These correspond to native Teradata utilities.

For more information, see the following sections.

Using FastLoad

FastLoad Supported Features and Restrictions

SAS/ACCESS Interface to Teradata supports FastLoad, a native Teradata bulk-load utility that greatly accelerates inserting data into empty Teradata tables. For general information about using FastLoad and error recovery, see the Teradata FastLoad documentation.

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT FastExport, FastLoad, and MultiLoad facilities. Documentation remains for users who have not upgraded to SAS 9.4M9.
  • 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.
  • 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.
  • If you create a table with special characters that require quotation marks, you must specify a name in BL_LOG= that does not require quotation marks.

Starting FastLoad

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT FastLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

If you do not specify FastLoad, then your Teradata tables are loaded normally. To start non-TPT FastLoad in the SAS/ACCESS interface, you can use one of these methods:

  • Set the BULKLOAD= data set option to YES and the TPT= data set option to NO in a processing step that populates an empty Teradata table.
  • Set the BULKLOAD= LIBNAME option to YES and the TPT= LIBNAME option to NO on the destination libref (the Teradata DBMS library where one or more intended tables are to be created and loaded).
  • Use the FASTLOAD= alias for either of these methods, while also setting TPT=NO.

FastLoad Data Set Options

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT FastLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

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

  • BL_LOG= specifies the names of error tables that are created when you use the non-TPT 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.
    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 specify DBCOMMIT= and BULKLOAD=YES. The Teradata alias for this option is CHECKPOINT=.

For details, see About the Data Set Options for Relational Databases.

Using MultiLoad

MultiLoad Supported Features and Restrictions

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT MultiLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

SAS/ACCESS Interface to Teradata supports a bulk-load capability called MultiLoad that 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.

Unlike FastLoad, which loads only 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 non-TPT 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 and Upsert operations are supported.

Because the non-TPT MultiLoad facility is similar to the native Teradata MultiLoad utility, they share a limitation: You must specify these items on the target tables before the load:

  • unique secondary indexes
  • foreign key references
  • join indexes

Both the Teradata MultiLoad utility and the non-TPT 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

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT MultiLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

Here are the requirements for using the non-TPT 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.

If not already done during post-installation configuration, see the SAS configuration documentation for your system for information about how to configure SAS to work with MultiLoad.

MultiLoad Data Set Options

Note: Starting in SAS 9.4M9, SAS/ACCESS no longer supports non-TPT MultiLoad. Documentation remains for users who have not upgraded to SAS 9.4M9.

Call the non-TPT 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 Overview.

  • MBUFSIZE=
  • ML_CHECKPOINT=
  • ML_ERROR1= allows 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 when 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= allows 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= allows 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.
  • UPSERT= when MultiLoad=YES, specifies that a Teradata Upsert operation should take place. The default value is NO.
  • UPSERT_WHERE= specifies which columns in the master table are to be used when generating a Where condition for a MultiLoad Upsert. The default value is none.
  • UPSERT_CONDITION= specifies additional conditions to be appended to the UPSERT_WHERE= option. The default value is none.

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.
  • A rollback does not take place to the last checkpoint on reaching ERRLIMIT= as the rows without errors have already been sent to Teradata.

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

Examples

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 TPT MultiLoad step that records checkpoints and fails 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;
Last updated: February 3, 2026