Specifies whether Teradata Insert and Append operations should use the Teradata MultiLoad utility and TPT update driver.
| Valid in: | DATA and PROC steps (when creating and appending to DBMS tables using SAS/ACCESS software) |
|---|---|
| Category: | Data Set Control |
| Default: | NO |
| Data source: | Teradata |
| See: | LOGDB= LIBNAME option, Maximizing Teradata Load and Read Performance, MULTILOAD= data set option, MULTISTMT= data set option, QUERY_BAND= data set option, TPT= LIBNAME option, TPT_APPL_PHASE= data set option, TPT_BUFFER_SIZE= data set option, TPT_CHECKPOINT_DATA= data set option, TPT_DATA_ENCRYPTION= data set option, TPT_ERROR_TABLE_1= data set option, TPT_ERROR_TABLE_2= data set option, TPT_LOG_TABLE= data set option, TPT_MAX_SESSIONS= data set option, TPT_MIN_SESSIONS= data set option, TPT_RESTART= data set option, TPT_TRACE_LEVEL= data set option, TPT_TRACE_LEVEL_INF= data set option, TPT_TRACE_OUTPUT= data set option, TPT_UNICODE_PASSTHRU= LIBNAME option, TPT_WORK_TABLE= data set option |
Table of Contents
uses the Teradata Parallel Transporter (TPT) API to load data, if available.
sends inserts to Teradata tables one row at a time.
The TPT MultiLoad utility provides a bulk-loading method of loading both empty and existing Teradata tables. Unlike TPT FastLoad, TPT MultiLoad can append data to existing tables.
To determine whether threaded Reads are actually
generated, turn on SAS tracing by specifying
OPTIONS SASTRACE=",,,d"; in your program.
The TPT MultiLoad utility uses four temporary processing tables when it performs the bulk-loading 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 TPT MultiLoad facility generates names for these temporary processing tables. To specify a different name for these tables, use TPT_CHECKPOINT_DATA=, TPT_ERROR_TABLE_1=, TPT_ERROR_TABLE_2=, and TPT_WORK_TABLE= data set options, respectively.
|
Temporary Processing Table |
Default Table Name |
|---|---|
|
Restart table |
target-table_RS |
|
Acquisition error table |
target-table_ET |
|
Application error table |
target-table_UV |
|
Work table |
target-table_WT |
The upsert feature for Teradata performs a combination of updates and inserts in one step. When updating a master table with a transaction table, an UPDATE statement is first issued on the master table using a row of data from the transaction table. If no target row exists to satisfy the update, an INSERT statement adds the transaction row to the master table. To use the upsert feature, specify UPSERT=YES.
Upsert processing requires a WHERE clause that refers to the primary index of the target table that identifies the rows to update. When UPSERT=YES, Teradata builds a WHERE condition, by default, based on the primary index columns of the target table. To specify the WHERE clause for an upsert, use the UPSERT_CONDITION= data set option. To specify one or more columns that the WHERE clause applies to, use the UPSERT_WHERE= data set option.