15.10 - Simplifying Scripts with Operator Templates and Generated Schemas - Parallel Transporter

Teradata Parallel Transporter User Guide

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

Using Operator Templates

You can simplify your job script and reduce its size by using operator templates. An operator template is a stored DEFINE OPERATOR statement that is automatically imported into your job script when you reference in an APPLY statement a standard Teradata PT-supplied operator by its template name, as shown in the following example:

APPLY  'INSERT INTO TABLE_X ( :col1, ..., :coln );'
    TO OPERATOR( $LOAD() ) ... ;

In this example, $LOAD is the template name of the standard Teradata PT-supplied Load operator. The DEFINE OPERATOR statement for the $LOAD operator is stored in template file $LOAD.txt in the Teradata PT template directory. Template operator names are the standard operator types prefixed by the dollar sign ($), unless a shorter yet still descriptive name exists.

The following table lists the operator templates that Teradata PT supplies. All operator templates are located in the Teradata PT Install directory in the template directory and begin with the dollar sign ($).

 

Table 16: Teradata PT Operator Templates 

Template Operator Name

Standard Operator Type

Template File Name

$DATACONNECTOR_
CONSUMER

DATACONNECTOR CONSUMER

$DATACONNECTOR_
CONSUMER.txt

$DATACONNECTOR_
PRODUCER

DATACONNECTOR PRODUCER

$DATACONNECTOR_
PRODUCEF

$DDL

DDL

$DDL.txt

$DELETER

UPDATE STANDALONE

$DELETER.txt

$EXPORT

EXPORT

$EXPORT.txt

$FE_OUTMOD

FASTEXPORT OUTMOD

$FE_OUTMOD.txt

$FILE_READER

DATACONNECTOR PRODUCER

$FILE_READER.txt

$FILE_WRITER

DATACONNECTOR CONSUMER

$FILE_WRITER.txt

$FL_INMOD

FASTLOAD INMOD

$FL_INMOD.txt

$INSERTER

INSERTER

$INSERTER.txt

$LOAD

LOAD

$LOAD.txt

$ML_INMOD

MULTILOAD INMOD

$ML_INMOD.txt

$ODBC

ODBC

$ODBC.txt

$OS_COMMAND

OS COMMAND

$OS_COMMAND.txt

$SCHEMAP

SCHEMAMAPPER

$SCHEMAP.txt

$SELECTOR

SELECTOR

$SELECTOR.txt

$STREAM

STREAM

$STREAM.txt

$UPDATE

UPDATE

$UPDATE.txt

Using an operator template is like storing your own DEFINE OPERATOR statement in a separate file and then importing the file into your job script using a Teradata PT INCLUDE directive, except that when you use an operator template, you do not need to code any INCLUDE directive.

Since the DEFINE OPERATOR statement for a template is not in your script when you code it, you do not have normal access to its operator attribute declarations and cannot assign job-scope default values to these attributes. To make them accessible to you, all operator attributes that are defined for each standard operator are declared in its template definition and assigned conventionally-named job variables as their job-scope default values.

The following, for example, is the template definition for the DDL operator:

 DEFINE OPERATOR $DDL
   DESCRIPTION 'Teradata Parallel Transporter DDL Operator'
   TYPE DDL
   ATTRIBUTES
   (
     VARCHAR UserName            = @TargetUserName,
     VARCHAR UserPassword        = @TargetUserPassword,
     VARCHAR TdpId               = @TargetTdpId,
     VARCHAR AccountId           = @TargetAccountId,
     VARCHAR WorkingDatabase     = @TargetWorkingDatabase,
     VARCHAR LogonMech           = @TargetLogonMech,
     VARCHAR LogonMechData       = @TargetLogonMechData,
     VARCHAR DataEncryption      = @DDLDataEncryption,
     VARCHAR ARRAY ErrorList     = @DDLErrorList,
     VARCHAR LogSQL              = @DDLTargetLogSQL,
     VARCHAR PrivateLogName      = @DDLPrivateLogName,
     VARCHAR QueryBandSessInfo   = @DDLQueryBandSessInfo,
     VARCHAR ReplicationOverride = @DDLReplicationOverride,
     VARCHAR TraceLevel          = @DDLTraceLevel
   );

Notice that all DDL Operator attributes can be assigned values via job variables for the execution of any job script that references the $DDL template. The names of these job variables are formed from the corresponding attribute names as follows:

  • Logon attribute names, that is, attributes associated with logging on to Teradata Database, such as UserName and UserPassword, are prefixed with 'Source' in producer templates and 'Target' in consumer and standalone templates.
  • All other template attributes names are prefixed with a unique name, either the corresponding operator type or a short name or mnemonic that is easily associated with the template.
  • This template job variable naming convention ensures that assigned job variables for any template cannot inadvertently affect attribute values of other referenced templates.

    The following table lists the job variable name prefix for each of the standard operator template supplied by Teradata PT:

     

    Table 17: Teradata PT Operator Templates Job Variable Name Prefix 

    Template Operator Name

    Standard Operator Type

    Job Variable Name Prefix

    $DATACONNECTOR_
    CONSUMER

    DATACONNECTOR
    CONSUMMER

    DCC

    $DATACONNECTOR_
    PRODUCER

    DATACONNECTOR PRODUCER

    DCP

    $DDL

    DDL

    DDL

    $DELETER

    UPDATE STANDALONE

    Deleter

    $EXPORT

    EXPORT

    Export

    $FE_OUTMOD

    FASTEXPORT OUTMOD

    FEOutmod

    $FILE_READER

    DATACONNECTOR PRODUCER

    FileReader

    $FILE_WRITER

    DATACONNECTOR CONSUMER

    FileWriter

    $FL_INMOD

    FASTLOAD INMOD

    FLInmod

    $INSERTER

    INSERTER

    Inserter

    $LOAD

    LOAD

    Load

    $ML_INMOD

    MULTILOAD INMOD

    MLInmod

    $ODBC

    ODBC

    ODBC

    $OS_COMMAND

    OS COMMAND

    OSCommand

    $SCHEMAP

    SCHEMAMAPPER

    Smap

    $SELECTOR

    SELECTOR

    Selector

    $STREAM

    STREAM

    Stream

    $UPDATE

    UPDATE

    Update

    When no value has been assigned to the job variable of any particular template attribute, Teradata PT interprets the attribute as having been declared without a job-scope default value assignment. If the $DDL job variable TargetLogonMech, for example, is not assigned any value when a script using the $DDL template is executed, then its attribute declaration:

    VARCHAR LogonMech = @TargetLogonMech,

    will be interpreted by Teradata PT as if it had been declared in the $DDL template as

       VARCHAR LogonMech,

    Of course, like the attributes of script-defined operators, attributes of a template operator can always be assigned values at the place in the APPLY statement where the template is referenced. Such assignments definitively determine the attribute values for that specific invocation of the template operator, whether or not the corresponding job variables were assigned values, as shown below:

       SELECT *
       FROM OPERATOR
       (
         $EXPORT() 
         ATTR
         (
           PrivateLogName = 'weekly_sample.log',
           SelectStmt     = 'Select * from Weekly_Trans;'
         )
       )

    In the above script extract, the values assigned to attributes PrivateLogName and SelectStmt are those that this particular execution of the Export Operator will use, even if the corresponding job variables in the $EXPORT template definition have different values. Attribute value specification works the same way for template operators as it does for script-defined operators.

    Assigning template attribute values for specific template references in an APPLY statement will even be necessary if a job script contains more than one reference to a given template and a single set of job variable assignments will not work for all template references.