17.00 - Teradata TPump Statistics - 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

For each task, Teradata TPump accumulates statistical items and writes them to the customary output destination of the external system, SYSPRINT/stdout (or the redirected stdout), or the destination specified in the ROUTE command.

The following table lists the Teradata TPump statistics kept.

Teradata TPump Statistics 
Reference Number Reference Item Statistic
1 Candidate records considered The number of records read.
2 Apply conditions satisfied The number of statements sent to the database. If there are no rejected or skipped records, this value is equal to the number of candidate records, multiplied by the number of APPLY statements referenced in the import.
3 Errors loggable to error table The number of records resulting in errors on the database. These records are found in the associated error table.
4 Candidate records rejected The number of records which are rejected by the Teradata TPump client code because they are formatted incorrectly.
5 Statistics for Apply Label This area breaks out the total activity count for each statement within each DML APPLY clause. The Type column contains the values U for update, I for insert, and D for delete. Note that unlike the other reported statistics, these values are NOT accumulated across multiple imports.
6 Number of database requests sent These statistics are displayed only in the verbose mode, which is selected as a runtime parameter, VERBOSE, in z/OS, or -v in a UNIX system.

In addition, Teradata TPump receives a count of the number of rows deleted from the database. Teradata TPump writes it either to SYSPRINT/stdout (or the redirected stdout), or the destination specified in the ROUTE command.

If a record is rejected due to an error, as in the case of a duplicate, missing, or extra insert, update, or delete row, the following statistical output shows that an error condition occurred.

     .                                       IMPORT  1     Total thus far
     .                                       =========     ==============
     Candidate records considered:........           8.......           8  <-----(1)- 
     Apply conditions satisfied:..........           8.......           8  <-----(2)- 
     Errors loggable to error table:......           1.......           1  <-----(3)- 
     Candidate records rejected:..........           1.......           1  <-----(4)- 
     Number of RDBMS requests sent:.......           6.......           6  <-----(6)- 
** Statistics for Apply Label : LABELB
Type                      Database           Table or Macro Name     Activity
   I                           CME                  TDBTB734_TAL            7  <-(5)-

Restart Statistics

Teradata TPump stores statistics in the restart log table. After a restart, all statistics are properly restored.

Performance Statistics

Teradata TPump now issues UTY6677 message at the end of loading phase:

UTY6677 Loading phase statistics
 Elapsed time: 00:00:00:01 (dd:hh:mm:ss)
 CPU time:     xx.xx Seconds
 MB/sec:       xx.xx
 MB/cpusec:    xx.xx
The longhand of the abbreviation "MB/cpusec"'is megabytes(Total data TPump sends) per cpu second.
The longhand of abbreviation "MB/sec" is megabytes(Total data TPump sends) per elapsed second.
This performance statics could be varied on different platforms.

Teradata TPump Statistical Output

The following is an example of Teradata TPump output. Lines that are marked on the right-hand side with (-----------(n) are explained above.

     ========================================================================
     =                                                                      =
     =      Teradata Parallel Data Pump Utility Release 17.00.00.00         =      
     =      Platform WIN 64-bit                                             =
     =      PID 5836                                                        =
     =                                                                      =
     ========================================================================
     =                                                                      =
     = Copyright 1997-2019 Teradata Corporation. ALL RIGHTS RESERVED.       =
     =                                                                      =
     ========================================================================
**** 19:07:16 UTY2411 Processing start date: WED APR 24, 2019
     ========================================================================
     =                                                                      =
     =         Logon/Connection                                             =
     =                                                                      =
     ========================================================================
001 .LOGTABLE TPPLOGTESTT;
0002 .LOGON NODEID/HEMANTHDB,;
**** 19:07:16 UTY8400 Teradata Database Release: 17.00.00.00
**** 19:07:16 UTY8400 Teradata Database Version: 17.00.00.00
**** 19:07:16 UTY8400 Default character set: ASCII
**** 19:07:16 UTY8400 Current RDBMS has UDT support
**** 19:07:16 UTY8400 Current RDBMS has Large Decimal support
**** 19:07:16 UTY8400 Maximum supported buffer size: 1M
**** 19:07:16 UTY8400 Upsert supported by RDBMS server
**** 19:07:16 UTY8400 Data Encryption supported by RDBMS server
**** 19:07:16 UTY8400 Array Support supported by RDBMS server
**** 19:07:16 UTY8400 Statement Independence supported by RDBMS server
**** 19:07:18 UTY6211 A successful connect was made to the RDBMS.
**** 19:07:18 UTY6217 Logtable 'HEMANTHDB.TPPLOGTESTT' has been created.
      ========================================================================
      =                                                                      =
      =                Processing Control Statements                         =
      =                                                                      =
      ========================================================================
0003 drop table TAB1;
**** 19:07:19 UTY1016 'DROP' request successful.
0004 drop table TAB2;
**** 19:07:20 UTY1016 'DROP' request successful.
0005 drop table ET_TEST1;
**** 19:07:21 UTY1016 'DROP' request successful.

0006 CREATE TABLE TAB1, FALLBACK, NO JOURNAL (
            F0 integer,
            F1 integer,
            F2 integer,
            F3 char(38))
            UNIQUE PRIMARY INDEX(F0);
**** 19:07:22 UTY1016 'CREATE' request successful.

0007 CREATE TABLE TAB2, FALLBACK, NO JOURNAL (
            F0 integer,
            F1 integer,
            F2 integer,
            F3 char(38))
            UNIQUE PRIMARY INDEX(F0);
**** 19:07:23 UTY1016 'CREATE' request successful.

0008 .BEGIN LOAD
      SESSIONS 10
      ROBUST ON
      SERIALIZE ON
      CHECKPOINT 10
      NOMONITOR
      ERRORTABLE ET_TEST1;
      ========================================================================
      =                                                                      =
      =                 Processing TPump Statements                          =
      =                                                                      =
      ========================================================================
0009 .LAYOUT LAY1A;
0010 .FIELD F0 * integer key;
0011 .FIELD F1 * integer;
0012 .FIELD F2 * integer;
0013 .FIELD F3 * char(38);

0014 .DML LABEL TAB1PART1;
0015 INSERT into tab1 values (:F0,:F1,:F2,:F3);

0016 .DML LABEL TAB2PART1;
0017 INSERT into tab2 values (:F0,:F1,:F2,:F3);

0018 .DML LABEL TAB1UPSERT DO INSERT FOR MISSING UPDATE ROWS IGNORE DUPLICATE INSERT
      ROWS;
0019 UPDATE tab1 set F2=:F2 + 1 where f0=:f0 + 50 and f1 < 2000;
0020 INSERT into tab1 ( F0, F1, F2, F3) values (:F0 + 50,:F1,:F2,:F3);

0021 .DML LABEL TAB2UPSERT DO INSERT FOR MISSING UPDATE ROWS IGNORE DUPLICATE INSERT
      ROWS;
0022 UPDATE tab2 set F2=:F2 + 1 where f0=:f0 + 50 and f1 < 2000;
0023 INSERT into tab2 ( F0, F1, F2, F3) values (:F0 + 50,:F1,:F2,:F3);

0024 .IMPORT INFILE INDATA FROM 1 THRU 100
                           LAYOUT LAY1A
                           APPLY TAB1PART1
                           APPLY TAB2PART1;
0025 .IMPORT INFILE INDATA FROM 1 THRU 100
                           LAYOUT LAY1A
                           APPLY TAB1UPSERT
                           APPLY TAB2UPSERT;
0026 .END LOAD;
**** 19:07:24 UTY6609 Starting to log on sessions...
**** 19:07:37 UTY6610 Logged on 10 sessions.
     ========================================================================
     =                                                                      =
     =                 TPump Import(s) Beginning                            =
     =                                                                      =
     ========================================================================
**** 19:07:37 UTY6630 Options in effect for following TPump Import(s):
      .        Tenacity: 4 hour limit to successfully connect load sessions.
      .        Max Sessions: 10 session(s).
      .        Min Sessions: 8 session(s).
      .        Checkpoint: 10 minute(s).
      .        Errlimit: No limit in effect.
      .        Restart Mode: SIMPLE.
      .        Serialization: OFF.
      .        Packing: 20 Statements per Request.
      .        StartUp Rate: UNLIMITED Statements per Minute.
**** 19:07:42 UTY6608 Import 1 begins.
**** 19:07:44 UTY6641 Since last chkpt., 100 recs. in, 200 stmts., 10 reqs
**** 19:07:44 UTY6647 Since last chkpt., avg. DBS wait time: 151.40
**** 19:07:44 UTY6612 Beginning final checkpoint...
**** 19:07:44 UTY6641 Since last chkpt., 100 recs. in, 200 stmts., 10 reqs
**** 19:07:44 UTY6647 Since last chkpt., avg. DBS wait time: 151.40
**** 19:07:44 UTY6607 Checkpoint Completes with 200 rows sent.
**** 19:07:44 UTY6642 Import 1 statements: 200, requests: 10
**** 19:07:44 UTY6643 Import 1 average statements per request: 20.00
**** 19:07:44 UTY6644 Import 1 average statements per record: 1.00
**** 19:07:44 UTY6645 Import 1 statements/session: avg. 20.00, min. 20.00, max.
     20.00
**** 19:07:44 UTY6646 Import 1 requests/session: average 1.00, minimum 1.00,
      maximum 1.00
**** 19:07:44 UTY6648 Import 1 DBS wait time/session: avg. 151.40, min. 0.00,
     max. 1217.00
**** 19:07:44 UTY6649 Import 1 DBS wait time/request: avg. 151.40, min. 0.00,
     max. 1217.00
**** 19:07:44 UTY1823 Import processing statistics
                                             IMPORT 1 Total thus far
.                                            ========= ==============
            Candidate records considered:.... 100       100<-----(1)-
            Apply conditions satisfied:...... 200       200<-----(2)-
            Records logable to error table:..   0         0<-----(3)-
            Candidate records rejected:......   0         0<-----(4)-
            Number of RDBMS requests sent:...   10       10<-----(6)-
**** Statistics for Apply Label : TAB1PART1
     Type: I
     Database: HEMANTHDB
     Table or Macro Name: tab1
     Activity: 100<---(5)-
**** Statistics for Apply Label : TAB2PART1
      Type: I
      Database: HEMANTHDB
      Table or Macro Name: tab2 
      Activity: 100
**** 19:07:44 UTY6677 Loading phase statistics
     Elapsed time: 00:00:00:02(dd:hh:mm:ss)
     CPU time: 0 Seconds
     MB/sec: 0.005
     MB/cpusec: N/A
**** 19:07:51 UTY6608 Import 2 begins.
**** 19:08:03 UTY6641 Since last chkpt., 100 recs. in, 355 stmts., 30 reqs
**** 19:08:03 UTY6647 Since last chkpt., avg. DBS wait time: 421.20
**** 19:08:03 UTY6612 Beginning final checkpoint...
**** 19:08:03 UTY6641 Since last chkpt., 100 recs. in, 355 stmts., 30 reqs
**** 19:08:03 UTY6647 Since last chkpt., avg. DBS wait time: 421.20
**** 19:08:04 UTY6607 Checkpoint Completes with 200 rows sent.
**** 19:08:04 UTY6642 Import 2 statements: 355, requests: 30
**** 19:08:04 UTY6643 Import 2 average statements per request: 11.83
**** 19:08:04 UTY6644 Import 2 average statements per record: 1.77
**** 19:08:04 UTY6645 Import 2 statements/session: avg. 35.50, min. 20.00, max.
     175.00
**** 19:08:04 UTY6646 Import 2 requests/session: average 3.00, minimum 1.00,
     maximum 21.00
**** 19:08:04 UTY6648 Import 2 DBS wait time/session: avg. 1263.60, min. 0.00,
     max. 9672.00
**** 19:08:04 UTY6649 Import 2 DBS wait time/request: avg. 342.46, min. 0.00,
      max. 2137.00
**** 19:08:04 UTY1823 Import processing statistics
.                                             IMPORT 2 Total thus far
.                                            ========= ==============
          Candidate records considered:....         100            200
          Apply conditions satisfied:......         200            400
          Records logable to error table:..           0              0
          Candidate records rejected:......           0              0
          Number of RDBMS requests sent:...          10             20
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+                                                                      +
+         ERRORS ENCOUNTERED PROCESSING INPUT DATA                     +
+                                                                      +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
**** Statistics for Apply Label : TAB1UPSERT
     Type: U
     Database: HEMANTHDB
     Table or Macro Name: tab1
     Activity: 50
     Type: I
     Database: HEMANTHDB
     Table or Macro Name: tab1
     Activity: 50
**** Statistics for Apply Label : TAB2UPSERT
     Type: U
     Database: HEMANTHDB
     Table or Macro Name: tab2
     Activity: 50
     Type: I
     Database: HEMANTHDB
     Table or Macro Name: tab2
     Activity: 50
**** 19:08:04 UTY6677 Loading phase statistics
     Elapsed time: 00:00:00:13(dd:hh:mm:ss)
     CPU time: 0 Seconds
     MB/sec: 0.000769231
     MB/cpusec: N/A
**** 19:08:06 UTY0820 Error table HEMANTHDB.ET_TEST1 is EMPTY, dropping table.
     ========================================================================
     =                                                                      =
     =                               Logoff/Disconnect                      =
     =                                                                      =
     ========================================================================
**** 19:08:13 UTY6216 The restart log table has been dropped.
**** 19:08:13 UTY6212 A successful disconnect was made from the RDBMS.
**** 19:08:13 UTY2410 Total processor time used = '0.780005 Seconds'
     . Start :    19:07:11 - WED APR 24, 2019
     . End   :    19:08:13 - WED APR 24, 2019
     . Highest return code encountered = '0'.

The above script has a realistic degree of complexity. The script demonstrates a Teradata TPump job that contains two imports and each import has at least two associated statements.

For the first import there are two statements, each of which is specified in a separate DML statement. The IMPORT statement references the two statements through two APPLY clauses.

The second import adds additional complexity by having two statements in each DML statement. In this case, the two statements in each DML compose an upsert statement.