Recovering a Failed Update Operator

Recovering a Failed Update Operator - Parallel Transporter

Teradata® Parallel Transporter User Guide - 17.20

Deployment

VantageCloud

VantageCore

Edition

Enterprise

IntelliFlex

Lake

VMware

Product

Parallel Transporter

Release Number

17.20

Published

June 2022

Language

English (United States)

Last Update

2023-08-25

dita:mapPath

uzp1645128359760.ditamap

dita:ditavalPath

tvt1507315030722.ditaval

dita:id

B035-2445

Product Category

Teradata Tools and Utilities

A failure can happen in these stages of the Update 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 Update operator does the following:

Verifies the value of the attributes in the job
Logs onto the database
Sends the command to the database to create the work tables
Sends the DML statements to the database

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

During the Acquisition or Application Phase

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

Sends the command to the database to start the acquisition phase.
Sends data to the database. The data is loaded into the work tables.
Sends the command to the database to end the acquisition phase.

In the acquisition phase, the database puts MultiLoad locks on the target tables.

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

Sends the command to the database to start the application phase
Sends the command to the database to end the application phase

In the application phase, the database performs the DML statements on the target tables using the work tables. When the application phase is complete, the database releases the locks on the target tables.

When a failure occurs in the acquisition or application phase, the load is in the paused state. A paused state means the database is waiting for the load to be restarted after the failure is fixed. The target tables can be accessed when the failure occurred in the acquisition phase; it cannot be accessed when the failure occurred in the application phase.

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

If you cannot correct the error and you want the load to get out of the paused state, follow these steps:

Use a utility such as BTEQ.
Log onto the database.
Enter these commands in the specified sequence:
```
RELEASE MLOAD <Target Table 1>;
DROP TABLE <Work Table 1>;
DROP TABLE <Error Table 1>;
DROP TABLE <Error Table 2>;
DROP TABLE <Log Table>;
```
The target table name, work table name, error table names, and log table name are in the original load job that failed.

The RELEASE MLOAD command releases the MultiLoad locks on the target table.

If the original load job has more than one target table, repeat the commands in step 3 for the other target tables, work tables, and error tables.

You need to drop the log table only once.

The user should not drop the target tables unless they know what they are doing. The TPT Update operator is typically used to load populated tables. If the user drops the target tables, valuable data can be lost.
Verify the target tables can be selected.
Use a utility such as BTEQ to select the target tables.

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 error:
```
RDBMS 2652: Operation not allowed: <table name> is begin Loaded
```
If the original load job has more than one target table, repeat the sample request for the other target tables.

To release Application Locks when the target table has fallback, follow these steps to release MultiLoad locks entirely.

Use the RELEASE MLOAD <table_name > IN APPLY statement to change the application locks to the restoration lock.
- This query returns: Failure 7745 Mload table table_name cannot be released because fallback exists.
- However, the application lock does get changed to restoration lock.
Use DELETE <table_name> ALL to delete all of the rows in the table.
Use RELEASE MLOAD < table_name > IN APPLY to free the table.
This query removes the MLOAD lock entirely. The table is freed from this point.
Fix the MultiLoad script, if needed.
Run the MultiLoad job from the beginning.

For more information on the RELEASE MLOAD command, see RELEASE MLOAD.

After the Application Phase

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

Drops the work tables, error tables and log table, unless the user requested not to drop them
Logs off the sessions from the database

Corrective Action:

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