Recovering a Failed Load Operator
A failure can happen in these stages of the Load operator job:
Before the Acquisition Phase
Before the acquisition phase, the Load operator does the following:
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:
During the application phase, the Load operator does the following:
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
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  );
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:
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.