Teradata TPump Statistics

Teradata Parallel Data Pump Reference

brand
Teradata Tools and Utilities
prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

Teradata TPump Statistics

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.

Table 22 lists the Teradata TPump statistics kept.

 

Table 22: 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 Teradata 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.

**** 19:07:11 UTY2407 Run time parameters in effect are: VERBOSE.
     ========================================================================
     =                                                                      =
     =          Teradata Parallel Data Pump Utility    Release 15.00.00.000 =
     =          Platform WIN32                                              =
     =          PID      21320                                              =
     =                                                                      =
     ========================================================================
     =                                                                      =
     =     Copyright 1997-2014 Teradata Corporation. ALL RIGHTS RESERVED.   =
     =                                                                      =
     ========================================================================
**** 19:07:11 UTY2411 Processing start date: FRI JAN 10, 2014
     ========================================================================
     =                                                                      =
     =          Logon/Connection                                            =
     =                                                                      =
     ========================================================================
0001 .LOGTABLE TPPLOGTESTT;
0002 .LOGON NODEID/HEMANTHDB,;
**** 19:07:16 UTY8400 Teradata Database Release: 15.00.00.00
**** 19:07:16 UTY8400 Teradata Database Version: 15.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:    ROBUST.
     .  Serialization:    ON.
     .        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 - FRI JAN 10, 2014
     .       End   : 19:08:13 - FRI JAN 10, 2014
     .       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.