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

Teradata Parallel Transporter User Guide

prodname
Parallel Transporter
vrm_release
15.10
category
User Guide
featnum
B035-2445-035K

Writing Job Scripts for Reusability and Manageability

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 “Simplifying Scripts with Operator Templates and Generated Schemas” on page 229.
  • 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 Chapter 12: “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 : 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 : 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: Job Script with Simplified Syntax” on page 260 shows, 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.

    Value in Using 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 “Setting Up the Job Variables Files” on page 72.

    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