15.00 - Implications of Dropping Required Teradata MultiLoad-Created Tables - MultiLoad

Teradata MultiLoad Reference

prodname
MultiLoad
vrm_release
15.00
category
Programming Reference
featnum
B035-2409-034K

Implications of Dropping Required Teradata MultiLoad-Created Tables

Teradata MultiLoad creates four tables that are required for restarting a paused Teradata MultiLoad job:

  • Restart Log Table
  • Work Table
  • Acquisition Error Table
  • Application Error Table
  • If any of these tables are dropped while a Teradata MultiLoad job is paused, restarting the job normally is not an option, and the target tables can be left corrupted.

    In some limited circumstances, restart such a job using special procedures is possible but, in most cases, do not drop these tables for a paused job without careful analysis of the situation.

    The following topics describe the operational implications of having dropped the required restart tables for a paused Teradata MultiLoad job.

    Restart Log Table

    If the restart log table is dropped, a restart of the paused job is normally not possible by simply resubmitting it. The recovery procedure depends on whether:

  • The job entered the pause mode before or during the application phase
  • Any of the other required restart tables have been dropped
  • If the job paused before the application phase, then restart the job by:

  • Dropping the work table and error tables
  • Using BTEQ to enter a RELEASE MLOAD statement
  • Resubmitting the paused job as though it were a new one
  • If the job paused during the application phase, and the work and error tables have not been dropped, then restart the job. If the work and error tables have been dropped, the job cannot restart and the target tables must be manually restored.

    In either case, follow the After a Job Abort or Client System Failure instructions in “Restarting a Paused Teradata MultiLoad Job” on page 43.

    Work Table

    If the work table is dropped, a restart of the paused job is normally not possible. The recovery procedure depends on whether the job entered the pause mode before or during the application phase. Also keep in mind:

  • If the operations were only INSERT, then it may be possible to resubmit the paused job as a new job, although duplicate unique primary index or duplicate row errors may occur.
  • If the operations were only DELETE, then it may be possible to resubmit the paused job as a new job, although missing row errors may occur.
  • Note: Be very careful with MULTISET tables. This procedure could produce the wrong number of duplicate rows.

  • If the operations were only UPDATE, then it may or may not be possible to resubmit the paused job as a new job. If the update operation was setting the values of one or more columns without reference to original values in any columns, resubmitting the job will usually succeed. If, however, the update operation referenced any original column values (for example, to increment a column value, or multiply a column value by some constant), then resubmitting the job will fail.
  • If the operations were a combination of INSERT, DELETE, UPDATE, then consider the collective effect of the insert, delete, and update operations.
  • Acquisition Error Table

    If the acquisition error table is dropped, a restart of the paused job is not possible without special procedures. The recovery procedure depends on whether the job entered the pause mode before or during the application phase.

  • If the job paused before the application phase, then recover it by:
  • Dropping the restart log table, the work table, and the application error table
  • Using BTEQ to enter a RELEASE MLOAD statement
  • Resubmitting the job as a new one
  • If the job paused during the application phase, then it may be possible to recover by creating a “dummy” acquisition error table. Technical assistance from Teradata technical support may be required, and even if the recovery succeeds, any information about prior acquisition phase errors will have been lost.
  • Application Error Table

    If the application error table is dropped, a restart of the paused job is not possible without special procedures. The recovery procedure depends on whether the job entered the pause mode before or during the application phase.

  • If the job paused before the application phase, then recover by:
  • Dropping the restart log table, the work table, and the acquisition error table
  • Using BTEQ to enter a RELEASE MLOAD statement
  • Resubmitting the job as a new one
  • In some cases, a recovery is possible by creating a “dummy” application error table.

    Technical assistance from Teradata technical support is available.

  • If the job paused during the application phase, recovery is possible by creating a “dummy” application error table. Technical assistance from Teradata technical support is available. If the recovery succeeds, any information about prior application phase errors is lost.