Example 1: Delete Task Option - Parallel Transporter

Example 1: Delete Task Option

Following is an example of a job that uses the Delete Task option to delete rows from a table named Customer, where customer_number is less than the hard-coded job script value of 100000:

DEFINE JOB DELETE_TASK 

DESCRIPTION 'Hard-coded DELETE FROM CUSTOMER TABLE' 

(

  DEFINE OPERATOR UPDATE_OPERATOR 

  DESCRIPTION 'Teradata PT UPDATE OPERATOR'   

  TYPE UPDATE 

  SCHEMA * 

  ATTRIBUTES 

  (

    VARCHAR TargetTable  = 'Customer', 

    VARCHAR TdpId        = @Tdpid, 

    VARCHAR UserName     = @Userid, 

    VARCHAR UserPassword = @Pwd, 

    VARCHAR AccountId, 

    VARCHAR LogTable     = 'DeleteTask_log', 

    VARCHAR DeleteTask   = 'Y'

  ); 

  APPLY 

  ( 

   'DELETE FROM CUSTOMER WHERE CUSTOMER_NUMBER LT 100000;'

  ) 

  TO OPERATOR( UPDATE_OPERATOR [1] );

);

Notice the following about this script:

Setting the attribute DeleteTask to 'Y' makes this execution of the Update operator a Delete Task.

The example uses the hard-coded value of 100000 in the deletion criterion.

For increased flexibility, this value could be specified as a job variable, such as:

'DELETE FROM CUSTOMER WHERE CUSTOMER_NUMBER LT ' || @Custno || ';'

The value for the variable in this expression can come from a job variable file, or it can come from the command line:

tbuild -f <filename> -u "Custno = '100000'"

The script still requires the SCHEMA * clause even though the Update operator is functioning as a standalone operator.

The APPLY statement must specify a single SQL DELETE statement.

The LogTable attribute is always required for the Update operator.

For additional information about using variables Teradata Parallel Transporter User Guide.