Introduction to the Teradata Parallel Transporter Operators

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Introduction to the Teradata Parallel Transporter Operators

The Teradata Parallel Transporter (Teradata PT) operators play a vital role in high-speed data extraction and loading geared towards the Teradata Database. Besides interfacing with the Teradata Database, some of the Teradata PT operators provide access to external sources such as files, ODBC-compliant DBMS, and message-oriented middleware.

There are four different types of operators: 1) producer operators, 2) consumer operators, 3) standalone operators, and 4) user-written custom operators.

Producer Operators

Producer operators read data from a source and send the data to the Teradata PT data streams. Data streams are in-memory buffers that allow the data to be passed from a producer operator to a consumer operator without landing the data to disk. Teradata PT provides the following producer operators:

Export operator: Utilizes multiple sessions of the Teradata FastExport protocol to extract large volumes of data at fast speeds from one or more tables on the Teradata Database.

SQL Selector operator: Uses a single Teradata SQL session to extract low volumes of data from a table on the Teradata Database. The SQL Selector operator has two features not available in the Export operator. The SQL Selector operator can extract data in “field mode” (i.e. columns in VARCHAR format) and pass the data to the Data Connector operator in order to have the data written in delimited format to an external file. The SQL Selector operator can extract LOB data.

Data Connector operator: As a producer operator, the Data Connector operator reads data in parallel from multiple sources such as files, named pipes, access modules, and message queue systems.

FastLoad INMOD Adapter operator: Reads data from any Teradata FastLoad INMOD routines. The INMOD routines are user-written. The INMOD routines preprocess the data before the data are passed to the FastLoad INMOD Adapter operator.

MultiLoad INMOD Adapter operator: Reads data from a Teradata MultiLoad or TPump INMOD routine. The INMOD routine is written by the user. The INMOD routines preprocess the data before the data are passed to the MultiLoad INMOD Adapter operator.

ODBC operator: Reads data from many ODBC-compliant DBMS, such as Oracle, SQL Server, and DB2.

Consumer Operators

Consumer operators “consume” data from the Teradata PT data streams and send data to the Teradata Database or files. Teradata PT provides the following consumer operators:

Load operator: Utilizes multiple sessions of the Teradata FastLoad protocol to insert large volumes of data at fast speeds into an empty table on the Teradata Database.

Update operator: Uses multiple sessions of the Teradata MultiLoad protocol to perform Inserts, Updates, Deletes, and Upserts into empty or populated tables (up to 5 tables concurrently) on the Teradata Database.

Stream operator: Uses multiple Teradata SQL sessions to perform Inserts, Updates, Deletes, and Upserts into empty or populated tables (up to 127 tables concurrently) on the Teradata Database without locking the tables. Locking occurs at the row level.

SQL Inserter operator: Uses a single Teradata SQL session to insert low volumes of data into an empty or populated table on the Teradata Database. The SQL Inserter operator has a feature not available in the Load, Update, or Stream operator. The SQL Inserter operator can load LOB data.

Data Connector operator: As a consumer operator, the Data Connector operator writes data to files or access modules.

FastExport OUTMOD Adapter operator: Writes data to a Teradata FastExport OUTMOD routine. The OUTMOD routine is written by the user. The OUTMOD routines post process the data.

Standalone Operators

Standalone operators perform unique functions that do not involve sending data to, or receiving data from, the Teradata PT data streams. Teradata PT provides the following standalone operators:

DDL operator: Sends SQL requests to the Teradata Database to perform setup operations prior to running load or export tasks. For example, before loading the data using the Load operator use the DDL operator to send the DROP TABLE and CREATE TABLE requests to ensure the target table is empty.

OS Command operator: Sends commands to the client OS. Commands can be operating system commands, executables, or shell scripts.

Load operator: As a standalone operator, the Load operator sends a request to the Teradata Database to apply the data on the target table. For example, when there is no more data in a staged loading, use the Load operator as a standalone operator to apply the data already in the target table.

Update operator: As a standalone operator, the Update operator performs the Delete task on a single table on the Teradata Database. The Delete task performs faster than using a single DELETE SQL statement when a large percentage of rows in a table need to be deleted, such as deleting all rows with a transaction date prior to a certain date.

Custom Operators

Custom operators are user-developed operators that must follow the requirements of the Teradata PT operator interface. Custom operators must be written in C or C++ programming language. Teradata PT provides a sample custom operator when you install Teradata PT. 

48 REPLIES
Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

I have a big file(50GB) sitting in UNIX which is generated from TPT fast export. The file contains  sales details of the individuals. From this file we are generating separate files for each distinct individual using UNIX commands. For Example 

Individual ID  SalesAmt

1 50

2 60

1 70

3 80

We are generating 3 files here for 3 distinct individuals.

can we do this while we generate the file itself using TPT fast export. I heard about OUTMOD routine option but don't know how to use this. 

Please help. 

Thanks in advance.

Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

You can use the TPT FastExport OUTMOD Adapter operator with your user-written OUTMOD library.

The TPT FastExport OUTMOD Adapter operator can pass the data to your user-written OUTMOD library.

Your user-written OUTMOD library can generate separate files for each distinct individual.

See the TPT Reference manual for more information about the TPT FastExport OUTMOD Adapter operator.

See the TPT sample user guide #12 for a sample TPT script that uses the TPT FastExport OUTMOD

Adapter operator. The TPT sample user guide is available after you have installed the TPT

software.

See the Teradata FastExport manual for information on how to create and compile your

user-written OUTMOD library.

Your user-written OUTMOD library should be compatible with TPT.

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Thanks a lot Tony. Could you have all these documents available online, that would be a great help.

Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Thanks a lot. Its a great help.

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Hi Tony,

I am trying to develop an outmod c script, and I am getting below error. 

FILE_WRITER: TPT19501 Can not load OUTMOD module.  'dlopen' failed, reason: ''

EXPORT_OPERATOR: connecting sessions

EXPORT_OPERATOR: disconnecting sessions

EXPORT_OPERATOR: Total processor time used = '3.03 Second(s)'

Could you please suggest why I am getting this error?

Below is the c program.

#include <stdio.h>

#include <string.h>

#include <errno.h>

#include <stdlib.h>

#include<sys/stat.h>

struct  in_struct

{

   char  FIELD1                         [2048];

};

struct  out_rec

{

   char  FIELD1                         [2048];

};

FILE *file1_fp;

#define EQUAL                           0

#define NUM_OF_RCDS_WRITTEN             1

#define BAD_OPEN                        50

#define BAD_WRITE                       52

#define FILE_CTR                        1

int _dynamn(    entrytype,

                stmtno,

                resplen,

                resprec,

                outlen,

                outrec

            )

int                      *entrytype;

int                      *stmtno;

int                      *resplen;

struct in_struct         *resprec;

int                      *outlen;

char                     *outrec;

{

        struct out_rec    out_struct ;

        struct out_rec   *out_struct_ptr = &out_struct;

        char file_name    [150];      

        static int Record_Size = 0;

        static int File_Size = 0;

        static int Num_Files = 0;

        static unsigned long int tot_record_ctr = 0;

switch(*entrytype)

 {

     case 1:

     case 6:

        strcpy(file_name,getenv( "outmod_file_name" ));

        if ((file1_fp=fopen(file_name,"w")) == NULL)

        {

                fprintf(stderr,"%s %d:Bad Open on file <%s>,errno=%d\n",

                                        __FILE__,__LINE__,file_name,errno);

                fflush(stderr);

                exit(BAD_OPEN);

        }

        break;

     case 2:

        fclose(file1_fp);

        printf("\nThe extract is done \n");

        printf("\nTotal outmod_records Written: %d \n",tot_record_ctr);

        break;

     case 3:

        Record_Size=*resplen;

        File_Size=Record_Size+1;

        *resplen = 0;

        memcpy(out_struct_ptr->FIELD1,resprec->FIELD1, Record_Size);

        out_struct_ptr->FIELD1[Record_Size] = '\n';

        if ( fwrite(out_struct_ptr,File_Size,1,file1_fp) != NUM_OF_RCDS_WRITTEN )

        {

                fprintf(stderr,"%s %d:Bad Write on file <%s>,errno=%d\n",

                         __FILE__,__LINE__,file_name,errno);

                perror(file_name);

                fflush(stderr);

                exit(BAD_WRITE);

        }

        tot_record_ctr++;

        break;

     case 4:

        break;

     case 5:

        if ((file1_fp=freopen(file_name,"w",file1_fp)) == NULL)

        {

                fprintf(stderr,"%s %d:Bad ReOpen on file <%s>,errno=%d\n",

                        __FILE__,__LINE__,file_name,errno);

                fflush(stderr);

                exit(BAD_OPEN);

        }

        break;

}

return(0);

}

Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

Can you send me the TPT job script?

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Below is the TPT script.

DEFINE JOB J_TABLE_NAME

(

    DEFINE SCHEMA S_TABLE_NAME

    DESCRIPTION 'LOADING for TABLE_NAME'

        (

        COL1 VARCHAR(30),

        COL2 VARCHAR(10)

        );

DEFINE OPERATOR EXPORT_OPERATOR

     TYPE EXPORT

     SCHEMA S_TABLE_NAME

     ATTRIBUTES

     (

        VARCHAR TraceLevel     ='NONE',

        VARCHAR TdpId          =@TdpId,

        VARCHAR UserName       =@UserName,

        VARCHAR UserPassword   =@PassWord,

        VARCHAR PrivateLogName ='TABLE_NAME_export_operator',

        VARCHAR SelectStmt     ='SELECT

                        Col1,

                        Col2

                FROM  DATABASENAME.TABLE_NAME

                '

                                '

     );

DEFINE OPERATOR FILE_WRITER

DESCRIPTION 'TPT DATA CONNECTOR OPERATOR'

TYPE FASTEXPORT OUTMOD

SCHEMA S_TABLE_NAME

     ATTRIBUTES

     (

         VARCHAR TraceLevel     ='NONE',

         VARCHAR PrivateLogName ='TABLE_NAME_file_writer',

         VARCHAR DirectoryPath  ='$HOME/TPT/',

         VARCHAR FileName       ='TABLE_NAME.lst',

         VARCHAR Format         ='Delimited',

         VARCHAR OpenMode       ='Write',

         VARCHAR TextDelimiter  ='~_~',

         VARCHAR OutmodName='$HOME/TPT/testC.c'

     );

APPLY TO OPERATOR (FILE_WRITER)

select

        COL1,

        COL2

from OPERATOR (EXPORT_OPERATOR);

);

Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

The OutmodName attribute refers to the name of your FastExport OUTMOD library name, not the OUTMOD source file.