Example 2: Delete Task Option - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2436
lifecycle
previous
Product Category
Teradata Tools and Utilities

Example 2: Delete Task Option

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]);
 
);

Explanation

Notice the following in this script:

  • When using this approach, the DataConnector operator write a single row to the data stream so its input file contains a single record.
  • A schema must be defined in order for the input value to be read.
  • An SQL host variable (:CustNo) is used in the DELETE statement.
  • The colon ( : ) symbol prefixed to CustNo specifies to the Teradata Database that the value comes from a source external to the SQL DELETE statement.