15.00 - Invoking Teradata TPump - Parallel Data Pump

Teradata Parallel Data Pump Reference

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

Invoking Teradata TPump

Teradata TPump Support Environment

This section describes those Teradata TPump functions that are invoked from the Teradata TPump support environment on the client system. The Teradata TPump support environment is a platform from which Teradata TPump and a number of standard Teradata SQL, DDL, and DML operations can be performed. This client program includes a facility for executing Teradata SQL and is separate from Teradata TPump tasks that run in Teradata Database.

Teradata TPump support environment functionality includes:

  • Providing access to the data manipulation and data definition functions of the Teradata SQL language.
  • User‑defined variables and variable substitution.
  • System‑defined variables (for example, date and time).
  • Conditional execution based on the value of return codes and variables.
  • Expression evaluation.
  • Redirection of command input.
  • Runtime parameters for Teradata TPump invocation, foreign language support, and error logging functions.
  • Character set selection options for IBM mainframe and client‑based systems for UNIX.
  • The Teradata TPump support environment allows preparation for an initial invocation or resumption of a Teradata TPump task without having to invoke multiple distinct utilities. For example, the table that is to be loaded may need to be created, a database established as an implicit table‑name qualifier, or the relevant permanent journal checkpointed.

    Any statement not preceded by a period (.) is assumed to be a Teradata SQL statement and is sent to Teradata Database to be processed. An object name in an Teradata SQL statement may contain Katakana or multibyte characters when the appropriate character set is used.

    The Teradata TPump support environment interprets the commands and statements that define the job. It also controls the execution of those commands and manages recovery from Teradata Database or client failures during processing.

    Those commands not directly involved in defining a Teradata TPump task, but providing supportive functions (routing output, for example), are considered Teradata TPump support commands. These are individually executed as they are encountered.

    The commands that define a single Teradata TPump task are processed by the client as a single unit. These are considered to be Teradata TPump task commands. The actual execution of the Teradata TPump task is deferred until all pertinent task commands have been considered and validated by the client program.

    Support Environment Input/Output

    Support environment Input/Output (I/O) appears in the following forms:

  • Command and statement input (default = SYSIN/stdin)
  • Accept command input from file
  • Command and statement output (default = SYSPRINT/stdout)
  • Display command output (default = SYSPRINT/stdout)
  • Error output (default = SYSPRINT/stdout)
  • Note: For IBM statement input, the default is initially the user‑provided invocation parameter (JCL PARM), if specified. After all commands and nested files in the parameter are processed, the default is SYSIN.

    SYSPRINT/stdout Output

    The characteristics of SYSPRINT output for z/OS and UNIX systems standard output (stdout) are:

  • The first five positions of each output line are reserved. They contain a statement number if the line is the beginning of a Teradata TPump statement. This also applies to comments preceding Teradata TPump statements.
  • If the output line is a Teradata TPump‑generated message, the first five positions contain the string ****.
  • In all other cases, the first five positions are blank.
  • A message indicating the processing start date appears at the beginning of every job.
  • Teradata TPump‑generated messages are preceded by a header displaying system time. This timestamp appears on the same line as the message and follows the **** string.
  • Example  

    This example depicts each type of SYSPRINT/stdout output line noted in the previous list.

         ========================================================================
         =                                                                      =
         =          Teradata Parallel Data Pump Utility    Release 15.00.00.000 =
         =          Platform WIN32                                              =
         =          PID      16900                                              =
         =                                                                      =
         ========================================================================
         =                                                                      =
         =     Copyright 1997-2014 Teradata Corporation. ALL RIGHTS RESERVED.   =
         =                                                                      =
         ========================================================================
    **** 17:11:00 UTY2411 Processing start date: FRI JAN 10, 2014
         ========================================================================
         =                                                                      =
         =          Logon/Connection                                            =
         =                                                                      =
         ========================================================================
     
    0001 /************************************************************************
         *                    This is a sample test for TPump                    *
         ************************************************************************/
         .LOGTABLE TPLOG0044;
    0002 .LOGON NODEID/HEMANTHDB,;
    **** 17:11:06 UTY8400 Teradata Database Release: 15.00.00.00
    **** 17:11:06 UTY8400 Teradata Database Version: 15.00.00.00
    **** 17:11:06 UTY8400 Default character set: ASCII                         
    **** 17:11:06 UTY8400 Current RDBMS has UDT support
    **** 17:11:06 UTY8400 Current RDBMS has Large Decimal support
    **** 17:11:06 UTY8400 Maximum supported buffer size: 1M
    **** 17:11:06 UTY8400 Upsert supported by RDBMS server
    **** 17:11:06 UTY8400 Data Encryption supported by RDBMS server
    **** 17:11:06 UTY8400 Array Support supported by RDBMS server
    **** 17:11:06 UTY8400 Statement Independence supported by RDBMS server
    **** 17:11:11 UTY6211 A successful connect was made to the RDBMS.
    **** 17:11:11 UTY6217 Logtable 'HEMANTHDB.TPLOG0044' has been created.
         ========================================================================
         =                                                                      =
         =          Processing Control Statements                               =
         =                                                                      =
         ========================================================================
     
    0003 .NAME TPUMP0044;
     
    0004  DROP TABLE TPTBL0044;
    **** 17:11:12 UTY1008 RDBMS failure: 3807, Object 'TPTBL0044' does not exist.
    0005  DROP TABLE TPERR0044;
    **** 17:11:13 UTY1008 RDBMS failure: 3807, Object 'TPERR0044' does not exist.
     
    0006 CREATE TABLE TPTBL0044, 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)
         UNIQUE PRIMARY INDEX (F1);
    **** 17:11:14 UTY1016 'CREATE' request successful.
     
    0007 .BEGIN LOAD	CHECKPOINT 15
         		SESSIONS 4 1
         		TENACITY 2
         		ERRORTABLE TPERR0044
         		ROBUST OFF
         		NOMONITOR
         		PACK 500;
         ========================================================================
         =                                                                      =
         =          Processing TPump Statements                                 =
         =                                                                      =
         ========================================================================
     
    0008 .LAYOUT LAY0044;
    0009 .FIELD FF1 * INTEGER KEY;
    0010 .FIELD FF2 * CHAR(50);
    0011 .FIELD FF3 * VARCHAR(50);
    0012 .FIELD FF4 * FLOAT;
    0013 .FIELD FF5 * BYTE(10);
    0014 .FIELD FF6 * VARBYTE(10);
    0015 .FIELD FF7 * DECIMAL(8,2);
    0016 .FIELD FF8 * BYTEINT;
    0017 .FIELD FF9 * SMALLINT;
    0018 .FIELD FF10 * DATE;
     
    0019 .DML LABEL LABEL0044;
    0020  INSERT INTO TPTBL0044 VALUES (	:FF1,	:FF2,
         				:FF3,	:FF4,
         				:FF5,	:FF6,
         				:FF7,	:FF8,
         				:FF9,	:FF10);
     
    0021 .IMPORT INFILE ALLTYPE
                        LAYOUT LAY0044
                        APPLY LABEL0044;
    0022 .END LOAD;
    **** 17:11:15 UTY6609 Starting to log on sessions...
    **** 17:11:26 UTY6610 Logged on 4 sessions.
         ========================================================================
         =                                                                      =
         =          TPump Import(s) Beginning                                   =
         =                                                                      =
         ========================================================================
    **** 17:11:26 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:    No limit in effect.
         .   Restart Mode:    SIMPLE.
         .  Serialization:    OFF.
         .        Packing:    500 Statements per Request.
         .   StartUp Rate:    UNLIMITED Statements per Minute.
    **** 17:12:10 UTY6625 WARNING: Packing has changed to 254 statement(s) per
         request.
    **** 17:12:10 UTY6664 PACK factor was determined by the USING variable limit.
         Apply: 1, Stmt: 1.
    **** 17:12:12 UTY6608 Import 1 begins.
    **** 17:12:13 UTY6641 Since last chkpt., 20 recs. in, 20 stmts., 1 reqs
    **** 17:12:13 UTY6647 Since last chkpt., avg. DBS wait time: 1514.00
    **** 17:12:13 UTY6612 Beginning final checkpoint...
    **** 17:12:13 UTY6641 Since last chkpt., 20 recs. in, 20 stmts., 1 reqs
    **** 17:12:13 UTY6647 Since last chkpt., avg. DBS wait time: 1514.00
    **** 17:12:14 UTY6607 Checkpoint Completes with 20 rows sent.
    **** 17:12:14 UTY6642 Import 1 statements: 20, requests: 1
    **** 17:12:14 UTY6643 Import 1 average statements per request: 20.00
    **** 17:12:14 UTY6644 Import 1 average statements per record: 1.00
    **** 17:12:14 UTY6645 Import 1 statements/session: avg. 5.00, min. 0.00, max.
         20.00
    **** 17:12:14 UTY6646 Import 1 requests/session: average 0.25, minimum 0.00,
         maximum 1.00
    **** 17:12:14 UTY6648 Import 1 DBS wait time/session: avg. 378.50, min. 0.00,
         max. 1514.00
    **** 17:12:14 UTY6649 Import 1 DBS wait time/request: avg. 378.50, min. 0.00,
         max. 1514.00
    **** 17:12:14 UTY1823 Import processing statistics
         .                                           IMPORT  1      Total thus far
         .                                           =========      ==============
         Candidate records considered:....                  20                  20
         Apply conditions satisfied:......                  20                  20
         Records logable to error table:..                   0                   0
         Candidate records rejected:......                   0                   0
    **** Statistics for Apply Label : LABEL0044
         Type:                 I
         Database:             HEMANTHDB
         Table or Macro Name:  TPTBL0044
         Activity:             20
    **** 17:12:14 UTY6677 Loading phase statistics 
                  Elapsed time:   00:00:00:02(dd:hh:mm:ss)
                  CPU time:       0 Seconds
                  MB/sec:         0.000946
                  MB/cpusec:      N/A
    **** 17:12:16 UTY0821 Error table HEMANTHDB.TPERR0044 is EMPTY, dropping table.
    0023 .LOGOFF;
         ========================================================================
         =                                                                      =
         =          Logoff/Disconnect                                           =
         =                                                                      =
         ========================================================================
    **** 17:12:20 UTY6216 The restart log table has been dropped.
    **** 17:12:20 UTY6212 A successful disconnect was made from the RDBMS.
    **** 17:12:20 UTY2410 Total processor time used = '5.46003 Seconds'
         .       Start : 17:11:00 - FRI JAN 10, 2014
         .       End   : 17:12:20 - FRI JAN 10, 2014
         .       Highest return code encountered = '0'.