Consumer Operators - Parallel Transporter

Teradata® Parallel Transporter User Guide - 17.20
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Parallel Transporter
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-08-25
dita:mapPath
uzp1645128359760.ditamap
dita:ditavalPath
tvt1507315030722.ditaval
dita:id
B035-2445
Product Category
Teradata Tools and Utilities

The Teradata PT consumer operators in this section read data from the data stream and write it to the database.

The Teradata PT job script invokes a consumer operator using an APPLY statement. For further information on using APPLY to specify a consumer operator, see Coding the APPLY Statement and the section on APPLY in Teradata® Parallel Transporter Reference, B035-2436.

The following table briefly describes and compares the function of each Teradata PT operator that can be used as a consumer when loading data into the database:

Operator Description
Load Operator Inserts data at high speed into a single empty database table. Function is similar to the standalone FastLoad utility.
Features:
  • Best used for the initial data loads into database tables.
Limitations:
  • Does not support UPDATE, SELECT, or DELETE operations.
  • The target table must be empty, with no defined secondary indexes.
  • Multiple parallel instances of the Load operator can be used in a job, but they must all insert data into the same table.

For more information, see Teradata® Parallel Transporter Reference, B035-2436.
SQL Inserter Operator Uses one or more SQL sessions to insert data into the database, in either an empty or populated table.
Features:
  • Protects data integrity. If an error is encountered during the INSERT operation, SQL Operator backs out all rows it has inserted for the job since the last checkpoint.
  • Use of multiple instances of SQL Inserter allows parallel loading of LOBs, XML, and JSON.
Limitations:
  • Is slower than other operators capable of writing to the database.
  • Will terminate the job if an attempted insert would duplicate an existing row in the target table.
  • If multiple sessions are used with the SQL Inserter operator and the data has duplicate primary index values, this could result in a potential database deadlock 2631 error. To avoid this error, do not use multiple sessions with the TPT SQL Inserter operator.
  • If multiple sessions are used and the data accesses the same row-level lock on the database, the job could result in a hang. To avoid a hang, do not use multiple sessions with the TPT SQL Inserter operator. Use only one session.

For more information, see Teradata® Parallel Transporter Reference, B035-2436.
Stream Operator Performs high-speed, low-steady volume SQL DML transactions, INSERT, UPDATE, DELETE, or UPSERT on database tables. Function is similar to the standalone Teradata TPump utility.

Stream operator or Update operator can be used for many similar tasks. For comparison of function to help you choose the best operator for the job, see Comparing Update and Stream Operators.

For more information, see Teradata® Parallel Transporter Reference, B035-2436.
Update Operator Performs high-speed, high-volume SQL DML transactions, INSERT, UPDATE, DELETE, or UPSERT on database tables. Function is similar to the standalone Teradata MultiLoad utility.

Update operator or Stream operator can be used for many similar tasks. For comparison of function to help you choose the best operator for the job, see Comparing Update and Stream Operators.

For more information, see Teradata® Parallel Transporter Reference, B035-2436.
Consumer operators have a limit on the number of tables they can load simultaneously:
Teradata PT Operator Maximum Target Tables
Load 1
Update 5
Stream 127
SQL Inserter 1