Correcting Errors - FastLoad

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.