- 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.
- 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
- 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:
- 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.
- 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. - Repeat steps 2 through 4 as required to resolve all of the errortname1 error conditions.
- Drop the errortname1 table from the database after all of the errors have been resolved.
Use the following procedure to correct errors recorded in the error table specified as errortname1: