All Forums Teradata Applications
ck185031 7 posts Joined 09/10
04 Jun 2015
Why TPT is successful with return code 0, even when records goes to ET table?

We just upgraded To TD 14.10.04.06 version from TD 12, and we are in a process of replacing all the fastload scripts with TPT Load.
Earlier with fastload, it used to load the good records and move bad records to ET and job used to fail.
I noticed TPT doesn't fail even when bad records (data issue like, NULLs value into not null column etc.) goes to ET table, and it loads the table with good records.
I want to make it fail or successful with warning code 4, so that I can take some action for the bad records. Otherwise I will never get to know if it runs in batch.
Or atleast could someone suggest me any alternative for the problem.
I'm using TPT version 14.10.00.05.
I would greatly appreciate any help. Thank you.
--Chandu

-- Chandu
dnoeth 4628 posts Joined 11/04
04 Jun 2015

Hi Chandu, 
FastLoad exits with "Highest return code encountered = '0'" if there are any rows in the Error Tables.
Only when you specify an ErrorLimit the job will fail, this is the same for TPT.
So either add ErrorLimit or check if the Error Tables exist after the job finished.

Dieter

ck185031 7 posts Joined 09/10
04 Jun 2015

Perfect. Dieter thank you for your prompt reply.
Now, I have a small rather stupid question, is there any way/option by which I can make my TPT job load all the good records and let bad record go to error table but my script should fail/"success with warning" and my target table should not be locked.

-- Chandu

dnoeth 4628 posts Joined 11/04
04 Jun 2015

Hi Chandu,
afaik this is not possible, but this was the same for FastLoad :-)

Dieter

ck185031 7 posts Joined 09/10
05 Jun 2015

Thanks Dieter. Appreciate your help.

-- Chandu

ck185031 7 posts Joined 09/10
05 Jun 2015

For TPT Load, is there any flag which could allow error messages to be displayed in log. Currently, it doesn't show much info than below messages,

 

entering Acquisition Phase

Of 20 row(s) sent to the RDBMS,

1 row(s) were recorded as errors.

Job step data_load terminated (status 12)

 

By keeping ErrorLimit flag, my target table and ET table gets locked in case of failuare. And by no means I can find details about bad data like which record number and column has issue.

 

Earlier in fastload,  I used to get row number and column number information in log.

-- Chandu

dnoeth 4628 posts Joined 11/04
05 Jun 2015

Hi Chandu,
where in the FastLoad log did you find that information?
Only the number of errors is returned.

Dieter

ck185031 7 posts Joined 09/10
05 Jun 2015

FYI...

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

     =                                                                 =

     =          FASTLOAD UTILITY     VERSION 14.10.00.04               =

     =          PLATFORM SOLARIS/SPARC                                 =

     =                                                                 =

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

 

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

     =                                                                 =

     =          Copyright 1984-2013, Teradata Corporation.             =

     =          ALL RIGHTS RESERVED.                                   =

     =                                                                 =

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

 

**** 06:44:21 Processing starting at: Thu Jun  4 06:44:21 2015

 

0001 SESSIONS 32;

 

**** 06:44:21 FDL4866 SESSIONS command accepted

 

0002 .TENACITY 1;

 

**** 06:44:21 Tenacity Enabled:  1 hour(s)

 

0003 .SLEEP 6;

 

**** 06:44:21 Sleep Minutes Set: 6 minute(s)

 

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

     =                                                                 =

     =          Logon/Connection                                       =

     =                                                                 =

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

 

0004 .logon xxxxx/xxxxx,

 

**** 06:44:21 Teradata Database Release: 14.10.04.06

**** 06:44:21 Teradata Database Version: 14.10.04.06

**** 06:44:21 Number of AMPs available: 72

**** 06:44:21 Current CLI or RDBMS allows maximum row size: 64K

**** 06:44:21 Character set for this job: ASCII

 

 

0005 DELETE FROM tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK  ALL;

 

**** 06:44:22 Command completed successfully

 

0006 DROP  TABLE tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK_E1;

 

**** 06:44:22 RDBMS error 3807: Object

              'tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK_E1' does not

              exist.

 

0007 DROP  TABLE tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK_E2;

 

**** 06:44:22 RDBMS error 3807: Object

              'tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK_E2' does not

              exist.

 

0008 SET RECORD VARTEXT "»"  DISPLAY_ERRORS  NOSTOP;

 

**** 06:44:22 Now set to read 'Variable-Length Text' records

**** 06:44:22 Delimiter character(s) is set to '»'

**** 06:44:22 Rejected rows will be sent to STDERR

**** 06:44:22 FastLoad will continue if a row is rejected

**** 06:44:22 Command completed successfully

 

0009 BEGIN LOADING tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK

     ERRORFILES tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK_E1 , tst_dw_mstr_stage.T

     FL_MD_SF_DISTRCHECK_E2

     CHECKPOINT 0;

 

**** 06:44:27 Number of FastLoad sessions requested = 32

**** 06:44:27 Number of FastLoad sessions connected = 32

**** 06:44:27 FDL4808 LOGON successful

**** 06:44:28 Number of AMPs available: 72

**** 06:44:28 BEGIN LOADING COMPLETE

 

0010 DEFINE

     Available_c     (VARCHAR(50))

      , Begin_Date_c     (VARCHAR(50))

      , CreatedById     (VARCHAR(50))

      , CreatedDate     (VARCHAR(50))

      , CurrencyIsoCode     (VARCHAR(50))

      , IsDeleted     (VARCHAR(50))

      , Display_Rank_c     (VARCHAR(50))

      , Distribution_Check_Group_c     (VARCHAR(50))

      , Name     (VARCHAR(80))

      , End_Date_c     (VARCHAR(50))

      , Group_c     (VARCHAR(255))

      , LastActivityDate     (VARCHAR(50))

      , LastModifiedById     (VARCHAR(50))

      , LastModifiedDate     (VARCHAR(50))

      , OwnerId     (VARCHAR(50))

      , Pack_Type_c     (VARCHAR(255))

      , Product_c     (VARCHAR(50))

      , Product_Segmentation_c     (VARCHAR(255))

      , Rank_c     (VARCHAR(50))

      , Id     (VARCHAR(50))

      , Region_c     (VARCHAR(1500))

      , Score_with_Core_Region_c     (VARCHAR(255))

      , Short_Material_Number_c     (VARCHAR(1500))

      , SystemModstamp     (VARCHAR(50))

      , UPC_c     (VARCHAR(1500))

     FILE=/hsy/teradata/tst/data/SF_DISTRCHECK.dat;

 

**** 06:44:28 FDL4803 DEFINE statement processed

 

0011 RECORD 1;

 

**** 06:44:28 Starting record number set to  : 1

 

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

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

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

 

0012 INSERT INTO tst_dw_mstr_stage.TFL_MD_SF_DISTRCHECK

     (

     Available_c

      , Begin_Date_c

      , CreatedById

      , CreatedDate

      , CurrencyIsoCode

      , IsDeleted

      , Display_Rank_c

      , Distribution_Check_Group_c

      , Name

      , End_Date_c

      , Group_c

      , LastActivityDate

      , LastModifiedById

      , LastModifiedDate

      , OwnerId

      , Pack_Type_c

      , Product_c

      , Product_Segmentation_c

      , Rank_c

      , Id

      , Region_c

      , Score_with_Core_Region_c

      , Short_Material_Number_c

      , SystemModstamp

      , UPC_c

     )

     VALUES

     (

     :Available_c

      , :Begin_Date_c

      , :CreatedById

      , :CreatedDate

      , :CurrencyIsoCode

      , :IsDeleted

      , :Display_Rank_c

      , :Distribution_Check_Group_c

      , :Name

      , :End_Date_c

      , :Group_c

      , :LastActivityDate

      , :LastModifiedById

      , :LastModifiedDate

      , :OwnerId

      , :Pack_Type_c

      , :Product_c

      , :Product_Segmentation_c

      , :Rank_c

      , :Id

      , :Region_c

      , :Score_with_Core_Region_c

      , :Short_Material_Number_c

      , :SystemModstamp

      , :UPC_c

     );

 

**** 06:44:28 Number of recs/msg: 9

**** 06:44:28 Starting to send to RDBMS with record 1

**** 06:44:28 Error on piom GET ROW: 60, Text: Column length error, row

              not returned !ERROR! Delimited Data Parsing error: Column

              length overflow(s) in row 29 for column 4

**** 06:44:28 Error at record number 29

**** 06:44:28 Error on piom GET ROW: 60, Text: Column length error, row

              not returned !ERROR! Delimited Data Parsing error: Column

              length overflow(s) in row 34 for column 4

**** 06:44:28 Error at record number 34

**** 06:44:28 Error on piom GET ROW: 60, Text: Column length error, row

              not returned !ERROR! Delimited Data Parsing error: Column

              length overflow(s) in row 37 for column 4

**** 06:44:28 Error at record number 37

**** 06:44:28 Error on piom GET ROW: 60, Text: Column length error, row

              not returned !ERROR! Delimited Data Parsing error: Column

              length overflow(s) in row 47 for column 4

**** 06:44:28 Error at record number 47

**** 06:44:28 Error on piom GET ROW: 60, Text: Column length error, row

              not returned !ERROR! Delimited Data Parsing error: Column

              length overflow(s) in row 181 for column 4

**** 06:44:28 Error at record number 181

**** 06:44:28 Sending row 241

**** 06:44:28 Finished sending rows to the RDBMS

 

**** 06:44:28 Acquisition Phase statistics:

              Elapsed time: 00:00:00 (in hh:mm:ss)

              CPU time:     0 Seconds

              MB/sec:       N/A

              MB/cpusec:    N/A

 

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

     =                                                                 =

     =          End Loading Phase                                      =

     =                                                                 =

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

 

0013 END LOADING;

 

**** 06:44:28 END LOADING COMPLETE

 

     Total Records Read              =  241

      - skipped by RECORD command    =  0

      - sent to the RDBMS            =  241

     Total Error Table 1             =  0  ---- Table has been dropped

     Total Error Table 2             =  0  ---- Table has been dropped

     Total Inserts Applied           =  236

     Total Duplicate Rows            =  0

     Total Rejected Rows             =  5

 

     Start:   Thu Jun  4 06:44:28 2015

     End  :   Thu Jun  4 06:44:28 2015

 

**** 06:44:28 Application Phase statistics:

              Elapsed time: 00:00:00 (in hh:mm:ss)

 

0014 QUIT;

 

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

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

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

 

**** 06:44:29 Logging off all sessions

**** 06:44:33 Total processor time used = '1.61 Seconds'

     .        Start : Thu Jun  4 06:44:21 2015

     .        End   : Thu Jun  4 06:44:33 2015

     .        Highest return code encountered = '4'.

**** 06:44:33 FDL4818 FastLoad Terminated

 

-- Chandu

dnoeth 4628 posts Joined 11/04
05 Jun 2015

Hi Chandu,
and now for something completely different :-)
 

0008 SET RECORD VARTEXT "»"  DISPLAY_ERRORS  NOSTOP;
     Total Error Table 1             =  0  ---- Table has been dropped
     Total Error Table 2             =  0  ---- Table has been dropped
    Total Rejected Rows             =  5

 

The error tables are both empty, those errors were read-errors and these are handled by the DataConnector. You need to set some options, e.g.:

 

RecordErrorFileName

RecordErrorVerbosity

TruncateColumnData

AcceptExcessColumns

AcceptMissingColumns

 

 

Dieter

ck185031 7 posts Joined 09/10
05 Jun 2015

I explored these DataConnector options. It will handle only less columns, more columns or excess column width.
But I'm looking at data issues like Null into Not Null column, char into integer/date column or invalid date value.
In these cases, it goes to loader and it fails the job when I set the ErrorLimt, also locks the target table and ET table.
In this situaltion, how can I locate the erroneous records when I can't access ET table nor it be displayed in log.

-- Chandu

dnoeth 4628 posts Joined 11/04
05 Jun 2015

Hi Chandu,
yes, but this was exactly the same problem for FastLoad, it didn't change with TPT.

Dieter

ck185031 7 posts Joined 09/10
05 Jun 2015

You are right.
The only difference/enhancement I see with TPT is, if we don't mention ErrorLimt it load the good records and bad ones goes to ET and no locking, but it doesn't fail the script however fastload fails.

-- Chandu

You must sign in to leave a comment.