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: