- Data Manipulation Language (DML): Insert, Update, Delete, Upsert, Merge, and Select
- Data Control Language (DCL): Give, Grant, and Revoke
- Data Definition Language (DDL): Create, Drop, Alter, Modify, Delete Database, Delete User, and Rename
OS Commands
Use the OS Command operator to send commands to the operating system on the client system
Delimited Data
Delimited data are variable-length text records with each field or column separated by one or more delimiter characters. Delimited data are also known as VARTEXT.
Use the Data Connector operator to read or write delimited data
Large Object Data Types
There are two kinds of large object data type:
• Character large object (CLOB)
• Binary large object (BLOB)
• Three operators support the CLOB and BLOB data types.
• The Inserter operator can insert CLOB and BLOB data types into a Teradata table
• The Selector operator can export CLOB and BLOB data types from a Teradata table
• The Data Connector operator can read/write CLOB and BLOB data types from/to a file.
Selecting the wrong operator to process the CLOB or BLOB data type terminates the job.
TPT script Structure
Building TPT Scripts
TPT uses a SQL-like scripting language for extract, basic transformation, and load functions. This easy-to-use language is based on SQL, making it familiar to most database users. All operators use the same scripting language. This represents an improvement over the individual utilities, each of which has its own unique scripting language. A single script can be used to define multiple operators and schemas to create complex extracting and loading jobs.
There are only a few statements that are needed to build a TPT script. A quick look at the basic statements can be seen here:
DEFINE JOB
Defines the overall job and packages together all following DEFINE and APPLY statements.
DEFINE SCHEMA
Defines the structure of a data object in terms of columns of specific data types. A given schema definition can be used to describe multiple data objects. Also, multiple schemas can be defined in a given script.
DEFINE OPERATOR
Defines a specific TPT operator to be used in this job.
DEFINE DBMS
Defines an instance of a database server to be used in this job.
APPLY
A processing statement used to initiate a TPT load, update, or delete operation.
Generally TPT script has two sections
1) Declarative section
2) Executable section
Declarative section—all schema and operator definitions
Define the job name for the script
Provide the description of job
Schema Definition
Operator Definition #1
….
Operator Definition #n
Executable section - -
Specifies all sql statements processing that is extract, load, filter, delete and update done by Apply definition
It also requires a job Variable file.
The structure of TPT script is as mentioned below
Script example
DEFINE JOB FILE_LOAD
DESCRIPTION 'Load a Teradata table from a file'
(
/* TPT declaration section/*
DEFINE SCHEMA Trans_n_Accts_Schema
(
Account_Number VARCHAR(50),
Trans_Number VARCHAR(50),
Trans_Date VARCHAR(50),
Trans_ID VARCHAR(50),
Trans_Amount VARCHAR(50)
);
DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log',
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA Trans_n_Accts_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = @jobvar_datafiles_path,
VARCHAR FileName = 'accounts.txt',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = '|'
);
DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'load_log',
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR TargetTable = @jobvar_tgt_dbname || '.Trans',
VARCHAR LogTable = @jobvar_wrk_dbname || '.LG_Trans',
VARCHAR ErrorTable1 = @jobvar_wrk_dbname || '.ET_Trans',
VARCHAR ErrorTable2 = @jobvar_wrk_dbname || '.UV_Trans'
);
STEP Setup_Tables
(
/* TPT Executation section/*
APPLY
('DROP TABLE ' || @jobvar_wrk_dbname || '.ET_Trans;'),
('DROP TABLE ' || @jobvar_wrk_dbname || '.UV_Trans;'),
('DROP TABLE ' || @jobvar_tgt_dbname || '.Trans;'),
('CREATE TABLE ' || @jobvar_tgt_dbname
|| '.Trans (Account_Number VARCHAR(50),
Trans_Number VARCHAR(50),
Trans_Date VARCHAR(50),
Trans_ID VARCHAR(50),
Trans_Amount VARCHAR(50));')
TO OPERATOR (DDL_OPERATOR);
);
STEP Load_Trans_Table
(
APPLY
('INSERT INTO ' || @jobvar_tgt_dbname || '.Trans(Account_Number,
Trans_Number,
Trans_Date,
Trans_ID,
Trans_Amount)
VALUES(:Account_Number,
:Trans_Number,
:Trans_Date,
:Trans_ID,
:Trans_Amount);')
TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[2]);
);
);
Note: Example, Job Variables can be maintained in separate variables file or we can pass directly
Job execution
tbuild -f <script file name> -z <checkpoint interval>
The -z option sets the checkpoint interval to the number of seconds specified.
SET CHECKPOINT INTERVAL 160 SEC
Or
SET CHECKPOINT INTERVAL 12 MINUTES
The checkpoint interval can be specified in a job script between the last DEFINE statement
and the APPLY statement(s).
tbuild
We have seen the tbuild command in many of the previous examples. This command is used to initiate a TPT job. The following key options may be used with tbuild:
-f Specifies the filename to be used as input.
-u Specifies job variable values which are to be applied.
-z Specifies a checkpoint interval to be used for the client side.
-s Specifies that job execution is to start at a specific job step.
-v Specifies that job attributes are to be read from an external file.
-l Specifies latency interval - how often to flush stale buffers.
-n Specifies that the job should continue, even if a step return code is greater than 4
Note: If the checkpoint interval is specified both in the job script and with the tbuild -z command option, the -z option takes precedence.
Troubleshooting a Failed Job
Common Job Failures and Remedies
There are two categories of job failures. The evaluation and correction of each type of failure must be handled differently:
• Some jobs fail at launch, during execution of the tbuild statement, but before the initial job step have run.
• Some jobs launch successfully, and one or more job steps may execute successfully, but thejob fails to run to completion.
The following sections describe common errors encountered by Teradata PT jobs.
- When the Job Fails to Begin Running.
- When a job is launched but fails to begin execution, the associated errors appear in the public log. Errors are detected according to the launch sequence:
1. Teradata PT first processes the options specified in the tbuild command. If it detects tbuild command errors, the job stops.
Error types encountered: tbuild command errors
2 If Teradata PT encounters no tbuild command errors, it then parses the job script and creates a parallel job execution plan that will perform the operations specified in the APPLY
statement(s) in the job script.
Errors types encountered:
- Pre processor errors -- Incorrect use of job variables or the INCLUDE directive.
- Job script compilation errors -- Syntactic and semantic errors.
3.Only when script compilation is successful and the execution plan has been generated does the Teradata PT allocate resources for and launch the various internal tasks required to execute the job plan
Errors types encountered: System resource errors
The following common types of tbuild errors may occur at job launch:
- User errors
- executing the tbuild command
- Script compiler errors
- System resource errors
- semaphore errors
- socket errors
- shared memory errors
