AttachmentSize
tpt-operators.pdf36.59 KB
tpt-operators.xls22 KB

Teradata Parallel Transporter (Teradata PT) has fourteen different operators. Each behaves differently. This article provides a table to help you in selecting the right operator to use for your Teradata PT job. You can view the table as Excel .xls or PDF.

Performance

It is important to select the right operator for your job’s performance. If you want to load data as quickly as possible into an empty Teradata table with no defined secondary or join indexes, select the Load operator. It uses the high speed FastLoad protocol.

If you cannot use the Load operator, the next fastest operator is the Update operator. It uses the high performing MultiLoad protocol.

If you want to export data from a Teradata table as quickly as possible, select the Export operator. It uses the high speed FastExport protocol.

Selecting the wrong operator increases the completion time. For example, if you select the Stream or Inserter operator to load the data, the job will be slower than a job that uses the Load or Update operator. The Stream and Inserter operators use the less efficient Teradata SQL protocol. Moreover, if you select the Selector operator to export data, the job will be slower than a job that uses the Export operator. The Selector operator uses the less efficient Teradata SQL protocol.

Strategic Loading

Teradata PT supports the following strategies to load data into Teradata Database:

  • High speed bulk loading into an empty table
  • Mini-batch loading
  • Concurrent loading into multiple tables
  • Continuous loading of transactional data from JMS, MQ, or Named Pipe

High Speed Bulk Loading into an Empty Table

Use the Load operator to load data into an empty Teradata table with no defined secondary or join indexes.

Mini-Batch Loading

If the Teradata table is already populated or has secondary or join indexes, Teradata recommends mini-batch loading, which requires 2 steps:

  • Use the Load operator to load data into an empty Teradata staging table.
  • Use the DDL operator to send the INSERT/SELECT request to move the data from the staging table to the destination table.

Concurrent Loading into Multiple Tables

Use the Update operator to perform INSERT, UPDATE, and DELETE operations on multiple tables. The Update operator supports up to 5 tables.

For more than 5 tables, use the Stream operator. It supports up to 127 tables.

Continuous Loading of Transactional Data from JMS, MQ, or Named Pipe

Use the Stream operator to perform continuous INSERT, UPDATE, and DELETE operations on transactional data from a Java Message Service (JMS), message queue (MQ), or a named pipe.

Use the Data Connector operator to read transactional data from JMS, MQ, or a named pipe.

Load Slot

A load slot is used by these Teradata Database protocols: FastLoad, MultiLoad, or FastExport. Database administrators can set the maximum load slot running concurrently on the Teradata Database system. When the maximum load slot value is reached, the next job that uses a load slot will not be allowed to run until one job that uses a load slot completes.

For example, the database administrator sets the maximum load slot to 5. The first five jobs that use a load slot will be allowed to run. The sixth job will not be allowed to run until one of the first five jobs completes.

The Load, Update, and Export operators use a load slot.

  • The Load operator uses the FastLoad protocol.
  • The Update operator uses the MultiLoad protocol.
  • The Export operator uses the FastExport protocol.

The Stream, Inserter, and Selector operators do not use a load slot. They use the Teradata SQL protocol.

Selecting the wrong operator could delay the execution of the job.

Data Availability

Critical mission data needs to be loaded in near real-time. The right operator to accomplish this is the Stream operator. When you use it, no table lock is placed on the target table. A row-level hash lock is placed on the table.

Selecting the wrong operator delays the availability of the loaded data. For example, if you select the Load or Update operator, the data will not be available because a lock is placed on the target table for the duration of the loading time.

Data Sources

Teradata PT can export or access data from these data sources:

  • Teradata tables
  • ODBC-compliant databases
  • Disk files
  • Access modules

Teradata Tables

Use the Export or Selector operator to export data from one or more Teradata tables.

ODBC-Compliant Databases

Use the ODBC operator to export data from ODBC-compliant databases such as Oracle, SQL Server, and DB2.

Note: Do not use the ODBC operator to extract data from the Teradata Database. The ODBC operator is only certified to work with the Progress DataDirect ODBC drivers.

Disk Files

Use the Data Connector operator to read data from one or more disk files on the client system.

Access Modules

Access modules are software libraries that give Teradata PT access to various data sources. Teradata PT supports these access modules:

  • Teradata Access Module for JMS
  • Teradata Named Pipes Access Module
  • Teradata OLE DB Access Module
  • WebSphere® MQ Access Module for Teradata
  • User-written access modules

Data from the data sources can be:

  • Loaded into one or more Teradata tables
  • Written to a disk file
  • Passed to a user-written FastExport OUTMOD routine

The Data Connector and FastExport OUTMOD Adapter operators are the only two operators that interface with access modules.

  • Use the Data Connector operator to interface with access modules.
  • Use the FastExport OUTMOD Adapter operator to interface with access modules when your job has a FastExport OUTMOD routine.

Selecting the wrong operator prohibits Teradata PT access to the data source.

For information on access modules, see Teradata Tools and Utilities Access Module Reference.

Delimited Data

Delimited data are variable-length text records with each field or column separated by one or more delimiter characters. Delimited data are also known as VARTEXT.

Use the Data Connector operator to read or write delimited data.

Large Object Data Types

There are two kinds of large object data type:

  • Character large object (CLOB)
  • Binary large object (BLOB)

Three operators support the CLOB and BLOB data types.

  • The Inserter operator can insert CLOB and BLOB data types into a Teradata table
  • The Selector operator can export CLOB and BLOB data types from a Teradata table
  • The Data Connector operator can read/write CLOB and BLOB data types from/to a file.

Selecting the wrong operator to process the CLOB or BLOB data type terminates the job.

SQL Statement

Teradata PT supports the following types of SQL statements:

  • Data Manipulation Language (DML): Insert, Update, Delete, Upsert, Merge, and Select
  • Data Control Language (DCL): Give, Grant, and Revoke
  • Data Definition Language (DDL): Create, Drop, Alter, Modify, Delete Database, Delete User, and Rename

DML Statements

For INSERT statement, use the Load, Update, Stream, or Inserter operator. Using these operators depends on the job performance you require, data availability, the availability of a load slot, and the presence of large object data types.

For UPDATE statement, use the Update or Stream operator. Using these operators depends on the job performance you require, data availability, and the availability of a load slot.

For DELETE statement, use the Update or Stream operator. Using these operators depends on the job performance you require, data availability, and the availability of a load slot.

For MultiLoad’s DELETE task, use the Update operator.

For UPSERT statement, use the Update or Stream operator. Using these operators depends on the job performance you require, data availability, and the availability of a load slot.

For MERGE statement, use the Stream operator. It is the only operator that supports the merge statement.

For SELECT statement from one or more Teradata tables, use the Export or Selector operator. Using these operators depends on the job performance you require, the availability of a load slot, and the presence of large object data types.

For SELECT statement from ODBC-compliant databases, use the ODBC operator. It is the only operator that can export data from ODBC-compliant databases.

DCL Statements

For DCL statements, use the DDL operator. It is the only operator that can send DCL statements to the Teradata Database.

DDL Statements

For DDL statements, use the DDL operator. It is the only operator that can send DDL statements to the Teradata Database.

INMOD and OUTMOD

The acronym for INMOD is input modification. The acronym for OUTMOD is output modification.

An INMOD library is a user-written routine that processes input data records before they are sent to the Teradata Database.

An OUTMOD library is a user-written routine that processes output data records from Teradata Database before they are saved to a disk file on the client system.

  • Use the FastLoad INMOD Adapter operator to interface with a FastLoad INMOD routine.
  • Use the MultiLoad INMOD Adapter operator to interface with a MultiLoad or TPump INMOD routine.
  • Use the FastExport OUTMOD Adapter operator to interface with a FastExport OUTMOD routine.

OS Commands

Use the OS Command operator to send commands to the operating system on the client system.

Schema Checker

Use the Schema Mapping operator to verify the schema definition correctly describes the input data.

When input data does not conform to the schema definition, the job can terminate abnormally. The solution to abnormal termination is to verify that the schema definition correctly describes the input data. The Schema Mapping operator does this, logging input data that does not conform to the schema definition to the Schema Mapping operator’s private log file.

Once you’ve run the Schema Mapping operator, inspect the private log file and correct input data that does not conform to the job’s schema definition. Then you can load the data into the Teradata Database.

Note: The Schema Mapping operator is available starting in the Teradata PT 14.00.00.00 version.

For information on the Teradata PT operators, see Teradata Parallel Transporter Reference.

For information on Teradata PT examples, see Teradata Parallel Transporter User Guide.

Discussion
VasuKillada 12 comments Joined 10/11
27 Apr 2012

Tony - The description and the flow of this article is simply awesome. I enjoyed it and also the example was really simple and clear. Can you give me an example for continuous loading of transactional data from JMS and using stream to do DML operations. I've a situation exactly like above. Can you also give me an example where Java services read queue tables for pop(select and consume). Thanks in advance.

Thanks,
Vasu

You must sign in to leave a comment.