16.20 - Writing Job Scripts for Reusability and Manageability - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
uah1527114222342.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval

Teradata PT employs a single scripting language for extracting and loading data Teradata also provides:

  • A simplified version of its scripting language, called “Simplicity,” that uses pre-defined reusable operator templates easy to maintain. Using pre-defined operator templates vastly reduces the number of lines of code you have to write. Job schemas are generated dynamically based on the source or target table. For information on simplified Teradata PT scripts, see Simplifying Scripts with Operator Templates and Generated Schemas.
  • A utility called Easy Loader that allows you to run a Teradata PT load job using a command line interface. This makes it unnecessary to write a job script. For information on Easy Loader, see Teradata PT Easy Loader.

Using the Simplicity Job Script

Simplicity features include operator templates that are imported into the job script when operators are referenced in an APPLY/SELECT statement, as shown in the following code excerpt:

APPLY 'INSERT INTO TABLE_X (:col1, :col2);'
TO OPERATOR ($LOAD)
SELECT * FROM OPERATOR ($FILE_READER);

By referencing the $LOAD and $FILE_READER operator template names, Teradata PT knows the Load template to import into a job script. If reducing the amount of code for loading or exporting and simplifying job maintenance is important to you, use the simplified job script method.

The following two examples illustrate loading a delimited file into a Teradata table:

Example 1: Job Script with Simplified Syntax

DEFINE JOB PLOAD_JOB
DESCRIPTION 'PLOAD JOB'
(
  /* Use the schema of the TargetTable for TPT_SCHEMA */
DEFINE SCHEMA TPT_SCHEMA FROM TABLE DELIMITED @LoadTargetTable;

  APPLY $INSERT @LoadTargetTable TO OPERATOR ($LOAD [@LoadInstances])
  SELECT * FROM OPERATOR ($FILE_READER(TPT_SCHEMA) [@ReaderInstances]);
);

Example 2: Job Script without Simplified Syntax

DEFINE JOB PLOAD_JOB
DESCRIPTION 'PLOAD JOB'
(
  DEFINE SCHEMA TPT_SCHEMA
  DESCRIPTION 'TPT SCHEMA'
  (
    COL001    VARCHAR(100),
    COL002    VARCHAR(100),
    COL003    VARCHAR(100)
  );

  DEFINE OPERATOR LOAD_OPERATOR
  DESCRIPTION 'TPT Load Operator'
  TYPE LOAD
  SCHEMA *
  ATTRIBUTES
  (
    VARCHAR TdpId              = @LoadTdpId,
    VARCHAR UserName           = @LoadUserName,
    VARCHAR UserPassword       = @LoadUserPassword,
    VARCHAR TargetTable        = @LoadTargetTable,
    VARCHAR LogTable           = @LoadLogTable,
    VARCHAR ErrorTable1        = @LoadErrorTable1,
    VARCHAR ErrorTable2        = @LoadErrorTable2,
    VARCHAR PrivateLogName     = @LoadPrivateLogName
  );

  DEFINE OPERATOR FILE_READER_OPERATOR
  DESCRIPTION 'TPT DataConnector Producer Operator'
  TYPE DATACONNECTOR PRODUCER
  SCHEMA TPT_SCHEMA
  ATTRIBUTES
  (
    VARCHAR FileName           = @FileReaderFileName,
    VARCHAR Format             = @FileReaderFormat,
    VARCHAR OpenMode           = @FileReaderOpenMode,
    VARCHAR TextDelimiter      = @FileReaderTextDelimiter,
    VARCHAR MultipleReaders    = @FileReaderMultipleReaders,
    VARCHAR PrivateLogName     = @FileReaderPrivateLogName
  );

  APPLY
  (
    'INS ' || @LoadTargetTable || ' (
     :COL001,
     :COL002,
     :COL003
   );'
 TO OPERATOR (LOAD_OPERATOR [@LoadInstances])
 SELECT *
 FROM OPERATOR ( FILE_READER_OPERATOR [@ReaderInstances]);
);

As Example 1 above shows (Job Script with Simplified Syntax), using templates eliminates the need to write DEFINE OPERATOR statements for operators in the job script itself. The reference to the job variable @LoadTargetTable in the DEFINE SCHEMA statement allows Teradata PT to generate the job schema at runtime based on the value provided for the job variable.

The Value of Using a Job Variables File

Using job variables defined in a job variables file allows the job script compiler to assign values to job variables at script execution time rather than having to have you code values as constants in every script. Each job variable in a script should be prefixed with an @ sign so that the script compiler replaces each variable with the corresponding value when the Teradata PT job executes.

Teradata PT allows unlimited variable substitution in a script, maximizing the reusability of scripts across systems. Moreover, using a job variables file allows multiple jobs to share a common set of variable values.

For information on a job variables file, see Setting Up the Job Variables Files.

The Advantages of a Simplicity Script

  • Smaller and simpler job scripts
  • Less job script maintenance with the use of operator templates
  • Scripts that can be customized by using user-defined templates
  • Generated schema objects
  • Generated SQL insert statements
  • Standardized job variable names corresponding to operator attributes