Sample Scripts
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 above 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;