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:
.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.
.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.
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.