Sample Scripts|DML - Sample Scripts - Parallel Data Pump

The following is an example using the SERIALIZEON keyword:

.LOGTABLE TPLOG01;
.LOGON <TDPID>/<USERID>,<PASSWORD>;
.BEGIN LOAD
   ERRLIMIT 100 50
   CHECKPOINT 15
   SESSIONS 20
   TENACITY 2
   ERRORTABLE TPERR01
   PACK 30
   ROBUST ON
   NOMONITOR;

.LAYOUT LAY01;
 .FIELD cc1 * CHAR(8);
 .FIELD cc2 * CHAR(4);
 .FIELD cc3 * CHAR(6);
 .FIELD cc4 * CHAR(62);
.DML LABEL LABEL01
 DO INSERT FOR MISSING UPDATE ROWS
 SERIALIZEON (CC1);
 UPDATE TPTBL01
 SET C4 = :CC4
 WHERE C1 = :CC1;
 INSERT TPTBL01 (C1, C2, C4)
 VALUES (:CC1,:CC2, CC4);

 UPDATE TPTBL02 SET C4 = :CC4 WHERE C1 = :CC1
                                AND C2 = :CC2;
 INSERT TPTBL02 (C1, C2, C3, C4)
          VALUES (:CC1,:CC2,:CC3, :CC4);
.IMPORT INFILE .\TPDAT.txt FORMAT TEXT
               LAYOUT LAY01
               APPLY LABEL01
               APPLY LABEL02;

.END LOAD;
.LOGOFF;

The following is an example using the USE keyword:

.LOGTABLE TPLOG01;
.LOGON <TDPID>/<USERID>,<PASSWORD>;

 DROP TABLE TPERR01;
 DROP TABLE TPTBL01;
 DROP TABLE TPTBL02;
 DROP TABLE TPTBL03;

CREATE TABLE TPTBL01(
  C1  INTEGER,
  C2  VARCHAR(30),
  C3  VARCHAR(30),
  C4  VARCHAR(30),
  C5  VARCHAR(30),
  C6  VARCHAR(30))
  UNIQUE PRIMARY INDEX (C1);

CREATE TABLE TPTBL02(
  C1  INTEGER,
  C2  VARCHAR(30),
  C3  VARCHAR(30),
  C4  VARCHAR(30),
  C5  VARCHAR(30))
  UNIQUE PRIMARY INDEX (C1);

CREATE TABLE TPTBL03(
  C1  INTEGER,
  C2  VARCHAR(30),
  C3  VARCHAR(30),
  C4  VARCHAR(30),
  C5  VARCHAR(30),
  C6  VARCHAR(30),
  C7  VARCHAR(30),
  C8  VARCHAR(30),
  C10 VARCHAR(30),
  C11 VARCHAR(30))
  UNIQUE PRIMARY INDEX (C1);

.BEGIN LOAD
   CHECKPOINT 15
   SESSIONS 5
   ERRORTABLE TPERR01
   NOMONITOR;

 .LAYOUT LAY01;
 .FIELD FLD1 * VARCHAR(10);
 .FIELD FLD2 * VARCHAR(10);
 .FIELD FLD3 * VARCHAR(10);
 .FIELD FLD4 * VARCHAR(15);
 .FIELD FLD5 * VARCHAR(20);
 .FIELD FLD6 * VARCHAR(25);
 .FIELD FLD7 * VARCHAR(30);
 .FIELD FLD8 * VARCHAR(30);
 .FIELD FLD9 * VARCHAR(1);
 .FIELD FLD10 * VARCHAR(30);
 .FIELD FLD11 * VARCHAR(30);

.DML LABEL LABEL01 USE(FLD1, FLD2, FLD4, FLD6, FLD8, FLD10);

 INSERT TPTBL01 (C1, C2, C3, C4, C5, C6)
          VALUES (:FLD1,:FLD2,:FLD4,:FLD6,:FLD8,:FLD10);

.DML LABEL LABEL02 USE(FLD1, FLD3, FLD5, FLD7, FLD11);

 INSERT TPTBL02 (C1, C2, C3, C4, C5)
          VALUES (:FLD1,:FLD3,:FLD5,:FLD7,:FLD11);

.DML LABEL LABEL03;

 INSERT TPTBL03 (C1, C2, C3, C4, C5, C6, C7, C8, C10, C11)
          VALUES (:FLD1, :FLD2, :FLD3, :FLD4, :FLD5,
                  :FLD6, :FLD7, :FLD8, :FLD10, :FLD11);

.IMPORT INFILE INDATA FORMAT VARTEXT ','
               LAYOUT LAY01
               APPLY LABEL01 WHERE FLD9 = 'A'
               APPLY LABEL02 WHERE FLD9 = 'B'
               APPLY LABEL03;

.VERSION;
.END LOAD;
.LOGOFF;

Note that as in the example, DML USE APPLYs can be mixed with DML APPLYs not using the USE keyword within the same IMPORT.

The following is an example using partitioning:

.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;