Adjusting the Preliminary Estimate - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-18
dita:mapPath
aim1544831946660.ditamap
dita:ditavalPath
gyk1507317446489.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

Make adjustments according to the requirements and expectations of the Teradata MultiLoad job to the preliminary estimate for one target table (84 times number of rows processed per the example in the preceding topic).

Adjusting the Preliminary Estimate 
Teradata MultiLoad Job Description
Has multiple target tables Repeat the preliminary estimate for each target table.
Is interrupted by a database restart There may be duplicate rows in Teradata MultiLoad work tables if the data placed there is not rolled back upon failure.

(These are later removed by the sort operation.)

Job uses fallback protection for the target table Multiply the estimated permanent space requirement by 2.
Uses journaling Add space for journals.

Permanent space for journaling is proportional to the number of rows updated.

To estimate the amount of space to add for journaling, multiply the estimated permanent space requirement by a factor of:

  • 1 for SN journaling (single before images, no after images)
  • 2 for SS journaling (single before images, single after images)
  • 2 for DN journaling (dual before images, no after images)
  • 2 for ND journaling (no before images, dual after images)
  • 3 for DS journaling (dual before images, single after images)
  • 3 for SD journaling (single before images, dual after images)
  • 4 for DD journaling (dual before images, dual after images)
Uses NUSIs Use the following formula to estimate the space required for changes to the NUSIs:
NUSIPERM = (index length + 66) * (number of changes to indexes) * ((1 for insert or delete) OR (2 for update))

This formula does not account for fallback tables. To account for fallback tables, multiply the NUSIPERM amount by 2.

The NUSIPERM space estimation would be further impacted by conditional APPLY expressions and by multiple NUSIs with different index lengths.

Writes to the error tables Add space requirements for the error tables—two per target table.

Teradata MultiLoad error tables have fallback protection and their size varies with the using data size specification.