16.10 - Example 2: Delete Task Option - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

The following example accomplishes the same purpose as the previous Delete Task example, but with a slightly different technique. Rather than hard-coding values in the deletion criterion, the value in this example is supplied from an external file by the DataConnector operator and a data stream. The SQL host variable (:CustNo) represents the value in the DELETE statement.

In this case, the Update operator, used as a Delete Task, is a consumer operator because it receives input from a data stream. Differences between this approach and the first example are shown in bold text.

DEFINE JOB DELETE_TASK_PARAM
DESCRIPTION 'External File DELETE FROM CUSTOMER TABLE'
(
DEFINE SCHEMA CUST_NUM_SCHEMA
DESCRIPTION 'CUSTOMER NUMBER SCHEMA'
(
Cust_Num INTEGER
);

  DEFINE OPERATOR UPDATE_OPERATOR
  DESCRIPTION 'Teradata PT UPDATE OPERATOR'
  TYPE UPDATE
  SCHEMA CUST_NUM_SCHEMA
  ATTRIBUTES
  (
    VARCHAR TargetTable = 'Customer',
    VARCHAR TdpId = @Tdpid,
    VARCHAR UserName = @Userid,
    VARCHAR UserPassword = @Pwd,
    VARCHAR AccountId,
    VARCHAR LogTable = 'DeleteTask_log',
    VARCHAR DeleteTask = 'Y'
  );

DEFINE OPERATOR DATA_PRODUCER
DESCRIPTION 'DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA CUST_NUM_SCHEMA
ATTRIBUTES
(

VARCHAR OpenMode = 'Read',
VARCHAR Format = 'Formatted',
VARCHAR IndicatorMode,
VARCHAR FileName =  'Single_Row_File'
);
APPLY
(
 'DELETE FROM CUSTOMER WHERE CUSTOMER_NUMBER LT :CustNo')

TO OPERATOR (UPDATE_OPERATOR [1])

SELECT * FROM OPERATOR  (DATA_PRODUCER[1]);

);