17.00 - Example: PARTITION - 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

A sample script that uses partitioning follows:

.LOGTABLE TPLOG01;
.LOGON <TDPID>/<USERID>,<PASSWORD>;
 DROP TABLE TPTBL01;
 DROP TABLE TPTBL02;
 DROP TABLE TPERR01;

 CREATE TABLE TPTBL01, FALLBACK(
  C1  CHAR(12) not null,
  C2  CHAR(8) not null)
  PRIMARY INDEX (C1);
 CREATE TABLE TPTBL02, FALLBACK(
  C1  CHAR(12),
  C2  CHAR(8),
  C3  CHAR(6))
  UNIQUE PRIMARY INDEX (C1);

.BEGIN LOAD
   ERRLIMIT 100 50
   CHECKPOINT 15
   TENACITY 2
   ERRORTABLE TPERR01
   ROBUST off
   serialize on
   ;

 .LAYOUT LAY02;
 .FIELD cc1 * CHAR(12) key;
 .FIELD cc2 * CHAR(8);
 .FIELD cc3 * CHAR(6);
 .filler space1 * char(1);

.partition part1  pack 10 sessions 10;
.partition part2 sessions 5 1 packmaximum;

.DML LABEL LABEL01 partition part1
 DO INSERT FOR MISSING ROWS
 ignore extra update rows
   use(cc1, cc2);

 UPDATE TPTBL01
 SET C2 = :CC2
 WHERE C1 = :CC1;
 INSERT TPTBL01 (C1, C2)
 VALUES (:CC1,:CC2);

.DML LABEL LABEL02 partition part2
 serializeon( cc1 )
 ignore extra update rows
 DO INSERT FOR MISSING UPDATE ROWS;

 UPDATE TPTBL02 SET C2 = :CC2 WHERE C1 = :CC1;
 INSERT TPTBL02 (C1, C2, C3)
          VALUES (:CC1,:CC2,:CC3);

.IMPORT INFILE TpumpData001.txt FORMAT TEXT
               LAYOUT LAY02
               APPLY LABEL01
               APPLY LABEL02 where CC2 = '00000001';

.END LOAD;
.LOGOFF;