Correcting Load Errors - Parallel Transporter

Teradata Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2445
lifecycle
previous
Product Category
Teradata Tools and Utilities

Correcting Load Errors

Though the procedures are somewhat different depending on the error table in question, use the following procedure to correct load errors:

1 Retrieve the error information from the error tables on the Teradata Database.

2 Evaluate and correct the errors.

3 Insert the corrected records into the Load TargetTable.

Because the Load operator accesses only an empty table, after the job is complete you must use a utility, such as BTEQ, to access the Teradata Database. The following procedures and examples assume that BTEQ is running and that you are logged on to the Teradata Database.

For more information about using BTEQ, see the Basic Teradata Query Reference.

Acquisition Error Table

Use the TeradataPT Error Table Extractor command to extract the error information from the Load operator's acquisition phase error table when the schema for the Load operator job contains all VARCHAR columns.

If you use the TeradataPT Error Table Extractor command, you can skip steps 1 and 2 and go to step 3 below.

For more information about the TeradataPT Error Table Extractor command, see "twbertbl" in the Teradata Parallel Transporter Reference.

If you do not use the TeradataPT Error Table Extractor command, use the following procedure to correct errors recorded in the acquisition phase error table, which is defined by the ErrorTable1 attribute:

1 Use the following Teradata SQL statement to retrieve the error code and field name for each error in the first error table, where etname1 is the name you specified for the ErrorTable1 error table:

SELECT ErrorCode, ErrorFieldName FROM etname1 ORDER BY ErrorCode ;

Note: If the operator definition does not specify a name for the ErrorTable1 attribute, the error table will be named <TargetTableName>_ET by default. For details, see the chapter on Load operator in Teradata Parallel Transporter Reference.

The BTEQ response is 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 the messages reference documentation for your operating system environment.
  • 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 your client system:

  • If the values in the ErrorCode column indicate that a constraint violation error occurred, retrieve the DataParcel information in record mode:
  •    .SET RECORDMODE ON
       .EXPORT DATA FILE=err.out
       SELECT DataParcel FROM etname1
  • 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 etname1

    3 Use the ErrorCode and ErrorFieldName information returned in step 1 and the DataParcel information returned in step 2 to determine which records you want to correct and reload to the Teradata Database.

    The methods that you can use to correct the individual error conditions will vary depending on the number and types of errors encountered.

    4 After correcting the errors, use the following BTEQ commands and Teradata SQL statements to insert the corrected records into the Load table on the Teradata Database:

  • BTEQ IMPORT command to transfer the data to the Teradata Database
  • Teradata SQL USING modifier to define the fields in each record
  • Teradata SQL INSERT statement to insert a record into the Load table
  • Notice:
    Do not reference the first two bytes in the INSERT statement for data records 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 ErrorTable1 table is defined as a variable-length field, the first two bytes always indicate the length. If you do not reference this field 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 ErrorTable1 error conditions.

    6 After you resolve all errors, drop the ErrorTable1 table from the Teradata Database.

    Application Error Table

    Use the following procedure to correct errors recorded in the application error table, which is defined by the ErrorTable2 attribute:

    1 Use the following Teradata SQL statement to retrieve all rows from the second error table, where ttname_UV is the name of the second error table and cname is the unique primary index for the table:

       SELECT * FROM ttname_UV ORDER BY cname ;

    Note: Use ttname_UV for the default name of ErrorTable2, If the operator definition specifies a name for the ErrorTable2 attribute, the SELECT statement shown above must contain use the name specified.

    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 Load TargetTable that has a primary index value identical to a row retrieved from the second error table, where ttname is the name of the Load TargetTable, cname is the index of the Load TargetTable, and errorvalue is the index value retrieved from the second error table:

       SELECT * FROM ttname WHERE cname = errorvalue

    3 Compare the rows selected from the ErrorTable2 with the rows selected from the TargetTable and determine which is correct:

  • If ErrorTable2 is correct, use one of the following:
  • DELETE statement to delete the incorrect row from the TargetTable.
  • INSERT statement to insert the correct row.
  • If TargetTable is correct, use the DELETE statement to delete the corresponding row from the ErrorTable2 table.
  • 4 Repeat steps 2 and 3 until all rows in the ErrorTable2 table are accounted for.

    5 Using BTEQ, drop the ErrorTable2 table from the Teradata Database after you resolve all of the errors, where etname2 is the name of the second error table:

       DROP TABLE etname2