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.
| 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.