Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 23 Jul 2004 @ 08:55:18 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Teradata Fastload error handling
 
From:   Avala, Srichand

I tried to simulate few error scenarios and noted the Hightest return code value. Teradata fastload returned zero always for almost all the scenarios. Kindly highlight user generated error scenarios for which Teradata fastload returns non zero value. Thanks in advance.

Source file sample data is as follows:-

     Q0440   440     73      Bridgestone     22      Bridgestone
     Q0440   440     18      Goodyear        38      Goodyear
     Q0440   440     21      Mitsubishi      4       Mitsubishi
     Q0440   440     13      BMW     30      BMW
     Q3103   3103    62      Michelin        33      Michelin
     Q3103   3103    59      Honda   6       Honda
     Q3103   3103    28      Other Cars      96      Other Cars
     Q3103   3103    27      Toyota  28      Toyota
     Q3103   3103    26      Ferrari 54      Ferrari
     Q3103   3103    25      Merc    52      Merc

The target table structure is as follows:-

     CREATE SET TABLE ETRAC.ANSWER_SOURCE ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           OriginalQuestionID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           QuestionID INTEGER,
           ExistingAnswerID INTEGER,
           ExistingAnswerDS VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
           NewAnswerID INTEGER,
           NewAnswerDS VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ( OriginalQuestionID ,QuestionID ,ExistingAnswerID ,NewAnswerID);

The working script is as follows:-

     .LOGON ETRAC/dbc,dbc;
     DATABASE ETRAC;
     .SET RECORD VARTEXT "   ";
     .DEFINE
     OriginalQuestionID(VARCHAR(30)),QuestionID(VARCHAR(6)),ExistingAnswerID(
     VARCHAR(6)),ExistingAnswerDS(VARCHAR(200)),
     NewAnswerID(VARCHAR(6)),NewAnswerDS) FILE=C:\EXCEL_ANSREF.TXT;
     .SHOW;
     .BEGIN LOADING ANSWER_SOURCE ERRORFILES
     ANSWER_SOURCE_ET,ANSWER_SOURCE_UV CHECKPOINT 1000;
     INSERT INTO ANSWER_SOURCE(OriginalQuestionID, QuestionID,
     ExistingAnswerID, ExistingAnswerDS, NewAnswerID, NewAnswerDS)
     VALUES (:OriginalQuestionID, :QuestionID, :ExistingAnswerID,
     :ExistingAnswerDS, :NewAnswerID, :NewAnswerDS(date));
     .END LOADING;
     .LOGOFF;

I tried to introduce errors into the script by making the following changes.

Note: The script changes were reverted after each change.

a)Mention table name, ANSWER_SOURCE1 in .BEGIN LOADING that does not exist in the database

     .BEGIN LOADING ANSWER_SOURCE1 ....

b)Introduced a NOT NULL column at the last position in the target for the same source file as follows:

     CREATE SET TABLE ETRAC.ANSWER_SOURCE ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           OriginalQuestionID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           QuestionID INTEGER,
           ExistingAnswerID INTEGER,
           ExistingAnswerDS VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
           NewAnswerID INTEGER,
           NewAnswerDS VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
           NEWCOLUMN INTEGER NOT NULL)
     UNIQUE PRIMARY INDEX ( OriginalQuestionID ,QuestionID ,ExistingAnswerID ,NewAnswerID);

c)Duplicated the unique primary index column in the source file by including a row at the beginning of the file as follows

     Q0440   440     73      Bridgestone     22      Bridgestone1

d)Introducted a data conversion error by modifying insert statement as follows

     INSERT INTO ANSWER_SOURCE(OriginalQuestionID, QuestionID,
     ExistingAnswerID, ExistingAnswerDS, NewAnswerID, NewAnswerDS)
     VALUES (:OriginalQuestionID (date), :QuestionID, :ExistingAnswerID,
     :ExistingAnswerDS, :NewAnswerID, :NewAnswerDS(date));

e)Specified field length size less than that in source file by changing

     QuestionID(VARCHAR(6)) to QuestionID(VARCHAR(2))
     .DEFINE
     OriginalQuestionID(VARCHAR(30)),QuestionID(VARCHAR(6)),ExistingAnswerID(
     VARCHAR(6)),ExistingAnswerDS(VARCHAR(200)),
     NewAnswerID(VARCHAR(6)),NewAnswerDS) FILE=C:\EXCEL_ANSREF.TXT;

regards,

Srichand



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023