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

Teradata® Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-3021-220K

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 the following is true:
  • 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 );