Example – Job Steps - Parallel Transporter

The following example shows a job with a single APPLY statement using job step syntax:

DEFINE JOB LOADPROD
DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'
(
   DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
   DESCRIPTION 'PRODUCT INFORMATION'
   (
      PRODUCT_NAME        VARCHAR(24),
      PRODUCT_CODE        INTEGER,
      PRODUCT_DESCRIPTION VARCHAR(512),
      PRODUCT_COST        INTEGER,
      PRODUCT_PRICE       INTEGER
   );

   DEFINE OPERATOR DDL_OPERATOR
   TYPE DDL
   ATTRIBUTES
   (
      VARCHAR TdpId           = 'MYDATABASE',
      VARCHAR UserName        = 'MYUSER',
      VARCHAR UserPassword    = 'MYPASSWORD'
   );

   DEFINE OPERATOR LOAD_OPERATOR
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
   TYPE LOAD
   SCHEMA *
   ATTRIBUTES
   (
      INTEGER TenacityHours   = 0,
      INTEGER TenacitySleep   = 0,
      INTEGER BufferSize      = 16,
      INTEGER MaxSessions     = 1,
      INTEGER MinSessions     = 1,
      INTEGER ErrorLimit      = 1,
      VARCHAR TdpId           = 'MYDATABASE',
      VARCHAR UserName        = 'MYUSER',
      VARCHAR UserPassword    = 'MYPASSWORD',
      VARCHAR AccountId       = 'MYACCT',
      VARCHAR WorkingDatabase = 'SALES',
      VARCHAR TargetTable     = 'SALES_TABLE',
      VARCHAR LogTable        = 'SALES.SALES_TABLE_LOG',
      VARCHAR ErrorTable1     = 'SALES.SALES_TABLE_ERROR1',
      VARCHAR ErrorTable2     = 'SALES.SALES_TABLE_ERROR2'
   );

   DEFINE OPERATOR DATACONN
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATACONNECTOR OPERATOR'
   TYPE DATACONNECTOR PRODUCER
   SCHEMA PRODUCT_SOURCE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR FileName      = 'sales_data.txt',
      VARCHAR OpenMode      = 'Read',
      VARCHAR Format        = 'FORMATTED',
      VARCHAR IndicatorMode
   );
   Step Setup_Tables
   (
      APPLY
        ('DROP   TABLE SALES.SALES_TABLE_LOG;'),
        ('DROP   TABLE SALES.SALES_TABLE_ERROR1;'),
        ('DROP   TABLE SALES.SALES_TABLE_ERROR2;'),
        ('DROP   TABLE SALES.SALES_TABLE;'),
        ('CREATE TABLE SALES.SALES_TABLE (NAME        VARCHAR(24),
                                           CODE        INTEGER,
                                           DESCRIPTION VARCHAR(512),
                                           COST        INTEGER,
                                           PRICE       INTEGER);')

      TO OPERATOR (DDL_OPERATOR)
   );

   Step Load_Table
   (
      APPLY ('INSERT INTO SALES_TABLE (:PRODUCT_NAME,
                                       :PRODUCT_CODE,
                                       :PRODUCT_DESCRIPTION,
                                       :PRODUCT_COST,
                                       :PRODUCT_PRICE);')
      TO OPERATOR (LOAD_OPERATOR [3])

      SELECT * FROM (OPERATOR DATACONN);
   );
);