16.20 - Recovering a Failed Update Operator - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
uah1527114222342.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval

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 Teradata Database
  • Sends the command to the Teradata Database to create the work table(s)
  • Sends the DML statement(s) to the Teradata 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 Teradata Database to start the acquisition phase
  • Sends data to the Teradata Database. The data is loaded into the work table(s)
  • Sends the command to the Teradata Database to end the acquisition phase

In the acquisition phase, the Teradata Database puts MultiLoad locks on the target table(s).

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

  • Sends the command to the Teradata Database to start the application phase
  • Sends the command to the Teradata Database to end the application phase

In the application phase, the Teradata Database performs the DML statement(s) on the target table(s) using the work table(s). When the application phase is complete, the Teradata Database releases the locks on the target table(s).

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(s) 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 are unable to correct the error and you want the load to get out of the paused state, follow these steps:

  1. Use a utility such as BTEQ.
  2. Log onto the Teradata Database.
  3. 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 table(s), work table(s), and error table(s).

    You need to drop the log table only once.

    The user should not drop the target table(s) unless they know what they are doing. The TPT Update operator is typically used to load populated tables. If the user drops the target table(s), they could lose a lot of valuable data.
  4. Verify the target table(s) can be selected.

    Use a utility such as BTEQ to select the target table(s).

    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

    If the original load job has more than one target table, repeat the sample request for the other target table(s)

To release Application Locks when the target table has fallback, follow these steps to release MultiLoad locks entirely.
  1. 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.
  2. Use DELETE < table_name > ALL to delete all of the rows in the table.
  3. 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.

  4. Fix the MultiLoad script, if needed.
  5. 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 table(s), error tables and log table, unless the user requested not to drop them

  • Logs off the sessions from the Teradata Database

Corrective Action:

  1. Use a utility such as BTEQ to drop the work table(s), error tables or log table. Skip this step if the user did not request to drop them.
  2. 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.