All Forums Tools
JDBA 2 posts Joined 06/10
29 Jan 2013
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

James
feinholz 1234 posts Joined 05/08
30 Jan 2013

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

--SteveF

JDBA 2 posts Joined 06/10
31 Jan 2013

No, there is no end of marker afte the last data item.
This data is in Note pad.

James

simplesim092 11 posts Joined 04/14
02 May 2014

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

feinholz 1234 posts Joined 05/08
02 May 2014

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

simplesim092 11 posts Joined 04/14
03 May 2014

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

simplesim092 11 posts Joined 04/14
03 May 2014

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...!!!

tusharzaware1 25 posts Joined 02/13
08 Aug 2015

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.

 

feinholz 1234 posts Joined 05/08
10 Aug 2015

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

tusharzaware1 25 posts Joined 02/13
10 Aug 2015

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

feinholz 1234 posts Joined 05/08
10 Aug 2015

If the column is defined as varchar(20) in the database:
- if you are using the client session character set of ASCII (there is no ANSI), then the column must be defined as varchar(20) in the script.
- if you are using the client session character set of UTF8, then the column must be defined as varchar(60) in the script.
 
As to the EOF/EOR issue, we do not support TPT 13.0 anymore.
When you create the file manually, make sure the last record has the end-of-record marker (usuallt the newline character) at the end.
 

--SteveF

tusharzaware1 25 posts Joined 02/13
10 Aug 2015

Sorry for the Typo Steve (ANCII) :)
As you mentioned above, I am using ANCII and column defined as varchar(20) still I am getting below errors:
1)Data Format 'DELIMITED' requires all 'VARCHAR' schema. - When I define custname  as varchar(20)

2)Output Schema does not match data from SELECT statement - When I define all columns as varchar(20)

 

Please suggest.

 

Thanks,

Tushar

feinholz 1234 posts Joined 05/08
10 Aug 2015

I would be able to help if I have all of the information, instead of a little at a time.
If you are reading data from a file with the DataConnector operator, and the format is "delimited", then the schema (all columns) must be VARCHAR fields.
If the job still terminates with errors, then you will need to provide the entire script, and a sample of the data.
 

--SteveF

tusharzaware1 25 posts Joined 02/13
11 Aug 2015

PFB:
Sample Data:

1,Tushar1,9975775207

2,Tushar2,9975775208

3,Tushar3,9975775209

4,Tushar4,9975775210

5,Tushar5,9975775211

 

 

Script:

 

/* 1 */  

/* 2 */  

/* 3 */  USING CHARACTER SET ASCII

/* 4 */  DEFINE JOB EXP_1

/* 5 */  (

/* 6 */   DEFINE OPERATOR W_1_o_EXP_1

/* 7 */   TYPE DATACONNECTOR CONSUMER

/* 8 */   SCHEMA *

/* 9 */   ATTRIBUTES

/* 10 */   (

/* 11 */   VARCHAR FileName, 

/* 12 */   VARCHAR Format, 

/* 13 */   VARCHAR OpenMode, 

/* 14 */   INTEGER BlockSize, 

/* 15 */   INTEGER BufferSize, 

/* 16 */   INTEGER RetentionPeriod, 

/* 17 */   INTEGER RowsPerInstance, 

/* 18 */   INTEGER SecondarySpace, 

/* 19 */   INTEGER UnitCount, 

/* 20 */   INTEGER VigilElapsedTime, 

/* 21 */   INTEGER VigilWaitTime, 

/* 22 */   INTEGER VolumeCount, 

/* 23 */   VARCHAR AccessModuleName, 

/* 24 */   VARCHAR AccessModuleInitStr, 

/* 25 */   VARCHAR DirectoryPath, 

/* 26 */   VARCHAR ExpirationDate, 

/* 27 */   VARCHAR IndicatorMode, 

/* 28 */   VARCHAR PrimarySpace, 

/* 29 */   VARCHAR PrivateLogName, 

/* 30 */   VARCHAR RecordFormat, 

/* 31 */   VARCHAR RecordLength, 

/* 32 */   VARCHAR SpaceUnit, 

/* 33 */   VARCHAR TextDelimiter, 

/* 34 */   VARCHAR VigilNoticeFileName, 

/* 35 */   VARCHAR VigilStartTime, 

/* 36 */   VARCHAR VigilStopTime, 

/* 37 */   VARCHAR VolSerNumber, 

/* 38 */   VARCHAR UnitType

/* 39 */   );

/* 40 */  

/* 41 */   DEFINE SCHEMA W_0_s_EXP_1

/* 42 */   (

/* 43 */   orderno VARCHAR(20),

/* 44 */   productname VARCHAR(20),

/* 45 */   qty VARCHAR(20),

/* 46 */   custno VARCHAR(20)

/* 47 */   );

/* 48 */  

/* 49 */   DEFINE OPERATOR W_0_o_EXP_1

/* 50 */   TYPE EXPORT

/* 51 */   SCHEMA W_0_s_EXP_1

/* 52 */   ATTRIBUTES

/* 53 */   (

/* 54 */   VARCHAR UserName, 

/* 55 */   VARCHAR UserPassword, 

/* 56 */   VARCHAR SelectStmt, 

/* 57 */   INTEGER BlockSize, 

/* 58 */   INTEGER MaxSessions, 

/* 59 */   INTEGER MinSessions, 

/* 60 */   INTEGER TenacityHours, 

/* 61 */   INTEGER TenacitySleep, 

/* 62 */   INTEGER MaxDecimalDigits, 

/* 63 */   VARCHAR AccountID, 

/* 64 */   VARCHAR DateForm, 

/* 65 */   VARCHAR NotifyExit, 

/* 66 */   VARCHAR NotifyExitIsDLL, 

/* 67 */   VARCHAR NotifyLevel, 

/* 68 */   VARCHAR NotifyMethod, 

/* 69 */   VARCHAR NotifyString, 

/* 70 */   VARCHAR PrivateLogName, 

/* 71 */   VARCHAR TdpId, 

/* 72 */   VARCHAR TraceLevel, 

/* 73 */   VARCHAR WorkingDatabase

/* 74 */   );

/* 75 */  

/* 76 */   APPLY

/* 77 */   TO OPERATOR

/* 78 */   (

/* 79 */   W_1_o_EXP_1[1]

/* 80 */  

/* 81 */   ATTRIBUTES

/* 82 */   (

/* 83 */   FileName = 'C:\Users\Dadaji\Desktop\TEST', 

/* 84 */   Format = 'DELIMITED', 

/* 85 */   OpenMode = 'Write', 

/* 86 */   IndicatorMode = 'N', 

/* 87 */   TextDelimiter = '|'

/* 88 */   )

/* 89 */   )

/* 90 */   SELECT * FROM OPERATOR

/* 91 */   (

/* 92 */   W_0_o_EXP_1[1]

/* 93 */  

/* 94 */   ATTRIBUTES

/* 95 */   (

/* 96 */   UserName = 'tduser', 

/* 97 */   UserPassword = 'tduser', 

/* 98 */   SelectStmt = 'SELECT orderno,productname,qty,custno FROM DBA_BACKUP.Order_Table;', 

/* 99 */   TdpId = '127.0.0.1'

/* 100 */   )

/* 101 */   );

/* 102 */  );

 

O/P:

 

Teradata Parallel Transporter Version 13.00.00.02

Job log: C:\Program Files\Teradata\Client\13.0\Teradata Parallel Transporter\\/logs/Export_Test-42.out

Job id is Export_Test-42, running on Dadaji-PC

Teradata Parallel Transporter DataConnector Version 13.00.00.02

Teradata Parallel Transporter Export Operator Version 13.00.00.00

W_1_o_EXP_1 DataConnector Consumer operator Instances: 1

W_0_o_EXP_1: private log not specified

W_1_o_EXP_1 Operator instance 1 processing file 'C:\Users\Dadaji\Desktop\TEST'.

W_0_o_EXP_1: connecting sessions

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

W_0_o_EXP_1: disconnecting sessions

W_0_o_EXP_1: Total processor time used = '0.312002 Second(s)'

W_0_o_EXP_1: Start : Tue Aug 11 17:27:57 2015

W_0_o_EXP_1: End   : Tue Aug 11 17:27:58 2015

W_1_o_EXP_1 Total files processed: 0.

Job step MAIN_STEP terminated (status 12)

Job Export_Test terminated (status 12)

 

 

Thanks,

Tushar

 

tusharzaware1 25 posts Joined 02/13
11 Aug 2015

Sorry , Posted Wrong Script. PFB correct export script. I have successfully done IMPORT!!!! Getting Error in export!!!
Export Script :
 

/* 1 */  

/* 2 */  

/* 3 */  USING CHARACTER SET ASCII

/* 4 */  DEFINE JOB Test_export

/* 5 */  (

/* 6 */   DEFINE OPERATOR W_1_o_Test_export

/* 7 */   TYPE DATACONNECTOR CONSUMER

/* 8 */   SCHEMA *

/* 9 */   ATTRIBUTES

/* 10 */   (

/* 11 */   VARCHAR FileName, 

/* 12 */   VARCHAR Format, 

/* 13 */   VARCHAR OpenMode, 

/* 14 */   INTEGER BlockSize, 

/* 15 */   INTEGER BufferSize, 

/* 16 */   INTEGER RetentionPeriod, 

/* 17 */   INTEGER RowsPerInstance, 

/* 18 */   INTEGER SecondarySpace, 

/* 19 */   INTEGER UnitCount, 

/* 20 */   INTEGER VigilElapsedTime, 

/* 21 */   INTEGER VigilWaitTime, 

/* 22 */   INTEGER VolumeCount, 

/* 23 */   VARCHAR AccessModuleName, 

/* 24 */   VARCHAR AccessModuleInitStr, 

/* 25 */   VARCHAR DirectoryPath, 

/* 26 */   VARCHAR ExpirationDate, 

/* 27 */   VARCHAR IndicatorMode, 

/* 28 */   VARCHAR PrimarySpace, 

/* 29 */   VARCHAR PrivateLogName, 

/* 30 */   VARCHAR RecordFormat, 

/* 31 */   VARCHAR RecordLength, 

/* 32 */   VARCHAR SpaceUnit, 

/* 33 */   VARCHAR TextDelimiter, 

/* 34 */   VARCHAR VigilNoticeFileName, 

/* 35 */   VARCHAR VigilStartTime, 

/* 36 */   VARCHAR VigilStopTime, 

/* 37 */   VARCHAR VolSerNumber, 

/* 38 */   VARCHAR UnitType

/* 39 */   );

/* 40 */  

/* 41 */   DEFINE SCHEMA W_0_s_Test_export

/* 42 */   (

/* 43 */   custno VARCHAR(20),

/* 44 */   custname VARCHAR(20),

/* 45 */   mob VARCHAR(20)

/* 46 */   );

/* 47 */  

/* 48 */   DEFINE OPERATOR W_0_o_Test_export

/* 49 */   TYPE EXPORT

/* 50 */   SCHEMA W_0_s_Test_export

/* 51 */   ATTRIBUTES

/* 52 */   (

/* 53 */   VARCHAR UserName, 

/* 54 */   VARCHAR UserPassword, 

/* 55 */   VARCHAR SelectStmt, 

/* 56 */   INTEGER BlockSize, 

/* 57 */   INTEGER MaxSessions, 

/* 58 */   INTEGER MinSessions, 

/* 59 */   INTEGER TenacityHours, 

/* 60 */   INTEGER TenacitySleep, 

/* 61 */   INTEGER MaxDecimalDigits, 

/* 62 */   VARCHAR AccountID, 

/* 63 */   VARCHAR DateForm, 

/* 64 */   VARCHAR NotifyExit, 

/* 65 */   VARCHAR NotifyExitIsDLL, 

/* 66 */   VARCHAR NotifyLevel, 

/* 67 */   VARCHAR NotifyMethod, 

/* 68 */   VARCHAR NotifyString, 

/* 69 */   VARCHAR PrivateLogName, 

/* 70 */   VARCHAR TdpId, 

/* 71 */   VARCHAR TraceLevel, 

/* 72 */   VARCHAR WorkingDatabase

/* 73 */   );

/* 74 */  

/* 75 */   APPLY

/* 76 */   TO OPERATOR

/* 77 */   (

/* 78 */   W_1_o_Test_export[1]

/* 79 */  

/* 80 */   ATTRIBUTES

/* 81 */   (

/* 82 */   FileName = 'C:\TD Utilities\Exported Files\Test_exp', 

/* 83 */   Format = 'DELIMITED', 

/* 84 */   OpenMode = 'Write', 

/* 85 */   IndicatorMode = 'N', 

/* 86 */   TextDelimiter = ','

/* 87 */   )

/* 88 */   )

/* 89 */   SELECT * FROM OPERATOR

/* 90 */   (

/* 91 */   W_0_o_Test_export[1]

/* 92 */  

/* 93 */   ATTRIBUTES

/* 94 */   (

/* 95 */   UserName = 'tduser', 

/* 96 */   UserPassword = 'tduser', 

/* 97 */   SelectStmt = 'SELECT custno,custname,mob FROM DBA_BACKUP.customer_tbl;', 

/* 98 */   TdpId = '127.0.0.1'

/* 99 */   )

/* 100 */   );

/* 101 */  );

 

Sample Data prensent in Customer_tbl:
1,Tushar1,9975775207
2,Tushar2,9975775208
3,Tushar3,9975775209
4,Tushar4,9975775210
5,Tushar5,9975775211

O/P:
 

Teradata Parallel Transporter Version 13.00.00.02

Job log: C:\Program Files\Teradata\Client\13.0\Teradata Parallel Transporter\\/logs/exp_var1-51.out

Job id is exp_var1-51, running on Dadaji-PC

Teradata Parallel Transporter DataConnector Version 13.00.00.02

Teradata Parallel Transporter Export Operator Version 13.00.00.00

W_0_o_Test_export: private log not specified

W_1_o_Test_export DataConnector Consumer operator Instances: 1

W_1_o_Test_export Operator instance 1 processing file 'C:\TD Utilities\Exported Files\Test_exp'.

W_0_o_Test_export: connecting sessions

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

W_0_o_Test_export: disconnecting sessions

W_0_o_Test_export: Total processor time used = '0.327602 Second(s)'

W_0_o_Test_export: Start : Tue Aug 11 17:53:06 2015

W_0_o_Test_export: End   : Tue Aug 11 17:53:07 2015

W_1_o_Test_export Total files processed: 0.

Job step MAIN_STEP terminated (status 12)

Job exp_var1 terminated (status 12)

 

 

Thanks,

Tushar

feinholz 1234 posts Joined 05/08
11 Aug 2015

In TPT 13.0 you cannot use the Export operator to write out data in DELIMITED format.
The Export operator can only retrieve the data in binary format and thus the schema must match the output from the Export operator (which will most likely not be all VARCHAR).
You need to use the Selector operator, and enable ReportMode.
This will cause the data to be exported by the Selector operator in Teradata Field Mode (all VARCHAR data) and then the schema will be ok and the data can be written out in DELIMITED format.
Please refer to the documentation for more information.
 

--SteveF

tusharzaware1 25 posts Joined 02/13
11 Aug 2015

Thanks Steve!!!! 

You must sign in to leave a comment.