TPT DDL Operator

Pradeep

TPT DDL Operator

Teradata Parallel Transporter (TPT) provides a way to executes any of sql statement inside Teradata session using TPT standalone DDL Operator.

TPT DDL Operator Characteristics:

This is a standalone operator which means it is neither a consumer or producer operator.
DDL Operator can use only single teradata session.
All type of SQL statements can be executed except SHOW, HELP and simple SELECT.
If multiple statements(as in example 1) are given in a single 'APPLY' then DDL operator will combine those statements into one single multistatement and sends it to the Teradata as one implicit transaction.
Multiple instances are not supported in SELECT Operator, if specified then it will fail with below error.
```
Producer_Query_Detail: TPT10327: Multiple instances are not supported
```
Size of sql statements can be upto 1 MB for DDL Operator.
Any Data values used in this operator should be hardcoded.

Example 1: Simple DDL Operator example

DEFINE JOB DDL_Operator
DESCRIPTION 'Running DDL Commands'
  (
      DEFINE OPERATOR ddl_commands
      TYPE DDL
      ATTRIBUTES
      (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password'
      );
     APPLY (
           'DELETE FROM tutorial_db.emp_unformatted;',
           'INSERT INTO tutorial_db.emp_unformatted  select distinct * from tutorial_db.emp_formatted;'
           ) 
           TO OPERATOR (ddl_commands);  
);

Example 2: DDL Operator example with Multiple Steps

DEFINE JOB DDL_Operator
DESCRIPTION 'Running DDL Commands'
  (
      DEFINE OPERATOR ddl_commands
      TYPE DDL
      ATTRIBUTES
      (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password'
      );

     STEP CLEANUP 
     (
           APPLY (
           'DELETE FROM tutorial_db.emp_unformatted;'
           ) 
           TO OPERATOR (ddl_commands);
     );
     STEP REPOPULATE 
     (
           APPLY (
           'INSERT INTO tutorial_db.emp_unformatted  select distinct * from tutorial_db.emp_formatted;'
           ) 
           TO OPERATOR (ddl_commands);
     );
);

Executing TPT DDL Operator

Example 1: Executing simple TPT DDL script
```
tbuild -f tpt_ddl.tpt
```
Example 2: Executing TPT DDL with steps script
```
tbuild -f tpt_ddl_with_steps.tpt
```

Download script files