17.00 - APPLY Example - Parallel Data Pump

Teradata® Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-3021-220K
.BEGIN LOAD SESSIONS 34;
 .LAYOUT EQTTB535;
 .FIELD Pool_Upd_Code     * CHAR(01);
 .FIELD Eqmt_Init         * CHAR(04);
 ....
 .DML LABEL UPSERTAC
    DO INSERT FOR MISSING UPDATE ROWS;
 UPDATE EQTDBT50.EQTTB535_TAL SET
  TCS_POOL_IDFR_NUM =:TCS_POOL_IDFR_NUM
  .....
 WHERE
  .....
  ;
 INSERT INTO EQTDBT50.EQTTB535_TAL
 VALUES(
  POOL_EXPN_DATE    =:POOL_EXPN_DATE (DATE, FORMAT 'YYYYMMDD')
  .....
 );
 .DML LABEL UPSERTDL;
 UPDATE EQTDBT50.EQTTB535_TAL SET
  .....
 WHERE
  .....
  ;
 .IMPORT INFILE INFILE
         LAYOUT EQTTB535
         APPLY  UPSERTAC WHERE (POOL_UPD_CODE = 'C'
                            OR  POOL_UPD_CODE = 'A')
         APPLY  UPSERTDL WHERE  POOL_UPD_CODE = 'D'
  ;
 .END LOAD;
 /* For the upsert:                    */
 /* (first statement in .DML UPSERTAC) */
 /* make sure we have the 50 updates   */
 .IF &IMP1_UPSERTAC_1 <> 50 THEN
 .LOGOFF 100;

 /* ... and 50 inserts                  */
 /* (second statement in .DML UPSERTAC) */
 .IF &IMP1_UPSERTAC_2 <> 50 THEN
 .LOGOFF 101;

 /* And for the plain update:          */
 /* (first statement in .DML UPSERTDL) */
 /* we should have 10 of these.        */
 .IF &IMP1_UPSERTDL_1 <> 10 THEN

 .LOGOFF 102;

 .LOGOFF;