Computing the Restart Log Table Requirements - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2018-10-10
dita:mapPath
lsl1527114222348.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

The space requirements of a Teradata MultiLoad restart log table are highly dependent on the specifics of the job. Although there are mandatory inserts for every Teradata MultiLoad job, others occur on a job-dependent basis.

Following is an example of the table definition for a Teradata MultiLoad restart log table, which is automatically created by Teradata MultiLoad:

CREATE TABLE SFD.samplelog ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      LogType INTEGER,
      Seq INTEGER,
      ReqRC INTEGER,
      ReqType INTEGER,
      ReqLen INTEGER,
      ReqMsg VARCHAR(255),
      SysInfo VARBYTE(255),
      MiscInt1 INTEGER,
      MiscInt2 INTEGER,
      MiscInt3 INTEGER,
      MiscInt4 INTEGER,
      MiscInt5 INTEGER,
      MiscInt6 INTEGER,
      MiscInt7 INTEGER,
      MiscInt8 INTEGER,
      MLoadSeq INTEGER DEFAULT 0 ,
      MLoadImpSeq INTEGER,
      MLoadSrcSeq INTEGER,
      MLoadCkpt VARBYTE(255),
      RunDate DATE DEFAULT DATE ,
      RunTime FLOAT DEFAULT TIME )
      UNIQUE PRIMARY INDEX( LogType ,Seq ,MLoadSeq );

From the table definition, the size of each row depends on the VARCHAR length, which can range from 0 to a maximum of 255:

  • If the VARCHAR length is 0, then the size of each row is 82 bytes.
  • If the VARCHAR length is 255, then the size of each row is 847 bytes.

The possible inserts for this table are as follows:

  • Seven mandatory inserts:
    • Three inserts, one each, upon entry to the DML phase, the acquisition phase, and the application phase
    • One insert to mark the end of a Teradata MultiLoad task
    • Three inserts, one each, to mark the dropping of the ET and UT error tables, and the WT work tables
  • One insert for each Teradata SQL statement processed (for example, CREATE TABLE, DROP TABLE)
  • One insert for each input data checkpoint

Using these guidelines provides an estimate of 260 KB of space required for the example restart log table:

  • 574 bytes: For mandatory entries: 82 bytes * 7 = 574 bytes
  • 2,541 bytes: For Teradata SQL request – maximum of 847 bytes multiplied by an average of 3: 847 * 3 = 2,541 bytes
  • 127,050 bytes: For checkpoints – assuming a 15 million row Teradata MultiLoad job, with checkpoints every 100,000 rows, for an average of 150 checkpoints: 847 bytes * 150 = 127,050 bytes
  • 130,165 bytes: For the fallback table, duplicating the size of the restart log table itself: 574 bytes + 2,541 bytes + 127,050 bytes = 130,165 bytes
  • 260,330 bytes: Total estimated restart log table space requirement

These calculations estimate that 260 K of space would probably suffice for the restart log table of the example job. Use a similar computation to estimate the requirements for the Teradata MultiLoad job.