Correcting Load Errors - Parallel Transporter

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

Retrieve the error information from the error tables on the Teradata Database.
Evaluate and correct the errors.
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 (B035-2414).

Acquisition Error Table

Use the Teradata PT 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 Teradata PT Error Table Extractor command, you can skip steps 1 and 2 and go to step 3 below.

For more information about the Teradata PT Error Table Extractor command, see "twbertbl" in the Teradata Parallel Transporter Reference (B035-2436).

If you do not use the Teradata PT 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:

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 ;
```
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 (B035-2436).

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.
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.outfile 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
```
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.
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
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.
Repeat steps 2 through 4 as required to resolve all of the ErrorTable1 error conditions.
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:

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  ;
```
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.
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
```
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.
Repeat steps 2 and 3 until all rows in the ErrorTable2 table are accounted for.
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
```

Recovering a Failed Load Operator

A failure can happen in these stages of the Load operator job:

Before the acquisition phase
During the acquisition phase
During the application phase
After the application phase

Before the Acquisition Phase

Before the acquisition phase, the Load operator does the following:

Verifies the value of the attributes in the job
Logs onto the Teradata Database
Sends the INSERT request to the Teradata Database

Corrective Action: The TPT job will display the error message. Correct the error and resubmit the job.

During the Acquisition or Application Phase

During the acquisition phase, the Load operator does the following:

Sends the command to the Teradata Database to start the acquisition phase
Sends data to the Teradata Database
Sends the command to the Teradata Database to end the acquisition phase

During the application phase, the Load operator does the following:

Sends the command to the Teradata Database to perform the application phase

When a failure occurs in the acquisition or application phase, the load is in the paused state. A paused state means the Teradata Database is waiting for the load to be restarted after the failure is fixed. The target table cannot be accessed while the load is paused.

Corrective Action: The TPT job displays the error message. Correct the error and resubmit the job.

If you are unable to correct the error and want the load to get out of the paused state without having to manually drop the target table, error tables, and log table, follow these steps:

Create a new TPT job using the Load operator as a standalone operator.
Here is a sample TPT job that uses the Load operator as a standalone operator:
```
DEFINE JOB FINISH_LOAD_OPERATOR_JOB
(
DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD STANDALONE
ATTRIBUTES
(
VARCHAR PrivateLogName = 'loadoper_privatelog',
INTEGER MaxSessions	=  1,
VARCHAR TargetTable	= 'MyTargetTable',
VARCHAR TdpId		= 'MyTdpId',
VARCHAR UserName	= 'MyUserName',
VARCHAR UserPassword	= 'MyPassword',
VARCHAR ErrorTable1	= 'MyErrorTable1',
VARCHAR ErrorTable2	= 'MyErrorTable2',
VARCHAR LogTable		= 'MyLogTable'
);
APPLY TO OPERATOR (LOAD_OPERATOR  [1] );
);
```
Modify the sample job as needed, such as the target table name, error table names, log table name, user name, password, and tdpid name. You do not need more than one session.

The target table name, error table names, log table name, user name, password, and tdpid name should be available in the original load job that failed.

Step 1 is the same step as performing BEGIN LOADING/END LOADING with FastLoad.

Step 1 is optional. The other option is to drop the target table, error tables, and log table and restart the job from the beginning. If you select the other option, skip step 2 and go to step 3.

Run the job that uses the Load operator as a standalone operator.

When the job is running, the load will complete without sending more data.

Here is a sample output:

Teradata Parallel Transporter Version 16.10.00.00
Job log: /opt/teradata/client/16.10/tbuild/logs/test-1.out
Job id is test-1, running on MyMachine
Teradata Parallel Transporter Load Operator Version 16.10.00.00
LOAD_OPERATOR: private log specified: loadoper_privatelog
LOAD_OPERATOR: connecting sessions
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: entering Acquisition Phase
LOAD_OPERATOR: entering Application Phase
LOAD_OPERATOR: Statistics for Target Table: 'MyTargetTable'
LOAD_OPERATOR: Total Rows Sent To RDBMS: 0
LOAD_OPERATOR: Total Rows Applied: 0
LOAD_OPERATOR: Total Rows in Error Table 1: 0
LOAD_OPERATOR: Total Rows in Error Table 2: 0
LOAD_OPERATOR: Total Duplicate Rows: 0
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '0.32 Second(s)'
LOAD_OPERATOR: Start : Thu Jul 21 14:33:27 2016
LOAD_OPERATOR: End : Thu Jul 21 14:33:33 2016
Job test completed successfully
Job start: Thu Jul 21 14:33:12 2016
Job end: Thu Jul 21 14:33:33 2016

The row count for the target table and error tables might contain some rows, depending on how far the acquisition phase progressed before the failure.

Verify the target table can be selected.
Use a utility such as BTEQ to select the target table.

Here is a sample request to select the target table:
```
SELECT COUNT(*) FROM <MyTargetTable>;
```
You should get the row count, which means the load is no longer in the paused state.

The result should not return the following Teradata Database error:
```
RDBMS 2652: Operation not allowed: <table name> is begin Loaded.
```

After the Application Phase

After the application phase, the Load operator does the following:

Drops the error tables and log table, unless the user requested they not be dropped
Logs off the sessions from the Teradata Database

Corrective Action:

Use a utility such as BTEQ to drop the error tables or log table. Skip this step if the user did not request to drop them.
If the sessions remain on the Teradata Database, ask the system administrator to delete the sessions; or, wait for 20 minutes. After 20 minutes, the sessions are logged off by the Teradata Database Gateway.
You do not need to resubmit the job.