17.00 - Simple Script Example - 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

The following is an example of a simple script.

/***********************************************/
/*   Script Name:  TP0623                      */
/*   Description:  WIN32 script.               */
/***********************************************/
.LOGTABLE TPLOG0623;
.LOGON NODEID/HEMANTHDB,HEMANTHDB;

DROP TABLE TPTBL0623;
DROP TABLE TPERR0623;

/***********************************************/
/* STEP01 CREATES THE TABLES FOR THE TPump JOB */
/***********************************************/
CREATE TABLE TPTBL0623, 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 TPERR0623
		ERRLIMIT 10	ROBUST OFF
		PACK 500	DATAENCRYPTION ON
		ARRAYSUPPORT ON RATE 200
		RETRYTIMES 200  SLEEP 40
		NOATOMICUPSERT  MACRODB HEMANTHDB
		NOTIFY OFF	SERIALIZE ON;
.LAYOUT LAY0623;
.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);
.DML LABEL LABEL0623	IGNORE DUPLICATE ROWS
			IGNORE MISSING ROWS
			IGNORE EXTRA ROWS;
 INSERT INTO TPTBL0623 VALUES (:FF1, :FF2, :FF3, :FF4,	
				:FF5, :FF6, :FF7, :FF8,	
				:FF9, :FF10, :FF11, :FF12);
.IMPORT INFILE ./ALLTYPE.data
               LAYOUT LAY0623
               APPLY LABEL0623;
.END LOAD;
.LOGOFF;

produces the following output:

     ========================================================================
     =                                                                      =
     =          Teradata Parallel Data Pump Utility    Release 17.00.00.00  =
     =          Platform WIN 64-bit                                         =
     =          PID      9396                                               =
     =                                                                      =
     ========================================================================
     =                                                                      =
     =     Copyright 1997-2019 Teradata Corporation. ALL RIGHTS RESERVED.   =
     =                                                                      =
     ========================================================================
**** 03:33:05 UTY2411 Processing start date: WED APR 24, 2019
     ========================================================================
     =                                                                      =
     =          Logon/Connection                                            =
     =                                                                      =
     ========================================================================
	 
0001  /************************************************/
      /* Script Name: TP0623                          */
      /* Description: WIN 64-bit script.                  */
      /***********************************************/
	  
.LOGTABLE TPLOG0623;
0002 .LOGON NODEID/TPUSER,;
**** 03:33:05 UTY8400 Teradata Database Release: 17.00.00.00
**** 03:33:05 UTY8400 Teradata Database Version: 17.00.00.00
**** 03:33:05 UTY8400 Default character set: ASCII
**** 03:33:05 UTY8400 Current RDBMS has UDT support
**** 03:33:05 UTY8400 Current RDBMS has Large Decimal support
**** 03:33:05 UTY8400 Current RDBMS has Statement Info Parcel support
**** 03:33:05 UTY8400 Maximum supported buffer size: 1M
**** 03:33:05 UTY8400 Upsert supported by RDBMS server
**** 03:33:05 UTY8400 Data Encryption supported by RDBMS server
**** 03:33:05 UTY8400 Array Support supported by RDBMS server
**** 03:33:05 UTY8400 Statement Independence supported by RDBMS server
**** 03:33:05 UTY6211 A successful connect was made to the RDBMS.
**** 03:33:05 UTY6217 Logtable 'TPUSER.TPLOG0623' has been created.
     ========================================================================
     =                                                                      =
     =          Processing Control Statements                               =
     =                                                                      =
     ========================================================================

0003 DROP TABLE TPTBL0623;
**** 03:33:05 UTY1008 RDBMS failure: 3807, Object 'TPTBL0623' does not exist.
0004 DROP TABLE TPERR0623;
**** 03:33:05 UTY1008 RDBMS failure: 3807, Object 'TPERR0623' does not exist.

0005 /***********************************************/
     /* STEP01 CREATES THE TABLES FOR THE TPump JOB */
     /***********************************************/
CREATE TABLE TPTBL0623, 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);
**** 03:33:05 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 TPERR0623
            ERRLIMIT 10 ROBUST OFF
            PACK 500 DATAENCRYPTION ON
            ARRAYSUPPORT ON RATE 200
            RETRYTIMES 200 SLEEP 40
            NOATOMICUPSERT MACRODB TPUSER
            NOTIFY OFF SERIALIZE ON;
     ========================================================================
     =                                                                      =
     =          Processing TPump Statements                                 =
     =                                                                      =
     ========================================================================

0007 .LAYOUT LAY0623;
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  .DML LABEL LABEL0623
      IGNORE DUPLICATE ROWS
      IGNORE MISSING ROWS
      IGNORE EXTRA ROWS;
	  
0021 INSERT INTO TPTBL0623 VALUES (:FF1,:FF2,:FF3,:FF4,
     :FF5,:FF6,:FF7,:FF8,
     :FF9,:FF10,:FF11,:FF12);
	 
0022 .IMPORT INFILE ./ALLTYPE.data
      LAYOUT LAY0623
      APPLY LABEL0623;
	  
0023 .END LOAD;	 
**** 03:33:05 UTY6609 Starting to log on sessions...
**** 03:33:06 UTY6610 Logged on 3 sessions.
     ========================================================================
     =                                                                      =
     =          TPump Import(s) Beginning                                   =
     =                                                                      =
     ========================================================================
**** 03:33:06 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.
**** 03:33:06 UTY8802 WARNING: Rate Monitoring turned off - database TPumpMacro
     does not exist.
**** 03:33:06 UTY6608 Import 1 begins.
**** 03:38:06 UTY6641 Since last chkpt., 1024 recs. in, 1024 stmts., 25 reqs
**** 03:38:06 UTY6647 Since last chkpt., avg. DBS wait time: 2.48
**** 03:38:06 UTY6612 Beginning final checkpoint...
**** 03:38:06 UTY6641 Since last chkpt., 1024 recs. in, 1024 stmts., 25 reqs
**** 03:38:06 UTY6647 Since last chkpt., avg. DBS wait time: 2.48
**** 03:38:06 UTY6607 Checkpoint Completes with 1024 rows sent.
**** 03:38:06 UTY6642 Import 1 statements: 1024, requests: 25
**** 03:38:06 UTY6643 Import 1 average statements per request: 40.96
**** 03:38:06 UTY6644 Import 1 average statements per record: 1.00
**** 03:38:06 UTY6645 Import 1 statements/session: avg. 341.33, min. 341.00,
     max. 342.00
**** 03:38:06 UTY6646 Import 1 requests/session: average 8.33, minimum 8.00,
     maximum 9.00
**** 03:38:06 UTY6648 Import 1 DBS wait time/session: avg. 20.67, min. 15.00,
     max. 31.00
**** 03:38:06 UTY6649 Import 1 DBS wait time/request: avg. 2.44, min. 1.88, max.
     3.44
**** 03:38:06 UTY1823 Import processing statistics
     .                                           IMPORT 1       Total thus far
     .                                           =========      ==============
     Candidate records considered:....                 1024               1024
     Apply conditions satisfied:......                 1024               1024
     Records logable to error table:..                    0                  0
     Candidate records rejected:......                    0                  0
**** Statistics for Apply Label : LABEL0623
     Type:                 I
     Database:             TPUSER
     Table or Macro Name:  TPTBL0623
     Activity:             1024
**** 03:38:06 UTY6677 Loading phase statistics
              Elapsed time:   00:00:05:00(dd:hh:mm:ss)
              CPU time:       0.0312002 Seconds
              MB/sec:         0.000413013
              MB/cpusec:      3.97126
**** 03:38:06 UTY0821 Error table TPUSER.TPERR0623 is EMPTY, dropping table.
0024 .LOGOFF;
     ========================================================================
     =                                                                      =
     =                     Logoff/Disconnect                                =
     =                                                                      =
     ========================================================================
     **** 03:38:06 UTY6216 The restart log table has been dropped.
     **** 03:38:06 UTY6212 A successful disconnect was made from the RDBMS.
     **** 03:38:06 UTY2410 Total processor time used = '0.249602 Seconds'
     .             Start : 03:33:05 - WED APR 24, 2019
     .             End :   03:38:06 - WED APR 24, 2019 
     .             Highest return code encountered = '0'.