TPT Update Operator

Pradeep

TPT Update Operator

Update operator is a data consumer operator which works similar to native Teradata Mload utility. Teradata advises to use TPT Update over native Teradata Mload utility as it is better optimised for performance over native load and also old utilities are not enhanced in new version.

TPT Update Operator Characteristics:

Import/load data from a file or Teradata database table to empty or populated Teradata table.
Upto 5 empty/populated tables can be loaded at a time but delete can be done from single table only.
Update operator does not discard duplicate rows, it will be inserted into target table if target table is MULTISET and it will be inserted into error table if target table is SET.
Target tables will be locked until update opertor job is finished.
Teradata DML statements like Insert,update,upsert,and deletes can be executed.
Update operator's import job can be paused but delete job cannot be paused.
DeleteTask attribute is required to make Update operator script work as standalone.
```
Attribute DeleteTask   = 'Y'
```
Five file formats are supported for import: Delimited, Text, Unformatted, Formatted and Binary. For detail of each file format, please click here
Careful! always define maximum number of sessions(using MaxSessions) that can be allocated to the job, else it may end up taking all the sessions.

Example 1: Simple Update Operator example

DEFINE JOB Import_Employee_Data
Description 'Test Update script'
(
    DEFINE OPERATOR update_operator
    TYPE UPDATE
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='dept_tpt_stream_log',
        VARCHAR DateForm='AnsiDate',
        INTEGER MaxSessions=6
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    (
        emp_no INTEGER,
        ename_name VARCHAR(50),
        hire_date ANSIDATE
    );

    DEFINE OPERATOR Producer_File_Detail
    TYPE DATACONNECTOR PRODUCER
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
        VARCHAR DirectoryPath='/Users/username/Desktop/tpt',
        VARCHAR FileName='emp_formatted.out',
        VARCHAR Format='FORMATTED',
        VARCHAR OpenMode='Read',
        VARCHAR DateForm='AnsiDate'
    );
    
    APPLY
    (
       'INSERT INTO tutorial_db.emp_unformatted(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);'
    )
    TO OPERATOR(update_operator)
    
    SELECT * FROM OPERATOR (Producer_File_Detail);
);

Example 2: Delete Task using Update Operator example

DEFINE JOB Import_Employee_Data
Description 'Test Update script'
(
    DEFINE OPERATOR update_operator
    TYPE UPDATE
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='dept_tpt_stream_log',
        INTEGER MaxSessions=4,
        VARCHAR DeleteTask   = 'Y'

    );
        
    APPLY
       'DELETE FROM tutorial_db.emp_unformatted;'
    TO OPERATOR(update_operator);
);

Note: DeleteTask attribute is required to make above example standalone, otherwise it will fail with below error

TPT13114: Error: In the current job, the Update operator cannot run as a standalone operator. Please make sure your  APPLY-SELECT statement has the SELECT clause.

Executing TPT Update Operator

Example 1: Executing simple TPT Update script
```
tbuild -f tpt_update_formatted.tpt
```
Example 2: Executing TPT Delete task as standalone operator
```
tbuild -f tpt_update_delete_task.tpt
```

Download script files

Teradata Parallel Transporter (TPT) Update is one of the teradata utility to load data into empty or populated teradata tables.

TPT Update Operator