Protection and Location of Teradata TPump Database Objects - Parallel Data Pump

Teradata Parallel Data Pump Reference

Product
Parallel Data Pump
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-3021
lifecycle
previous
Product Category
Teradata Tools and Utilities

Protection and Location of Teradata TPump Database Objects

The restart log table is critical to the recovery process. If the restart log table is dropped, there is no way to recover an interrupted Teradata TPump job.

In addition to the restart log table, Teradata TPump also creates an error table and a number of macros (where each macro corresponds to a DML SQL statement involved in current IMPORT). If these database objects are dropped, they can, with some effort, being recreated. However, it is much more convenient for this NOT to be necessary.

Teradata TPump does not have special locks that it places on database objects. It is important that administrators take security precautions to avoid the loss of these objects.

If the objects are dropped accidentally, the following information should allow an administrator to recreate them.

Teradata TPump macros are placed in the same database that contains the log restart table.

The macros are named according to the following convention:

Jobname_DDD_SSS

where

  • Jobname is the job name, which, if not explicitly specified, defaults to: MYYYYMMDD_HHMMSS_LLLLL.
  • LLLLL is the low‑order 5 digits of the logon sequence number returned by the database from the .LOGON command.
  • DDD is the number of the .DML sequence (ordinal) number. This value is not reset to one for successive loads (.BEGIN LOAD) in a single job, but continues to be incremented.
  • SSS is the SQL statements sequence (ordinal) number within the .DML group.
  • Thus, given the following script fragment:

    .LOGTABLE LT_SIGH;
    .LOGON TDPID/CME,CME;
    ...
    .LAYOUT LAY1A
    ...
    .DML LABEL TAB1PART1;
     
    INSERT into tab1 values (:F0,:F1,:F2,:F3);
    .DML LABEL TAB2PART1;
    INSERT into tab2 values (:F0,:F1,:F2,:F3);
    ...
    .IMPORT INFILE TPDAT  
                   LAYOUT LAY1A
                   APPLY TAB1PART1
                   APPLY TAB2PART1;

    and assuming the job name is defaulted, the macros are named:

    M20020530_171209_06222_001_001 and M20020530_171209_06222_002_001.

    The contents of a Teradata TPump macro is taken directly from the script and consists of a parameter clause derived from the LAYOUT and the actual statement which is specified in the script. Continuing the example above, if the LAYOUT associated with the statement is as follows:

    .LAYOUT LAY1A;
    .FIELD   F0 * integer key;
    .FIELD   F1 * integer;
    .FIELD   F2 * integer;
    .FILLER  FX * integer;
    .FIELD   F3 * char(38);

    then the macros will be created as follows:

    CREATE MACRO CME.M20020530_171209_06222_001_001 (
    F0 (INTEGER), F1 (INTEGER), F2 (INTEGER), F3 (CHAR(38))
    ) AS (INSERT INTO TAB1 VALUES(:F0, :F1, :F2, :F3);
    );
    CREATE MACRO CME.M20020530_171209_06222_002_001 (
    F0 (INTEGER), F1 (INTEGER), F2 (INTEGER), F3 (CHAR(38))
    ) AS ( INSERT INTO TAB2 VALUES(:F0, :F1, :F2, :F3);
    );

    Note that the actual names of the parameters in the parameter list are not important; however, what is important is that the types of the parameters are specified in the macro in exactly the same order as the types in the LAYOUT. Also important is the fact that FILLER fields are not included in the parameter list since they are stripped out by Teradata TPump.

    The error table, if it is not explicitly specified, is:

    <JobName>_nnn_ET

    Where nnn is the load sequence number.

    If the database for the error table is not explicit in the script, the table is placed in the database associated with the Teradata TPump user logon, unless the DATABASE command has been issued.

    Continuing the above example, assuming the user defaults the error table, then the create table command for it will be:

    CREATE SET TABLE M20020530_171209_06222_001_ET, 
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL
         (
          ImportSeq BYTEINT,
          DMLSeq BYTEINT,
          SMTSeq BYTEINT,
          ApplySeq BYTEINT,
          sourceseq INTEGER,
          DataSeq BYTEINT,
          ErrorCode INTEGER,
          ErrorMsg VARCHAR(255) CHARACTER SET UNICODE NOT CASESPECIFIC,
          ErrorField SMALLINT,
          HostData VARBYTE(63677))
    UNIQUE PRIMARY INDEX ( ImportSeq ,DMLSeq ,SMTSeq ,ApplySeq ,sourceseq ,
    DataSeq );