Restarted Upsert Example - Parallel Data Pump

Teradata Parallel Data Pump Reference

Product
Parallel Data Pump
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-3021
lifecycle
previous
Product Category
Teradata Tools and Utilities

Restarted Upsert Example

This restarted upsert example uses two IMPORT clauses. The first one loads half of the records from the data source into an empty table. The second one does an upsert using all the records in the same data file. The result is that it updates the rows inserted during the first import and inserts all of the rows that the first import skipped.

This script:

/***********************************************/
/*   Script Name:  TP0734                      */
/*   Description:  WIN32 script.               */
/***********************************************/
.LOGTABLE TPLOG0734a;
.LOGON NODEID/HEMANTHDB,HEMANTHDB;
 
DROP TABLE TPTBL0734;
DROP TABLE TPERR0734;
 
/***********************************************/
/* STEP01 CREATES THE TABLES FOR THE TPump JOB */
/***********************************************/
CREATE TABLE TPTBL0734, FALLBACK(
	F1 INTEGER,	F2 CHAR(50),
	F3 VARCHAR(50),	F4 FLOAT,
	F5 BYTE (10),	F6 VARBYTE (10),
	F7 DECIMAL(8,2),F8 BYTEINT,
	F9 SMALLINT,	F10 DATE,
	F11 BIGINT,	F12 DECIMAL(38,0))
UNIQUE PRIMARY INDEX (F1);
 
/***********************************************/
/* BEGIN LOAD WITH ALL THE OPTIONS SPECIFIED   */
/* SUCH AS ERRLIMIT, CHECKPOINT, SESSIONS,     */
/* TENACITY, etc.                              */
/***********************************************/
.BEGIN LOAD	CHECKPOINT 15	SESSIONS 4 1
		TENACITY 2	ERRORTABLE TPERR0734
		ERRLIMIT 10	ROBUST OFF
		PACK 500	DATAENCRYPTION ON             
		ARRAYSUPPORT OFF RATE 200                      
		RETRYTIMES 200  SLEEP 40                      
		NOATOMICUPSERT  MACRODB HEMANTHDB                    
		NOTIFY OFF	SERIALIZE ON;
.LAYOUT LAY0734;
.FIELD FF1 * INTEGER KEY;
.FIELD FF2 * CHAR(50);
.FIELD FF3 * VARCHAR(50);
.FIELD FF4 * FLOAT;
.FIELD FF5 * BYTE(10);
.FIELD FF6 * VARBYTE(10);
.FIELD FF7 * DECIMAL(8,2);
.FIELD FF8 * BYTEINT;
.FIELD FF9 * SMALLINT;
.FIELD FF10 * DATE;
.FIELD FF11 * BIGINT;
.FIELD FF12 * DECIMAL(38,0);
/* insert half of the rows ......................*/
     .DML LABEL LABEL0734A      IGNORE DUPLICATE ROWS
                                IGNORE MISSING ROWS
                                IGNORE EXTRA ROWS;
  INSERT INTO TPTBL0734 VALUES (    :FF1,   :FF2,   :FF3,   :FF4,
                                :FF5,   :FF6,   :FF7,   :FF8,
                                :FF9,   :FF10,  :FF11,  :FF12);
/* ... and then upsert all of the rows ..........*/
     .DML LABEL LABEL0734B      IGNORE DUPLICATE ROWS
                                IGNORE MISSING ROWS
                                IGNORE EXTRA ROWS
                                DO INSERT FOR MISSING UPDATE ROWS;
UPDATE TPTBL0734 SET F7 = F7 + 1 WHERE F1 = :FF1;
INSERT INTO TPTBL0734 VALUES (    :FF1,   :FF2,   :FF3,   :FF4,
                                :FF5,   :FF6,   :FF7,   :FF8,
                                :FF9,   :FF10,  :FF11,  :FF12);
/* should result in an upsert with half inserts and half updates */
     .IMPORT INFILE ./ALLTYPE.data
        LAYOUT LAY0734 FROM 1 FOR 400
        APPLY LABEL0734A WHERE FF3 = 'TERADATA';
.IMPORT INFILE ./ALLTYPE.data
        LAYOUT LAY0734 FROM 1 FOR 400
        APPLY LABEL0734B;
.END LOAD;
.LOGOFF;

produces the following output (assuming it was restarted during the second import):

     ========================================================================
     =                                                                      =
     =          Teradata Parallel Data Pump Utility    Release 15.10.00.000 =
     =          Platform WIN32                                              =
     =          PID      13540                                              =
     =                                                                      =
     ========================================================================
     =                                                                      =
     =     Copyright 1997-2015 Teradata Corporation. ALL RIGHTS RESERVED.   =
     =                                                                      =
     ========================================================================
**** 20:08:19 UTY2411 Processing start date: MON JAN 7, 2015
     ========================================================================
     =                                                                      =
     =          Logon/Connection                                            =
     =                                                                      =
     ========================================================================
0001 /***********************************************/
     /*   Script Name:  TP0734                      */
     /*   Description:  WIN32 script.               */
     /***********************************************/
     .LOGTABLE TPLOG0734a;
0002 .LOGON NODEID/HEMANTHDB,;
**** 20:08:24 UTY8400 Teradata Database Release: 15.10.00.00
**** 20:08:24 UTY8400 Teradata Database Version: 15.10.00.00
**** 20:08:24 UTY8400 Default character set: ASCII
**** 20:08:24 UTY8400 Current RDBMS has UDT support
**** 20:08:24 UTY8400 Current RDBMS has Large Decimal support
**** 20:08:24 UTY8400 Maximum supported buffer size: 1M
**** 20:08:24 UTY8400 Upsert supported by RDBMS server
**** 20:08:24 UTY8400 Data Encryption supported by RDBMS server
**** 20:08:24 UTY8400 Array Support supported by RDBMS server
**** 20:08:24 UTY8400 Statement Independence supported by RDBMS server
**** 20:08:27 UTY6211 A successful connect was made to the RDBMS.
**** 20:08:27 UTY6217 Logtable 'HEMANTHDB.TPLOG0734a' has been created.
     ========================================================================
     =                                                                      =
     =          Processing Control Statements                               =
     =                                                                      =
     ========================================================================
 
0003 DROP TABLE TPTBL0734;
**** 20:08:28 UTY1016 'DROP' request successful.
0004 DROP TABLE TPERR0734;
**** 20:08:29 UTY1008 RDBMS failure: 3807, Object 'TPERR0734' does not exist.
 
0005 /***********************************************/
     /* STEP01 CREATES THE TABLES FOR THE TPump JOB */
     /***********************************************/
     CREATE TABLE TPTBL0734, FALLBACK(
        F1 INTEGER,     F2 CHAR(50),
        F3 VARCHAR(50), F4 FLOAT,
        F5 BYTE (10),   F6 VARBYTE (10),
        F7 DECIMAL(8,2),F8 BYTEINT,
        F9 SMALLINT,    F10 DATE,
        F11 BIGINT,     F12 DECIMAL(38,0))
     UNIQUE PRIMARY INDEX (F1);
**** 20:08:30 UTY1016 'CREATE' request successful.
 
0006 /***********************************************/
     /* BEGIN LOAD WITH ALL THE OPTIONS SPECIFIED   */
     /* SUCH AS ERRLIMIT, CHECKPOINT, SESSIONS,     */
     /* TENACITY, etc.                              */
     /***********************************************/
     .BEGIN LOAD        CHECKPOINT 15   SESSIONS 4 1
                TENACITY 2      ERRORTABLE TPERR0734
                ERRLIMIT 10     ROBUST OFF
                PACK 500        DATAENCRYPTION ON
                ARRAYSUPPORT OFF RATE 200
                RETRYTIMES 200  SLEEP 40
                NOATOMICUPSERT  MACRODB HEMANTHDB
                NOTIFY OFF      SERIALIZE ON;
     ========================================================================
     =                                                                      =
     =          Processing TPump Statements                                 =
     =                                                                      =
     ========================================================================
0007 .LAYOUT LAY0734;
0008 .FIELD FF1 * INTEGER KEY;
0009 .FIELD FF2 * CHAR(50);
0010 .FIELD FF3 * VARCHAR(50);
0011 .FIELD FF4 * FLOAT;
0012 .FIELD FF5 * BYTE(10);
0013 .FIELD FF6 * VARBYTE(10);
0014 .FIELD FF7 * DECIMAL(8,2);
0015 .FIELD FF8 * BYTEINT;
0016 .FIELD FF9 * SMALLINT;
0017 .FIELD FF10 * DATE;
0018 .FIELD FF11 * BIGINT;
0019 .FIELD FF12 * DECIMAL(38,0);
0020 /* insert half of the rows ......................*/
          .DML LABEL LABEL0734A      IGNORE DUPLICATE ROWS
                                     IGNORE MISSING ROWS
                                     IGNORE EXTRA ROWS;
0021   INSERT INTO TPTBL0734 VALUES (    :FF1,   :FF2,   :FF3,   :FF4,
                                     :FF5,   :FF6,   :FF7,   :FF8,
                                     :FF9,   :FF10,  :FF11,  :FF12);
0022 /* ... and then upsert all of the rows ..........*/
          .DML LABEL LABEL0734B      IGNORE DUPLICATE ROWS
                                     IGNORE MISSING ROWS
                                     IGNORE EXTRA ROWS
                                     DO INSERT FOR MISSING UPDATE ROWS;
0023 UPDATE TPTBL0734 SET F7 = F7 + 1 WHERE F1 = :FF1;
0024 INSERT INTO TPTBL0734 VALUES (    :FF1,   :FF2,   :FF3,   :FF4,
                                     :FF5,   :FF6,   :FF7,   :FF8,
                                     :FF9,   :FF10,  :FF11,  :FF12);
0025 /* should result in an upsert with half inserts and half updates */
          .IMPORT INFILE ./ALLTYPE.data
             LAYOUT LAY0734 FROM 1 FOR 400
             APPLY LABEL0734A WHERE FF3 = 'TERADATA';
0026 .IMPORT INFILE ./ALLTYPE.data
             LAYOUT LAY0734 FROM 1 FOR 400
             APPLY LABEL0734B;
0027 .END LOAD;
**** 20:08:31 UTY6609 Starting to log on sessions...
**** 20:08:35 UTY6610 Logged on 3 sessions.
     ========================================================================
     =                                                                      =
     =          TPump Import(s) Beginning                                   =
     =                                                                      =
     ========================================================================
**** 20:08:35 UTY6630 Options in effect for following TPump Import(s):
     .       Tenacity:    2 hour limit to successfully connect load sessions.
     .   Max Sessions:    4 session(s).
     .   Min Sessions:    1 session(s).
     .     Checkpoint:    15 minute(s).
     .       Errlimit:    10 rejected record(s).
     .   Restart Mode:    SIMPLE.
     .  Serialization:    ON.
     .        Packing:    500 Statements per Request.
     .   StartUp Rate:    200 Statements per Minute.
     .Rate Per Period:    50 Statements per 15000 milliseconds.
     .  Atomic Upsert:    DISABLED.
**** 20:09:57 UTY6625 WARNING: Packing has changed to 211 statement(s) per
     request.
**** 20:09:57 UTY6664 PACK factor was determined by the USING variable limit.
     Apply: 1, Stmt: 1.
**** 20:09:58 UTY8802 WARNING: Rate Monitoring turned off - database TPumpMacro
     does not exist.
**** 20:09:58 UTY6608 Import 1 begins.
**** 20:12:00 UTY6641 Since last chkpt., 400 recs. in, 400 stmts., 12 reqs
**** 20:12:00 UTY6647 Since last chkpt., avg. DBS wait time: 354.75
**** 20:12:00 UTY6612 Beginning final checkpoint...
**** 20:12:00 UTY6641 Since last chkpt., 400 recs. in, 400 stmts., 12 reqs
**** 20:12:00 UTY6647 Since last chkpt., avg. DBS wait time: 354.75
**** 20:12:00 UTY6607 Checkpoint Completes with 400 rows sent.
**** 20:12:00 UTY6642 Import 1 statements: 400, requests: 12
**** 20:12:00 UTY6643 Import 1 average statements per request: 33.33
**** 20:12:00 UTY6644 Import 1 average statements per record: 1.00
**** 20:12:00 UTY6645 Import 1 statements/session: avg. 133.33, min. 133.00,
     max. 134.00
**** 20:12:00 UTY6646 Import 1 requests/session: average 4.00, minimum 4.00,
     maximum 4.00
**** 20:12:00 UTY6648 Import 1 DBS wait time/session: avg. 1419.00, min. 421.00,
     max. 2308.00
**** 20:12:00 UTY6649 Import 1 DBS wait time/request: avg. 354.75, min. 105.25,
     max. 577.00
**** 20:12:00 UTY1823 Import processing statistics
     .                                           IMPORT  1      Total thus far
     .                                           =========      ==============
     Candidate records considered:....                 400                 400
     Apply conditions satisfied:......                 200                 200
     Records logable to error table:..                   0                   0
     Candidate records rejected:......                   0                   0
**** Statistics for Apply Label : LABEL0734A
     Type:                 I
     Database:             HEMANTHDB
     Table or Macro Name:  TPTBL0734
     Activity:             200
**** 20:12:00 UTY6677 Loading phase statistics
              Elapsed time:   00:00:02:02(dd:hh:mm:ss)
              CPU time:       0.0312002 Seconds
              MB/sec:         0.000396721
              MB/cpusec:      1.55127
**** 20:12:27 UTY8802 WARNING: Rate Monitoring turned off - database TPumpMacro
     does not exist.
**** 20:12:27 UTY6608 Import 2 begins.
**** 20:14:29 UTY6641 Since last chkpt., 400 recs. in, 400 stmts., 12 reqs
**** 20:14:29 UTY6647 Since last chkpt., avg. DBS wait time: 525.08
**** 20:14:29 UTY6612 Beginning final checkpoint...
**** 20:14:29 UTY6641 Since last chkpt., 400 recs. in, 400 stmts., 12 reqs
**** 20:14:29 UTY6647 Since last chkpt., avg. DBS wait time: 525.08
**** 20:14:29 UTY6607 Checkpoint Completes with 400 rows sent.
**** 20:14:29 UTY6642 Import 2 statements: 400, requests: 12
**** 20:14:29 UTY6643 Import 2 average statements per request: 33.33
**** 20:14:29 UTY6644 Import 2 average statements per record: 1.00
**** 20:14:29 UTY6645 Import 2 statements/session: avg. 133.33, min. 133.00,
     max. 134.00
**** 20:14:29 UTY6646 Import 2 requests/session: average 4.00, minimum 4.00,
     maximum 4.00
**** 20:14:29 UTY6648 Import 2 DBS wait time/session: avg. 2100.33, min. 280.00,
     max. 4134.00
**** 20:14:29 UTY6649 Import 2 DBS wait time/request: avg. 525.08, min. 70.00,
     max. 1033.50
**** 20:14:29 UTY1823 Import processing statistics
     .                                           IMPORT  2      Total thus far
     .                                           =========      ==============
     Candidate records considered:....                 400                 800
     Apply conditions satisfied:......                 400                 600
     Records logable to error table:..                   0                   0
     Candidate records rejected:......                   0                   0
**** Statistics for Apply Label : LABEL0734B
     Type:                 U
     Database:             HEMANTHDB
     Table or Macro Name:  TPTBL0734
     Activity:             200
     Type:                 I
     Database:             HEMANTHDB
     Table or Macro Name:  TPTBL0734
     Activity:             200
**** 20:14:29 UTY6677 Loading phase statistics
              Elapsed time:   00:00:02:02(dd:hh:mm:ss)
              CPU time:       0 Seconds
              MB/sec:         0.000396721
              MB/cpusec:      N/A
**** 20:14:33 UTY0821 Error table HEMANTHDB.TPERR0734 is EMPTY, dropping table.
0028 .LOGOFF;
     ========================================================================
     =                                                                      =
     =          Logoff/Disconnect                                           =
     =                                                                      =
     ========================================================================
**** 20:14:37 UTY6216 The restart log table has been dropped.
**** 20:14:39 UTY6212 A successful disconnect was made from the RDBMS.
**** 20:14:39 UTY2410 Total processor time used = '6.03724 Seconds'
     .       Start : 20:08:19 - MON JAN 7, 2015
     .       End   : 20:14:39 - MON JAN 7, 2015
     .       Highest return code encountered = '0'.