16.20 - Using Operator Templates - 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

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 ($).

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            = @DDLUserName,
     VARCHAR UserPassword        = @DDLUserPassword,
     VARCHAR TdpId               = @DDLTdpId,
     VARCHAR AccountId           = @DDLAccountId,
     VARCHAR WorkingDatabase     = @DDLWorkingDatabase,
     VARCHAR LogonMech           = @DDLLogonMech,
     VARCHAR LogonMechData       = @DDLLogonMechData,
     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. All template attribute names are prefixed with a unique string, 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:

Teradata PT Operator Templates Job Variable Name Prefix 
Template Operator Name Standard Operator Type Job Variable Name Prefix
$DATACONNECTOR_CONSUMER DATACONNECTOR CONSUMER 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 DDLLogonMech, for example, is not assigned any value when a script using the $DDL template is executed, then its attribute declaration:

VARCHAR LogonMech = @DDLLogonMech,

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.