PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Multiload – Teradata Utility Error Handling

The Teradata Multiload utility provides the capability to perform batch maintenance on tables (insert, update, delete and upsert). It loads data from external sources and provides the capability to restart jobs interrupted by errors, exceptions and failures.

A Multiload job executes in 5 phases:

1. Preliminary

  • Parses and validates all of the MultiLoad commands and Tera-data SQL statements in your MultiLoad job
  • Establishes sessions and process control with the Teradata Database
  • Submits special Teradata SQL requests to the Teradata Data-base
  • Creates and protects temporary work tables and error tables in the Teradata Database

2. DML Transaction

  • Submits the DML statements specifying the insert, update, and delete tasks to the Teradata Database

3. Acquisition

  • Imports data from the specified input data source
  • Evaluates each record according to specified application con-ditions
  • Loads the selected records into the worktables in the Tera-data Database
  • There is no acquisition phase activity for a MultiLoad delete task.

4. Application

  • Acquires locks on the specified target tables and views in the Teradata Database
  • For an import task, inserts the data from the temporary work tables into the target tables or views in the Teradata Data-base
  • For a delete task, deletes the specified rows from the target table in the Teradata Database
  • Updates the error tables associated with each MultiLoad task

5. Cleanup

  • Forces an automatic restart/rebuild if an AMP went offline and came back online during the application phase
  • Releases all locks on the target tables and views
  • Drops the temporary work tables and all empty error tables from the Teradata Database
  • Reports the transaction statistics associated with the import and delete tasks.

Multiload returns the returns codes described previously (0, 4, 8, 12) to the operating system or calling program.

Restarts

It is possible to restart a Multiload job in any of the phases after an er-ror or failure has interrupted an executing Multiload job. There are differences in the restart procedures in some of the phases that will be described. It is also possible to rerun an interrupted Multiload job in some of the phases which will also be discussed.

A Multiload job creates a restart log table that must be present for a job to be restarted and must be dropped before a job can be rerun. It creates a work table for each input source. These tables must be pre-sent for a job to restart and must be dropped to rerun a job. Multiload also creates two error tables for each target table that must be present for a restart and that must be dropped to rerun a Multiload job.

A Multiload job can not be restarted or rerun if these tables are not present for an interruption that occurs during the APPLICATION PHASE.

Checkpoint frequency can be set for checkpoint to occur after a num-ber of records have been read from an input source. The checkpoints are taken during the acquisition phase. A restart after an interruption in the acquisition phase will start reading the input source at the point following the last checkpoint executed before the interruption.

Interruptions that occur during the application phase will be restarted based on internal checkpoints created by the database.

Rerun

Multiload jobs that are interrupted before the application phase starts or after the application phase ends may be rerun. Jobs that are inter-rupted during the application phase must be restarted.

To rerun an entire Multiload job the following actions must be taken before the job is rerun:

  1. Use BTEQ to execute RELEASE MLOAD command for each target table in Multiload script
  2. Drop work table for each target table
  3. Drop acquisition error table for each target table
  4. Drop application error table for each target table
  5. Drop restart log table

These actions can be executed by a BTEQ SQL script that is run as part of a Multiload script or run by an external job scheduling system.

The following is an example of a SQL script that will allow this example to be rerun.

1
2
3
4
5
6
7
8
9
10
11
12
.logon test/testuser,test
 
database test;
 
release mload tran;
drop table uv_tran;
drop table et_tran;
drop table wt_tran;
drop table log_tran;
 
.logoff
.quit

This script can be executed as part of a Multiload script that executes this script with BTEQ and then executes the Multiload job as illustrated in the following Multiload script.

1
2
3
4
.system 'bteq < c:\DataIntegration\scripts\tranreset.sql';
.run file c:\DataIntegration\scripts\tranload.ml;
 
.logoff;

This script can always be run when the intent is to run the entire Multi-load job.

Add a Comment

Your email address will not be published. Required fields are marked *