16.10 - Example 1: 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)

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, see the Teradata Parallel Transporter User Guide (B035-2445).