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

Re: Introduction to the Teradata Parallel Transporter Operators

The TPT log shows the job is in the restart mode.

Can you re-run the TPT job from the beginning?

Step 1: Delete the TPT job's checkpoint file(s) in the TPT "checkpoint" directory.

Note: The TPT "checkpoint" directory is located in the $TWB_ROOT directory.

The default value for $TWB_ROOT is /opt/teradata/client/13.10/tbuild.

Step 2: Run the TPT job.

Step 3: Send me the entire TPT FastExport OUTMOD Adapter operator's private log.

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

I removed thr check point file and rerun the TPT again.

pls find below the log.

Private log TWB_STATUS:

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 INITIATE-Started     02:15:15     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            INSERT_1[0001]       Success              FILE_WRITER             1    1 INITIATE-Started     02:15:15     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            INSERT_1[0001]       Success              FILE_WRITER             1    1 INITIATE-Ended       02:15:38    23.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 INITIATE-Ended       02:15:38    23.0000     2.1900      65000          0                0                0     0     0 N Y

MAIN_STEP            INSERT_1[0001]       Success              FILE_WRITER             1    1 EXECUTE-Started      02:15:38     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 EXECUTE-Started      02:15:38     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            INSERT_1[0001]       Success              FILE_WRITER             1    1 CHECKPOINT-Started   02:15:38     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 CHECKPOINT-Started   02:15:38     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            INSERT_1[0001]       Success              FILE_WRITER             1    1 CHECKPOINT-Ended     02:15:39     1.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 CHECKPOINT-Ended     02:15:39     1.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 CHECKPOINT-Started   02:15:39     1.0000     0.0200      65000          1                0                2     0     0 Y Y

MAIN_STEP            INSERT_1[0001]       Success              FILE_WRITER             1    1 TERMINATE-Started    02:15:39     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            INSERT_1[0001]       Success              FILE_WRITER             1    1 TERMINATE-Ended      02:15:39     0.0000     0.0000      65000          0                0                0     0     0 N Y

MAIN_STEP            SELECT_2[0001]       System Error         EXPORT_OPERATOR         1    1 CHECKPOINT-Ended     02:15:40     2.0000     0.0200      65000          1                0                2     0     0 Y Y

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 TERMINATE-Started    02:15:40     0.0000     0.0000      65000          0                0                0     0     0 Y Y

MAIN_STEP            SELECT_2[0001]       Success              EXPORT_OPERATOR         1    1 TERMINATE-Ended      02:16:00    20.0000     0.0300      65000          0                0                0     0     0 Y Y

Private log TWB_EVENTS:

JOBID-77,17,0,OperatorEnter,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0

JOBID-77,116,5,UtilityName,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,TPT Export Operator,2,0

JOBID-77,8,5,ExportVersionId,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,13.10.00.05,2,0

JOBID-77,115,1,UtilityId,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,3,2,0

JOBID-77,131,5,ExportTdpId,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,XXXXX.com,2,0

JOBID-77,9,5,ExportUserName,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,c1133700,2,0

JOBID-77,127,5,ExportDbase,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,C1133700,2,0

JOBID-77,86,0,ExportBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0

JOBID-77,88,5,ExportReqSubmitBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,                                  select col1,cast(col2 as varchar(1)) from database.TEST_OUTMOD     ,2,0

JOBID-77,89,0,ExportReqSubmitEnd,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0

JOBID-77,99,1,ExportStmtCount,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,1,2,0

JOBID-77,100,1,ExportBlockCount,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,2,2,0

JOBID-77,90,0,ExportReqFetchBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0

JOBID-77,92,0,ExportStmtFetchBegin,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0

JOBID-77,94,1,ExportStmtNumber,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,1,2,0

JOBID-77,95,1,ExportStmtBlockCount,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,2,2,0

JOBID-77,93,0,ExportStmtFetchEnd,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0

JOBID-77,96,1,ExportStmtRowsFetched,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,2,2,0

JOBID-77,91,0,ExportReqFetchEnd,MAIN_STEP,EXPORT_OPERATOR,1,2013-05-09,,2,0

Private log TABLE_NAME_file_writer-1:

     ==========================================================================

     =                                                                        =

     =                     TERADATA PARALLEL TRANSPORTER                      =

     =                                                                        =

     =        FASTEXPORT OUTMOD ADAPTER OPERATOR VERSION  13.10.00.05         =

     =                                                                        =

     =     FastExport OUTMOD Adapter UTILITY LIBRARY VERSION 13.10.00.17      =

     =                                                                        =

     =    COPYRIGHT 2001-2010, Teradata Corporation.  ALL RIGHTS RESERVED.    =

     =                                                                        =

     ==========================================================================

     Operator name: 'FILE_WRITER' instance 1 of 1 [Consumer]

**** 02:15:15 Processing starting at: Thu May  9 02:15:15 2013

     ==========================================================================

     =                                                                        =

     =                    Operator module static specifics                    =

     =                                                                        =

     =                 Compiled for platform: '32-bit LINUX'                  =

     =         Operator module name:'feoutop', version:'13.10.00.05C'         =

     =                                                                        =

     = pmdcomt_HeaderVersion: 'Common 13.10.00.10' - packing 'pack (push, 1)' =

     = pmddamt_HeaderVersion: 'Common 13.10.00.01' - packing 'pack (push, 1)' =

     =                                                                        =

     ==========================================================================

     ==========================================================================

     =                                                                        =

     ==========================================================================

     =                                                                        =

     =                   > General attribute Definitions <                    =

     =                                                                        =

     =                           TraceLevel: 'ALL'                            =

     =                      EndianFlip: 'NO (defaulted)'                      =

     =                    IndicatorMode: 'NO (defaulted)'                     =

     =                        SYSTEM_CharSetId: 'UTF8'                        =

     =                                                                        =

     ==========================================================================

     LITTLE ENDIAN platform

     Operator module name:'feoutop', source version:'13.10.00.02'

     Schema column count=2

     ColumnHandleList    Type Ind  Length  Offset  VAR  NC  D M

            160517080       7   1      30       0    1   1  1

            160539536       7   1       3      30    1   1  1

     SG_VARtypesFlag=1, NumIndBytes=1, TotalFixedLength=34

     loading OUTMOD='$HOME/TPT/testOUTMOD'

     Resolving entrypoint '_dynamn'

     Done with loading the outmod.

     omodaddr: f769554c

     OUTMOD returned, req=1, OutLen=0, InLen=0

     entry

     OUTMOD returned, req=4, OutLen=0, InLen=0

     entry

     SYSTEM_BUFFERMODE: 'no'

     Data from PX_GetRow: SH=160508756, ColCount=2

     Schema: col=0, Ind=0, Len=1, off=3

     Schema: col=1, Ind=0, Len=1, off=6

  Hexadecimal formatted display from address F6878DF6 for 7 bytes.

   0000  00 01 00 42.01 00 32                             *...B..2         *

     !ERROR! API not initialized

     !ERROR! on record 1

     pmWrite rc=26

     'Access Module API not initialized (call pmInit)'

     Setting exit code = 12.

     entry

     OUTMOD returned, req=2, OutLen=0, InLen=0

     Closing logs

     ===================================================================

     =                                                                 =

     =                      Module Identification                      =

     =                                                                 =

     ===================================================================

     Export Operator for Linux release 2.6.32-300.39.2.el5uek on XXXX.com

     ExportMain : 13.10.00.11

     ExportCLI  : 13.10.00.09

     ExportUtil : 13.10.00.02

     PcomCLI    : 13.10.00.19

     PcomMBCS   : 13.00.00.02

     PcomMsgs   : 13.01.00.07

     PcomNtfy   : 13.00.00.03

     PcomPx     : 13.10.00.18

     PcomUtil   : 13.10.00.09

     PXICU      : 13.10.00.06

     TDICU      : 13.10.00.02

     CLIv2      : 13.10.00.02

     ===================================================================

     =                                                                 =

     =                      Attribute Definitions                      =

     =                                                                 =

     ===================================================================

**** 02:15:15 Options in effect for this job:

              OperatorType:  Producer

              Instances:     1

              Character set: 'UTF8'

              Checkpoint:    No checkpoint in effect

              Notify:        Not enabled

              Tenacity:      4 hour limit to successfully connect

              Sleep:         6 minute(s) between connect retries

              Date format:   INTEGERDATE

              Blocksize:     Maximum allowable

              OutLimit:      No limit in effect

     ===================================================================

     =                                                                 =

     =                     Column/Field Definition                     =

     =                                                                 =

     ===================================================================

     Column Name                    Offset Length Type

     ============================== ====== ====== ========================

     COL1                                0     30 VARCHAR

     COL2                               32      3 VARCHAR

     ============================== ====== ====== ========================

     INDICATOR BYTES NEEDED: 1

     EXPECTED RECORD LENGTH: 38

Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

You are using the TPT 13.10.00.05 version.

Can you install the newer TPT 13.10.00.14 version?

Try the TPT job using the newer TPT 13.10.00.14 version.

Make sure to remove the TPT checkpoint file(s) before re-running the TPT job.

Send me the TPT FastExport OUTMOD Adapter operator's private log using the newer TPT version.

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Hello Tony, 

I am trying to use a where clause inside TPT export selector. I am getting syntax error for the following definition due to the quotes present in IN LIST of where clause. I cannot avoid the quotes for the list as its a varchar filed. Is there any way out to solve this, may be using some escape character?

Thanks In Advance.

/*** Export Operator Definition ***/

   DEFINE OPERATOR EXPORT_OPERATOR

    DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

    TYPE EXPORT

    SCHEMA SCHEMA_NAME

    ATTRIBUTES

    (

      VARCHAR PrivateLogName = 'exportoper_privatelog',

       INTEGER MaxSessions    =  8,

       INTEGER MinSessions,

       VARCHAR TdpId          = 'XXXXX',

       VARCHAR UserName       = 'XXXXXX',

       VARCHAR UserPassword   = 'XXXXXX',

       VARCHAR SelectStmt     = 'SELECT

COLUMN1,

COLUMN2,

COLUMN3,

COLUMN4,

COLUMN5

from TABLE where COLUMN3 IN ('000362464WH', '20550K01A');'

Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

Shyam,

You can escape the single quote with two consecutive single quotes, like this:

IN (''000362464WH'', ''20550K01A'');

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Hi Tony,

Can you please share sample TPT script which loads data from Oracle to Teradata with ODBC operator? I am trying the same using the below script but its failing due to ODBC issues.

DEFINE JOB COPY_ORACLE_TO_TERADATA

DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'

(

        DEFINE  SCHEMA TABLE_STRUCTURE

        DESCRIPTION 'THE STRUCTURE OF THE TABLE'

        (

                COL_1  DECIMAL(15,0),

                COL_2  VARCHAR(30)

        );

        DEFINE  SCHEMA TABLE_STRUCTURE_TGT

        DESCRIPTION 'THE STRUCTURE OF THE TARGET TABLE'

        (

                COL_1  DECIMAL(15,0),

                COL_2  VARCHAR(30)

        );

        /*** Export Operator Definition ***/

        DEFINE OPERATOR EXPORT_OPERATOR

        DESCRIPTION 'TPT ODBC Operator'

        TYPE ODBC

        SCHEMA TABLE_STRUCTURE

        ATTRIBUTES

        (

                VARCHAR TraceLevel='ALL',

                VARCHAR  PrivateLogName   = @ODBCPrivateLogName,

                VARCHAR  DSNname             = @DSNname,

                VARCHAR  UserName             = @ODBCUserName,

                VARCHAR  UserPassword       = @ODBCPassword,

                VARCHAR  SelectStmt             = @SelectStmt

        );

        /*** Load Operator Definition ***/

        DEFINE OPERATOR LOAD_OPERATOR

        DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

        TYPE LOAD

        SCHEMA TABLE_STRUCTURE_TGT

        ATTRIBUTES

        (

                VARCHAR PrivateLogName = 'loadoper_privatelog',

                INTEGER MaxSessions    = 16,

                INTEGER MinSessions,

                VARCHAR TargetTable    = @TargetTable,

                VARCHAR TdpId          = @TargetTdpId,

                VARCHAR UserName       = @TargetUserName,

                VARCHAR UserPassword   = @TargetUserPassword,

                VARCHAR ErrorTable1    = @TargetDataBase || '.LOADOPER_ERRTABLE1',

                VARCHAR ErrorTable2    = @TargetDataBase || '.LOADOPER_ERRTABLE2',

                VARCHAR LogTable       = @TargetDataBase || '.LOADOPER_LOGTABLE'

        );

        /*** Apply Statement ***/

        APPLY

        (

        'INSERT INTO ' || @TargetTable || '

                (

                :COL_1,

                :COL_2

                );'

        )

        TO OPERATOR (LOAD_OPERATOR [1])            SELECT * FROM OPERATOR (EXPORT_OPERATOR);

);

Also can you share the format of Connection string and DSN name which we should follow in variable file?

Thanks

Highlighted
Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

VetriSlevan,

What is the failure message?

Your script should work if your ODBC configuration was setup properly. You need to make sure the ODBC configurations is setup properly before you run the TPT job script. You may need to contact your ODBC administrator.

Have you looked at the ODBC Operator chapter in the "Teradata Parallel Transporter Reference"? It has information about configuring the ODBC initialization file.

The format of the Connection string is a free form text. The TPT ODBC Operator passes the Connection string to the ODBC driver for connection identification.

The DSN name is the name of the data source.

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Hi Tony.

I am getting the below ODBC error. So we need more clarification on using ConnString in the variable file. I checked the TPT manual and the format is like

ConnectString = 'connectString' . But it will be helpful if I get the variable file with sample values.

***********************************

Teradata Parallel Transporter Version 14.00.00.08

Job log: /apps/teradata/client/14.00/tbuild/logs/<user_name>-144.out

Job id is <user_name>-144, running on <server.com>

Found CheckPoint file: /apps/teradata/client/14.00/tbuild/checkpoint/<user_name>LVCP

This is a restart job; it restarts at step MAIN_STEP.

Teradata Parallel Transporter Load Operator Version 14.00.00.08

LOAD_OPERATOR: private log specified: loadoper_privatelog

LOAD_OPERATOR: connecting sessions

Teradata Parallel Transporter ODBC Operator Version 14.00.00.08

EXPORT_OPERATOR: private log specified: /data/odbclog.txt-1

EXPORT_OPERATOR: connecting sessions

EXPORT_OPERATOR: TPT17122: Error: unable to connect to data source

EXPORT_OPERATOR: TPT17101: Fatal error received from ODBC driver:

              STATE=IM002, CODE=0,

              MSG='[DataDirect][ODBC lib] Data source name not found and no default driver specified'

EXPORT_OPERATOR: disconnecting sessions

EXPORT_OPERATOR: TPT17124: Error: unable to disconnect from data source

EXPORT_OPERATOR: TPT17101: Fatal error received from ODBC driver:

              STATE=08003, CODE=0,

              MSG='[DataDirect][ODBC lib] Connection not open'

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

EXPORT_OPERATOR: Start : Thu May 15 06:48:05 2014

EXPORT_OPERATOR: End   : Thu May 15 06:48:05 2014

LOAD_OPERATOR: preparing target table

LOAD_OPERATOR: entering Acquisition Phase

LOAD_OPERATOR: disconnecting sessions

LOAD_OPERATOR: Total processor time used = '19.85 Second(s)'

LOAD_OPERATOR: Start : Thu May 15 06:48:05 2014

LOAD_OPERATOR: End   : Thu May 15 06:48:32 2014

Job step MAIN_STEP terminated (status 12)

Job <user_name> terminated (status 12)

org.apache.commons.exec.ExecuteException: Process exited with an error: 12(Exit value: 12)

 at org.apache.commons.exec.DefaultExecutor.executeInternal(DefaultExecutor.java:346)

 at org.apache.commons.exec.DefaultExecutor.execute(DefaultExecutor.java:149)

 at org.apache.commons.exec.DefaultExecutor.execute(DefaultExecutor.java:136)

 at com.ge.health.fa.jfm.enhancement.Session$1.run(Session.java:93)

 at java.lang.Thread.run(Thread.java:619)

Thanks!!!

Teradata Employee

Re: Introduction to the Teradata Parallel Transporter Operators

VetriSlevan,

Here is a sample of the ConnectString attribute:

'DSN=MYDSN;UID=username;PWD=Secret'

You need to replace the MYDSN, username, and Secret values with your own values.

According to the TPT17122/TPT17101 errors, you specifed an incorrect or undefined DSN name. Make sure you are using the correct DSN name.

Enthusiast

Re: Introduction to the Teradata Parallel Transporter Operators

Hi Tony,

Awesome... I just need one more clarification. As per my understanding we need to use either Connection String or DSN name but the sample ConnectString('DSN=MYDSN;UID=username;PWD=Secret') you have provided has DSN name. I am trying to connect only by using Connectstring (but without DSN). Is that possible? If so can you please provide sample Connection String attribute? I could not get this proper format.

Thank you !!!