Writing Job Scripts for Reusability and Manageability
Teradata PT employs a single scripting language for extracting and loading data Teradata also provides:
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.