15.00 - Correcting Errors - FastLoad

Teradata FastLoad Reference

prodname
FastLoad
vrm_release
15.00
category
Programming Reference
featnum
B035-2411-034K

Correcting Errors

Though the procedures are somewhat different, depending on the error table, correcting errors is a three‑step process, where:

1 The error information is retrieved from the error tables on the Teradata Database.

2 Errors are evaluated and correct ed.

3 Corrected records are inserted into the Teradata FastLoad table.

After the job has completed, another utility, such as BTEQ, must be used to access the Teradata Database, because Teradata FastLoad only operates on an empty table. The procedures and examples in the following subsections are performed under BTEQ. They assume BTEQ has been invoked and logged on to the Teradata Database. For more information about using BTEQ, see the Basic Teradata Query Reference (B035‑2414).

Procedure for Correcting Errors in the First Error Table

Use the following procedure to correct errors recorded in the error table specified as errortname1:

1 Use the following Teradata SQL statement to 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 the Teradata Database return codes for each error condition, as specified in 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 Teradata 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 Teradata 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 Teradata Database.

     

    To

    Use the

    transfer the data to the Teradata 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

    Note: Do not reference the first two bytes in the INSERT statement for data records that were exported from the Teradata 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 Teradata 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 Teradata Database after all of the errors have been resolved.

    Procedure for Correcting Errors in the Second Error Table

    Use the following procedure to correct errors recorded in the error table specified as errortname2:

    1 Use the following Teradata SQL statement to retrieve all rows from the second error table:

    SELECT * FROM errortname2 ORDER BY cname ;

    where:

     

    Syntax Element

    Description

    cname

    Unique primary index for the table

    errortname2

    Name of the second error table

    The BTEQ response is a list of the contents of the second error table, ordered by the values in the primary index column.

    2 Use the following Teradata SQL statement to retrieve each row from the Teradata FastLoad table that has a primary index value identical to a row retrieved from the second error table:

    SELECT * FROM tname WHERE cname = errorvalue

    where:

     

    Syntax Element

    Description

    cname

    Index of the Teradata FastLoad table

    errorvalue

    index value retrieved from the second error table

    tname

    Name of the Teradata FastLoad table

    3 Compare the rows selected from the error table with the rows selected from the Teradata FastLoad table and determine which is correct.

  • If the row selected from the error table is correct, then use a Teradata SQL DELETE statement to delete the incorrect row from the Teradata FastLoad table, and an INSERT statement to insert the correct row.
  • If the row selected from the Teradata FastLoad table is correct, then use a Teradata SQL DELETE statement to delete the corresponding row from the error table.
  • 4 Repeat Steps 2 and 3 until all rows in the error table are accounted for.

    5 Drop the errortname2 table from the Teradata Database after all errors have been resolved.