Correct Errors in the First Error Table | Teradata FastLoad - 17.10 - Procedure for Correcting Errors in the First Error Table - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
17.10
Published
June 2021
Last Update
2021-07-01
Content Type
Programming Reference
Publication ID
B035-2411-061K
Language
English (United States)
Use the following procedure to correct errors recorded in the error table specified as errortname1:
  1. Retrieve the error code and field name for each error in the first error table:

    SELECT ErrorCode, ErrorFieldName FROM errortname1 ORDER BY ErrorCode ;

    where errortname1 is the name specified for the first error table.

    BTEQ responds with a list of the error codes and the associated field names, formatted as follows:
    ***Query completed. 2 rows found. 2 columns returned.
    ***Total elapsed time was 1 second.
    ErrorCode ErrorFieldName
    --------- ------------------
       2679   A
       2679   A

    The values listed in the ErrorCode column are database return codes for each error condition, as specified in Teradata Vantage™ - Database Messages, B035-1096.

    The values listed in the ErrorFieldName column are the names of the fields that caused each error.

  2. Use the following BTEQ commands and Teradata SQL statements to retrieve the data records for each error in the first error table and store them in the specified err.out file on the client system:
    • If the values in the ErrorCode column indicate that either a constraint violation or a conversion error occurred, retrieve the DataParcel information in record mode:
      .SET RECORDMODE ON
      .EXPORT DATA FILE=err.out
      SELECT DataParcel FROM errortname1

      In record mode, the database returns the SELECT statement results in client computer format, which is usually a hexadecimal dump. Thus, if all of the fields identified in the Teradata FastLoad DEFINE commands were also specified in the INSERT statements, the data retrieved from the Teradata FastLoad error table is in the same format as it was in the original input data source.

    • Otherwise, if the values in the ErrorCode column indicate that the errors were all caused by unavailable AMP conditions, do not use the RECORDMODE command:
      .EXPORT DATA FILE=err.out
      SELECT DataParcel FROM errortname1
  3. Use the ErrorCode and ErrorFieldName information returned in Step 1 and the DataParcel information returned in Step 2 to determine which records to correct and reload to the database. The methods used to correct the individual error conditions will vary, depending on the number and types of errors.
  4. After the errors have been corrected, use the following BTEQ commands and Teradata SQL statements to insert the corrected records into the Teradata FastLoad table on the database.
    To Use the
    transfer the data to the database BTEQ IMPORT command
    define the fields in each record Teradata SQL USING modifier
    insert a record into the Teradata FastLoad table Teradata SQL INSERT statement
    Do not reference the first two bytes in the INSERT statement for data records that were exported from the database in record mode. Instead, make the first field (variable parameter) in the USING modifier a dummy SMALLINT field.
    When selecting data in record mode, the variable-length columns are all preceded by a two-byte field whose value indicates the length of the data field. But, because the DataParcel column of the errortname1 table is defined as a variable-length field, the first two bytes always indicate the length. If this field is not referenced in the INSERT statement, the database ignores this portion of each record in the input data.
  5. Repeat steps 2 through 4 as required to resolve all of the errortname1 error conditions.
  6. Drop the errortname1 table from the database after all of the errors have been resolved.