Why TPT is successful with return code 0, even when records goes to ET table?

Teradata Applications
Enthusiast

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
Tags (2)
11 REPLIES
Junior Contributor

Re: Why TPT is successful with return code 0, even when records goes to ET table?

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.

Enthusiast

Re: Why TPT is successful with return code 0, even when records goes to ET table?

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
Junior Contributor

Re: Why TPT is successful with return code 0, even when records goes to ET table?

Hi Chandu,

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

Enthusiast

Re: Why TPT is successful with return code 0, even when records goes to ET table?

Thanks Dieter. Appreciate your help.

~ Chandu
Enthusiast

Re: Why TPT is successful with return code 0, even when records goes to ET table?

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
Junior Contributor

Re: Why TPT is successful with return code 0, even when records goes to ET table?

Hi Chandu,

where in the FastLoad log did you find that information?

Only the number of errors is returned.

Enthusiast

Re: Why TPT is successful with return code 0, even when records goes to ET table?

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
Junior Contributor

Re: Why TPT is successful with return code 0, even when records goes to ET table?

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

Enthusiast

Re: Why TPT is successful with return code 0, even when records goes to ET table?

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