Sample Scripts|DML - サンプル スクリプト - Parallel Data Pump

SERIALIZEONキーワードの使用例を以下に示します。

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

USEキーワードの使用例を以下に示します。

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

この例のように、USEキーワードを使用しないDML APPLYとDML USE APPLYを同じIMPORT内で混在させることができることに注意してください。

パーティションの使用例を以下に示します。

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