TPT error in loading from an external file

Tools
Enthusiast

TPT error in loading from an external file

TD v13 on Windows,

Dear experts,

Could you tell me why I get an error in running a TPT?

Thank you in advance.

1. TPT script

/**************************************************************/

/*

/* Teradata Parallel Transporter

/*

/**************************************************************/

/* Description: 7

/*

/* This example script uses two job steps.

/*

/* The first job step, called "Setup_Tables", uses the DDL

/* Operator to setup the target table.

/*

/* The second job step, called "Load_Trans_Table", uses

/* an instance of the DataConnector Operator to read

/* rows from a file and uses an instance of the Load

/* Operator to write the rows into an empty Teradata target

/* table.

/**************************************************************/

DEFINE JOB FILE_LOAD

DESCRIPTION 'Load a Teradata table from a file'

(

DEFINE SCHEMA Trans_n_Accts_Schema

(

ident VARCHAR(30),

i1    VARCHAR(30)

);

DEFINE OPERATOR DDL_OPERATOR

TYPE DDL

ATTRIBUTES

(

VARCHAR PrivateLogName,

VARCHAR TdpId                 = '@TDPID',

VARCHAR UserName             = @UserID,

VARCHAR UserPassword         = @Password,

VARCHAR AccountID,

VARCHAR DataEncryption,

VARCHAR LogonMech,

VARCHAR LogonMechData,

VARCHAR QueryBandSessInfo,

VARCHAR ErrorList = '3807'

);

DEFINE OPERATOR FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA Trans_n_Accts_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName,

VARCHAR DirectoryPath         = 'C:\xxxx\Teradata\TPT Practice 01',

VARCHAR FileName             = 'testdata.txt',

VARCHAR Format                 = 'Delimited',

VARCHAR OpenMode             = 'Read',

VARCHAR TextDelimiter         = ','

);

DEFINE OPERATOR LOAD_OPERATOR

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName,

VARCHAR TdpId                 = 'xxxxxxxx',

VARCHAR UserName             = @UserID,

VARCHAR UserPassword         = @Password,

VARCHAR TargetTable         = 'xxxxxx.jp_test01',

VARCHAR SelectStmt            = 'sel * from xxxxxxx.jp_test01',

VARCHAR ErrorTable1         = @ErrTbl1 ,

VARCHAR ErrorTable2         = @ErrTbl2 ,

VARCHAR LogTable            = @LogTbl

);

STEP Setup_Tables

(

APPLY

( 'del from xxxxxx.jp_test01;'  )

TO OPERATOR (DDL_OPERATOR);

);

STEP Load_Trans_Table

(

APPLY

( 'insert into xxxxxxJP_TEST01

(ident,

i1 )

VALUES

(:ident,

:i1 );')

TO OPERATOR (LOAD_OPERATOR[1])

SELECT

*

FROM OPERATOR

(FILE_READER[1]);

);

);

2. Output

Teradata Parallel Transporter Version 13.10.00.04

Job log: ./load1exe-181.out

Job id is load1exe-181, running on WITDL140

Found CheckPoint file: C:\Program Files\Teradata\client\13.10\Teradata Parallel

Transporter/checkpoint\load1exeLVCP

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

Teradata Parallel Transporter DataConnector Version 13.10.00.04

FILE_READER Instance 1 restarting.

FILE_READER: TPT19008 DataConnector Producer operator Instances: 1

Teradata Parallel Transporter Load Operator Version 13.10.00.03

LOAD_OPERATOR: private log not specified

FILE_READER: TPT19003 ECI operator ID: FILE_READER-496

LOAD_OPERATOR: connecting sessions

LOAD_OPERATOR: preparing target table

LOAD_OPERATOR: entering Acquisition Phase

FILE_READER: TPT19222 Operator instance 1 processing file 'C:\JPARK\Teradata\TPT

 Practice 01\testdata.txt'.

FILE_READER: TPT19350 I/O error on file 'C:\JPARK\Teradata\TPT Practice 01\testd

ata.txt'.

FILE_READER: TPT19435 pmRead failed. EOF encountered before end of record (35)

FILE_READER: TPT19305 Fatal error reading data.


LOAD_OPERATOR: disconnecting sessions

FILE_READER: TPT19221 Total files processed: 0.

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

LOAD_OPERATOR: Start : Tue Jan 29 15:25:50 2013

LOAD_OPERATOR: End   : Tue Jan 29 15:26:02 2013

Job step Load_Trans_Table terminated (status 12)

Job load1exe terminated (status 12)

3. input file

135,123433789012345

134,123456789012345

125,1892345567

123,1234567

127,18899234567

136,129363378901345

126,18992345567

124,2345567

289,4444444

299,555555

400,6666666

16 REPLIES
Teradata Employee

Re: TPT error in loading from an external file

Is there an end-of-record marker after the last data item in the last record?

-- SteveF
Enthusiast

Re: TPT error in loading from an external file

No, there is no end of marker afte the last data item.

This data is in Note pad.

Enthusiast

Re: TPT error in loading from an external file

Does any one has resolution for this..I am facing teh same issue as point 2.. as above and There is no EOF

Teradata Employee

Re: TPT error in loading from an external file

I would imagine all files have an EOF.

Every record should also have EOR.

(We are fixing the code so that we will treat EOF as EOR on the last record, but that fix is not out yet. Thus, add an EOR to your last record.)

-- SteveF
Enthusiast

Re: TPT error in loading from an external file

Yeah ..It worked well with the EOR.. :-) ..!!! Thanks for reply ..!!!

Enthusiast

Re: TPT error in loading from an external file

I have another doubt about TPT..I have table which is to be loaded in one DB and for error tables hv defined another DB..when I running TPT I am facing an error as 

1) _OPERATOR: TPT10508: RDBMS error 3524: The user does not have CREATE TABLE access to database DWH..

Why do we need access for create  TO this DB..My error table DB has all the access...We dont have access for DDL in DWH..Do we need access for DWh scehma as we are creating varchar schema for Target table in DWH..in TPT .??

2) My other doubt is about....I am using update operator and File reader operator to load data from CSV to TD table...once my tpt got failed when i deleted my checkpoint and all ERROR tables ...Its giving m error as

TPT10508: RDBMS error 2652: Operation not allowed: DWH.ACCOUNT_HL is being Loaded.

Is der any other way rather than Recreating the DWH.ACCOUNT_HL table..As I doubt..we wil get access to DWH schema for DDL..

Is der way to handle this in TPT...

Awaiting for response...!!!

Enthusiast

Re: TPT error in loading from an external file

Hi Steve,

My Source file is:

1,Tushar1,9975775207

2,Tushar2,9975775208

3,Tushar3,9975775209

4,Tushar4,9975775210

5,Tushar5,9975775211

I am getting below error:

W_0_o_TPT_SRC_Job1 Operator instance 1 processing file 'C:\TD Utilities\Source Files\EmpFlat1.txt'.

W_0_o_TPT_SRC_Job1: TPT19350 I/O error on file 'C:\TD Utilities\Source Files\EmpFlat1.txt'.

W_0_o_TPT_SRC_Job1: TPT19435 pmRead failed. EOF encountered before end of record (35)

I do not understand what do you mean by " add an EOR to your last record."

Please help.

Teradata Employee

Re: TPT error in loading from an external file

Sometimes when data files are generated, the EOF (end of file character) comes right after the last byte of data, instead of the end-of-line marker.

What version of TPT are you using?

We did address this issue in TPT, but I need to know what version you are running so I can check that against when the issue was fixed.

-- SteveF
Enthusiast

Re: TPT error in loading from an external file

Hi Steve,

1) I am using Teradata Parallel Transporter Version 13.00.00.02 and i have manually created above source file.

2)Before import I tried Teradata Wizart to export the data to file but I was getting below error(That's y i had created file manually for above import):

W_0_o_Test_export: TPT12108: Output Schema does not match data from SELECT statement

Character Set :ANSII

My Table:

custno int,

custname varchar(20),

mob bigint

I checked your comment on another blog regarding this issue that "First of all, when you use a character set of UTF8, the sizes of the CHAR/VARCHAR columns must be 3x the definition of the columns in Teradata. That is because the size of the column in Teradata (and SQL) is in terms of characters, and TPT is in terms of bytes. And a single character can be anywhere from 1-3 bytes when the character set is UTF8."

Can you please help me to understand what varchar size should i use for above three column for this export?

Awaiting your reply...

Thanks,

Tushar