15.00 - Example Using the TABLE Command - Parallel Data Pump

Teradata Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

Example Using the TABLE Command

This example script uses the TABLE command and “INSERT <TABLENAME>.*” feature.

.LOGTABLE TPLOG0096;
.LOGON NODEID/HEMANTHDB,HEMANTHDB;
DROP TABLE TPTBL0096;
DROP TABLE TPERR0096;
CREATE TABLE TPTBL0096, FALLBACK(
        F0 integer,     F1 integer,
        F2 integer,     F3 CHAR(38))
UNIQUE PRIMARY INDEX (F0);
 
.BEGIN LOAD SESSIONS 8;
 
.LAYOUT LAY0096;
.TABLE  TPTBL0096;
 
.DML LABEL LABEL0096 ;
 INSERT INTO TPTBL0096.*;
 
.IMPORT INFILE datafile1
               LAYOUT LAY0096
               APPLY LABEL0096 FROM 1 THRU 111;
.END LOAD;
.LOGOFF;
 
 
12:58 PM 2/6/2014

produces the following results. When looking at the following results, notice that the output fields generated by the TABLE command includes the “KEY” modifier for the field coming from the primary index of the table. This is what enables the use of the “SERIALIZE” option:

     ========================================================================
     =                                                                      =
     =          Teradata Parallel Data Pump Utility    Release 15.00.00.000 =
     =          Platform WIN32                                              =
     =          PID      19008                                              =
     =                                                                      =
     ========================================================================
     =                                                                      =
     =     Copyright 1997-2014 Teradata Corporation. ALL RIGHTS RESERVED.   =
     =                                                                      =
     ========================================================================
**** 19:57:58 UTY2411 Processing start date: FRI JAN 10, 2014
     ========================================================================
     =                                                                      =
     =          Logon/Connection                                            =
     =                                                                      =
     ========================================================================
0001 .LOGTABLE TPLOG0096;
0002 .LOGON NODEID/HEMANTHDB,;
**** 19:58:03 UTY8400 Teradata Database Release: 15.00.00.00
**** 19:58:03 UTY8400 Teradata Database Version: 15.00.00.00
**** 19:58:03 UTY8400 Default character set: ASCII
**** 19:58:03 UTY8400 Current RDBMS has UDT support
**** 19:58:03 UTY8400 Current RDBMS has Large Decimal support
**** 19:58:03 UTY8400 Maximum supported buffer size: 1M
**** 19:58:03 UTY8400 Upsert supported by RDBMS server
**** 19:58:03 UTY8400 Data Encryption supported by RDBMS server
**** 19:58:03 UTY8400 Array Support supported by RDBMS server
**** 19:58:03 UTY8400 Statement Independence supported by RDBMS server
**** 19:58:05 UTY6211 A successful connect was made to the RDBMS.
**** 19:58:05 UTY6217 Logtable 'HEMANTHDB.TPLOG0096' has been created.
     ========================================================================
     =                                                                      =
     =          Processing Control Statements                               =
     =                                                                      =
     ========================================================================
0003 DROP TABLE TPTBL0096;
**** 19:58:05 UTY1008 RDBMS failure: 3807, Object 'TPTBL0096' does not exist.
0004 DROP TABLE TPERR0096;
**** 19:58:06 UTY1008 RDBMS failure: 3807, Object 'TPERR0096' does not exist.
0005 CREATE TABLE TPTBL0096, FALLBACK(
             F0 integer,     F1 integer,
             F2 integer,     F3 CHAR(38))
     UNIQUE PRIMARY INDEX (F0);
**** 19:58:08 UTY1016 'CREATE' request successful.
 
0006 .BEGIN LOAD SESSIONS 8;
     ========================================================================
     =                                                                      =
     =          Processing TPump Statements                                 =
     =                                                                      =
     ========================================================================
 
0007 .LAYOUT LAY0096;
0008 .TABLE  TPTBL0096;
**** 19:58:08 UTY6009 Fields generated by .TABLE command begin.
**** 19:58:08 UTY6010 *** .FIELD F0 * INTEGER KEY;
**** 19:58:08 UTY6010 *** .FIELD F1 * INTEGER;
**** 19:58:08 UTY6010 *** .FIELD F2 * INTEGER;
**** 19:58:08 UTY6010 *** .FIELD F3 * CHAR(38) MACROCHARSET LATIN;
**** 19:58:08 UTY6011 Fields generated by .TABLE command end.
 
0009 .DML LABEL LABEL0096 ;
0010  INSERT INTO TPTBL0096.*;
 
0011 .IMPORT INFILE datafile1
                    LAYOUT LAY0096
                    APPLY LABEL0096 FROM 1 THRU 111;
0012 .END LOAD;
**** 19:58:09 UTY6609 Starting to log on sessions...
**** 19:58:20 UTY6610 Logged on 8 sessions.
     ========================================================================
     =                                                                      =
     =          TPump Import(s) Beginning                                   =
     =                                                                      =
     ========================================================================
**** 19:58:20 UTY6630 Options in effect for following TPump Import(s):
     .       Tenacity:    4 hour limit to successfully connect load sessions.
     .   Max Sessions:    8 session(s).
     .   Min Sessions:    6 session(s).
     .     Checkpoint:    15 minute(s).
     .       Errlimit:    No limit in effect.
     .   Restart Mode:    ROBUST.
     .  Serialization:    OFF.
     .        Packing:    20 Statements per Request.
     .   StartUp Rate:    UNLIMITED Statements per Minute.
**** 19:58:26 UTY8802 WARNING: Rate Monitoring turned off - database TPumpMacro
     does not exist.
**** 19:58:26 UTY6608 Import 1 begins.
**** 19:58:28 UTY6641 Since last chkpt., 111 recs. in, 111 stmts., 6 reqs
**** 19:58:28 UTY6647 Since last chkpt., avg. DBS wait time: 390.00
**** 19:58:28 UTY6612 Beginning final checkpoint...
**** 19:58:28 UTY6641 Since last chkpt., 111 recs. in, 111 stmts., 6 reqs
**** 19:58:28 UTY6647 Since last chkpt., avg. DBS wait time: 390.00
**** 19:58:29 UTY6607 Checkpoint Completes with 111 rows sent.
**** 19:58:29 UTY6642 Import 1 statements: 111, requests: 6
**** 19:58:29 UTY6643 Import 1 average statements per request: 18.50
**** 19:58:29 UTY6644 Import 1 average statements per record: 1.00
**** 19:58:29 UTY6645 Import 1 statements/session: avg. 13.88, min. 0.00, max.
     20.00
**** 19:58:29 UTY6646 Import 1 requests/session: average 0.75, minimum 0.00,
     maximum 1.00
**** 19:58:29 UTY6648 Import 1 DBS wait time/session: avg. 292.50, min. 0.00,
     max. 1809.00
**** 19:58:29 UTY6649 Import 1 DBS wait time/request: avg. 292.50, min. 0.00,
     max. 1809.00
**** 19:58:29 UTY1823 Import processing statistics
     .                                           IMPORT  1      Total thus far
     .                                           =========      ==============
     Candidate records considered:....                 111                 111
     Apply conditions satisfied:......                 111                 111
     Records logable to error table:..                   0                   0
     Candidate records rejected:......                   0                   0
**** Statistics for Apply Label : LABEL0096
     Type:                 I
     Database:             HEMANTHDB
     Table or Macro Name:  TPTBL0096
     Activity:             111
**** 19:58:29 UTY6677 Loading phase statistics
              Elapsed time:   00:00:00:03(dd:hh:mm:ss)
              CPU time:       0 Seconds
              MB/sec:         0.00185
              MB/cpusec:      N/A
**** 19:58:31 UTY0821 Error table HEMANTHDB.M20140110_195809_00016_001_ET is
     EMPTY, dropping table.
0013 .LOGOFF;
     ========================================================================
     =                                                                      =
     =          Logoff/Disconnect                                           =
     =                                                                      =
     ========================================================================
**** 19:58:34 UTY6216 The restart log table has been dropped.
**** 19:58:35 UTY6212 A successful disconnect was made from the RDBMS.
**** 19:58:35 UTY2410 Total processor time used = '0.624004 Seconds'
     .       Start : 19:57:58 - FRI JAN 10, 2014
     .       End   : 19:58:35 - FRI JAN 10, 2014
     .       Highest return code encountered = '0'.