15.10 - Recovering a Failed Load Operator - Parallel Transporter

Teradata Parallel Transporter User Guide

prodname
Parallel Transporter
vrm_release
15.10
category
User Guide
featnum
B035-2445-035K

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:

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

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

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

    2 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 15.10.00.00
    Job log: /opt/teradata/client/15.10/tbuild/logs/test-1.out
    Job id is test-1, running on MyMachine
    Teradata Parallel Transporter Load Operator Version 15.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 : Tue Jan 21 14:33:27 2014
    LOAD_OPERATOR: End   : Tue Jan 21 14:33:33 2014
    Job test completed successfully
    Job start: Tue Jan 21 14:33:12 2014
    Job end:   Tue Jan 21 14:33:33 2014

    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.

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

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

    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.