TPT fixed length file loading ISSUE - urgent

Tools & Utilities

TPT fixed length file loading ISSUE - urgent

Getting below error while loading fixed length file into teradata

 

$FILE_READER[1]: TPT19108 Data Format 'TEXT' requires all 'CHAR/ANSIDATE' schema.
$FILE_READER[1]: TPT19015 TPT Exit code set to 12.

 

I have configured below parameters ..

 

TargetTdpId='@TD_HOST'
,SessionMaxLimit=10
,TargetUserName='@TD_USER'
,TargetUserPassword='@TD_PASSWD'
,SrcFileInstance=2
,SrcFormat='Text'
,SrcMultipleReaders='yes'
,SrcReaderQuotedData='n'
,TargetMinSessions=1
,TgtLoadDropErrorTable='yes'
,TgtLoadDropLogTable='yes'
,TgtLoadErrorLimit=5
,TgtLoadInstances=1

 

 

Below are the content in the data file to be loaded to teradata using TPT

 

0000100001Z
0000200002Z
0000300003Z
0000400004Z

 

And the table created in teradata is like below

 

CREATE MULTISET TABLE DEV_POC_IT.GEO_ZIP_S_0314 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ZIPCODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
PARENT_ZIPCODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
NO PRIMARY INDEX ;

 

 

Can you please help on this topic?

 

 

 

7 REPLIES
Teradata Employee

Re: TPT fixed length file loading ISSUE - urgent

How is the schema being provided / generated / inferred?

 

Including the script and/or log (lightly edited if needed to remove confidential info) generally makes it easier for someone to provide a meaningful answer.

Re: TPT fixed length file loading ISSUE - urgent

Below is a simple example to load your data. The below script is provided by Teradata for loads. This will load data with '|' as column separator. You can always change this as needed.

 

1) Your data with '|' :    save the below as 'test.data'

 

00001|00001|Z

00002|00002|Z

00003|00003|Z

00004|00004|Z

 

2) variable file to be used for all parameters :  save the below as jobvars1vm.txt

 

 TargetTdpId          = 'Riverside'         /* Change */

,TargetUserName       = 'xxxxxx'       /* change */

,TargetUserPassword   = 'xxxxxx'     /* change */

,DDLPrivateLogName    = 'ddlprivate.log'

,LoadPrivateLogName   = 'loadprivate.log'

,MaxSessions          = 2

,MinSessions          = 2

,TargetErrorList      = ['3807']

,TargetTable          = 'smi_db2.Geo_zip_s_0314'    /* change  */

,LogTable             = 'smi_db2.Geo_zip_s_0314_lg'   /* change */

,ErrorTable1          = 'smi_db2.Geo_zip_s_0314_e1'  /* change */

,ErrorTable2          = 'smi_db2.Geo_zip_s_0314_e2'   /* change */

,SourceFileName       = 'test.data'   /* your data file name . ref to #1 above */

,SourceFormat         = 'delimited'

,SourceTextDelimiter  = '|'

,OpenMode             = 'read'

 

#3 Teradata script :  save the below as qstart1vm.txt

 

DEFINE JOB qstart1vm

(

  APPLY $INSERT TO OPERATOR ($LOAD)

  SELECT * FROM OPERATOR($FILE_READER);

);

 

#4  excute :

 

tbuild -f  qstart1vm.txt -v  jobvars1vm.txt -j test123

 

The script assumes your data is seperated by '|' . Hope this helps.

Teradata Employee

Re: TPT fixed length file loading ISSUE - urgent

That example is not going to work; the original post did not have delimited data.

The user can keep the data file as it is and use:

  

SourceFormat = 'Text'

 

and because the target table is made up of all fixed-length CHAR fields, it should work just fine.

 

In order to find out why it did not work, as Fred pointed out, we need to see what was being used so we can

advise how to move forward.

 

-- SteveF

Re: TPT fixed length file loading ISSUE - urgent

Yes I agree with Fred and Steve that 'TEXT' format should work. 

My solution was just an alternative in case user is not able to upload the file/log and wants to just wants to load the data. The advantage here is that the user need not even define the schema inside the script. 

 

I always value your inputs.

 

-- Murali

 

 

Re: TPT fixed length file loading ISSUE - urgent

Make sure you have defined the field(s) as CHAR data type  in your script file. If you define the filed(s) as VARCHAR and load as 'TEXT' format, you may encounter this error.  -- SMI

Re: TPT fixed length file loading ISSUE - urgent

Hi Fred Thanks for your reply , we are already having a framework job in invoke TPT.. below are the error log

 

Files to be Processed

-rw-r--r-- 1 xdwetlusr xdwetlgrp 534040 Mar 15 01:29 /app_data/dev/STG_GEO/in/working.46960.zip18.dat


******ERROR LOADING FILE /app_data/dev/STG_GEO/in/zip18.dat .. Please validate*******
return code is 12
Pls Refer the log file /app_code/dev/STG_GEO/TEMP/STG_GEO.18.46960.log


Pls Refer the log file /app_code/dev/STG_GEO/TEMP/STG_GEO.18.46960.log shown below

*******************************************************************************
Contents of /app_code/dev/STG_GEO/TEMP/STG_GEO.18.46960.log
*******************************************************************************

Teradata Parallel Transporter Version 15.10.01.00 64-Bit
Job log: /app_code/dev/STG_GEO/TEMP/STG_GEO.18.46960_DEV-240970.out
Job id is STG_GEO.18.46960_DEV-240970, running on d01nappd0002
Teradata Parallel Transporter SQL DDL Operator Version 15.10.01.00
$DDL: private log not specified
$DDL: connecting sessions
$DDL: sending SQL requests
$DDL: Rows Deleted: 0
$DDL: disconnecting sessions
$DDL: Total processor time used = '0.01 Second(s)'
$DDL: Start : Thu Mar 15 01:58:24 2018
$DDL: End : Thu Mar 15 01:58:25 2018
Job step TruncTbl completed successfully
Teradata Parallel Transporter DataConnector Operator Version 15.10.01.00
Teradata Parallel Transporter Load Operator Version 15.10.01.00
$LOAD: private log not specified
$FILE_READER[1]: Instance 1 directing private log report to 'dtacop-xdwetlusr-47129-1'.
$FILE_READER[1]: DataConnector Producer operator Instances: 1
$FILE_READER[1]: TPT19108 Data Format 'TEXT' requires all 'CHAR/ANSIDATE' schema.
$FILE_READER[1]: TPT19015 TPT Exit code set to 12.
$FILE_READER[1]: Total files processed: 0.
$LOAD: connecting sessions
$LOAD: preparing target table
$LOAD: entering Acquisition Phase
$LOAD: disconnecting sessions
$LOAD: Total processor time used = '0.23 Second(s)'
$LOAD: Start : Thu Mar 15 01:58:27 2018
$LOAD: End : Thu Mar 15 01:58:31 2018
Job step Load_GEO_ZIP_S_0314 terminated (status 12)
Job STG_GEO.18.46960_DEV terminated (status 12)
Job start: Thu Mar 15 01:58:22 2018
Job end: Thu Mar 15 01:58:31 2018
ERROR td_load.ksh ABENDED with Return Code 12. Running in /app_code/dev/ETL_FRAMEWORK/SCRIPTS
Elapsed Time 0 minutes and 21 seconds.

Teradata Employee

Re: TPT fixed length file loading ISSUE - urgent

I should have been more specific. We need to see the TPT log, not just the STDOUT / STDERR from the job; e.g. in your example, the that would be the output from

tlogview -l /app_code/dev/STG_GEO/TEMP/STG_GEO.18.46960_DEV-240970.out -f "*" -g

 

Ideally you could also share the TPT script (the filename given in the -f parameter on the tbuild command, not the .ksh)