Log Table Space Requirements
The calculation of space requirements for a Teradata TPump log table is highly dependent on the specifics of the job. Although there are mandatory inserts for every Teradata TPump job, others occur on a job‑dependent basis. See “Estimating Space Requirements” for details on how to calculate log table space.
NAME
Purpose
The NAME command assigns a unique job name identifier to the environmental variable &SYSJOBNAME.
Syntax
where
|
Syntax Element |
Description |
|
jobname |
Character string that identifies the name of a job in a maximum of 16 characters If this command is not specified, the default job name of ltdbase_logtable is used, where: |
Usage Notes
The NAME environmental command must be used only once, in order to set the job name and the variable &SYSJOBNAME. Further attempts to execute the command will fail.
The NAME command sets the variable &SYSJOBNAME to the specified string. The string is truncated to 16 characters. It is an error to use this command more than once in a Teradata TPump script or after the first BEGIN LOAD command in the script.
If &SYSJOBNAME is not set using the NAME command, it defaults to MYYYYMMDD_HHMMSS_LLLLL, where
M = macro
MM = month
DD = day
YYYY = year
hh = hour
mm = minute
ss = second
lllll = is the low order 5 digits of the logon sequence number returned by the database
from the .LOGON command.
This variable is not set until created with the NAME command, or with the first BEGIN LOAD by default. Any attempt to use it before a NAME command is issued (or before the first BEGIN LOAD if there is no NAME command), results in a syntax error. This variable is significant because it is used by Teradata TPump when composing default names for various database artifacts, namely the error table and Teradata TPump‑created macros.
Note: If serialization for two or more DML statements is required, they cannot be put in different partitions. Serialization requires that all DML statements with identical hash values of the rows be submitted from the same session.
PARTITION
Purpose
The PARTITION command defines a collection of sessions used to transfer SQL requests to Teradata Database. A DML command may name the partition to be used for its requests to the database.
A default session partition may still be created using the SESSIONS and PACK parameters of the BEGIN LOAD command.
This command works in conjunction with a DML parameter, PARTITION, which names the session partition that a DML’s SQL will use. If the DML command does not have a PARTITION parameter, then the default partition created using the SESSIONS and PACK parameters of the BEGIN LOAD command will be used.
Syntax
where
|
Syntax Element |
Description |
||
|
number |
Number of sessions to be logged on for the partition Teradata TPump logs on and uses the number of sessions specified to communicate requests to Teradata Database. There is no default value for number; it must be specified. Neither is there a maximum value, except for system‑wide session limitations, which vary among machines. Limiting the number of sessions conserves resources on both the external system and Teradata Database. This conservation is at the expense of a potential decrease in throughput and increase in elapsed time. |
||
|
DATAENCRYPTION ON/OFF |
Keyword to encrypt import data and the request text during the communication between Teradata TPump and Teradata Database for the sessions defined in the PARTITION command If ON, the encryption will be performed. If OFF, the encryption will not be performed. If DATAENCRYPTION is not specified, the default is OFF when "‑y" runtime parameter is not specified and DATAENCRYPTION is OFF in the BEGIN LOAD command. If "‑y" runtime parameter is specified or DATAENCRYPTION is ON in the BEGIN LOAD command, the default is ON. This option applies to the sessions defined by the PARTITION command. When the session is specified explicitly, the setting overrides the encryption setting by the "‑y" runtime parameter and by the DATAENCRYPTION option in the BEGIN LOAD command for the sessions defined in the PARTITION command. |
||
|
PACK |
Keyword for the number of statements to pack into a multiple‑statement request Maximum value is 1500. Packing improves network/channel efficiency by reducing the number of sends and receives between the application and Teradata Database. |
||
|
PACKMAXIMUM |
Keyword requesting Teradata TPump to dynamically determine the maximum possible PACK factor for the current partition Maximum value is 1500. Displayed in message UTY6652, the value thus determined should be specifically used on subsequent runs, as the use of PACKMAXIMUM requires iterative interactions with the database during initialization to heuristically determine the maximum possible PACK factor. |
||
|
partition_name |
Name assigned to the partition for reference by one or more subsequent DML commands A partition name must obey the same rules for its construction as Teradata SQL column names. The name specified may be used in the PARTITION clause of a DML command. |
||
|
SESSIONS |
Keyword for designating the number of sessions for the partition |
||
|
statements |
Number of statements, as a positive integer of up to 1500, to pack into a multiple‑statement request Default value is 20 statements per request. Note: Under certain conditions, Teradata TPump may determine that the pack factor has been set too high. Teradata TPump then automatically lowers the pack setting to an appropriate value and issues warning message UTY6625, for example: “UTY6625 WARNING: Packing has been changed to 12 statements per request”, and continues. Packing improves network/channel efficiency by reducing the number of sends/receives between the application and the database. The packing factor is validated by sending a fully packed request to Teradata Database using a prepare. This test checks for syntax problems and requests that are excessively large and overwhelm the parser. To simplify the script development process, Teradata TPump ignores certain errors returned by an overloaded parser, shrinks the request, retries the prepare until it executes successfully and finally, issues a warning noting the revised packing factor size. When this happens, the Teradata TPump script should be modified to eliminate the warning, which avoids the time‑consuming process of shrinking the request. Note: A packing failure may occur if the source parcel length does not match the data defined. If this happens, Teradata TPump issues the message: “UTY2819 WARNING: Packing may fail because input data does not match with the data defined.” To resolve this problem, increase the packing factor and resubmit the job. |
||
|
threshold |
Minimum number of sessions to be logged on for the partition When logging on sessions, if system limits are reached above the threshold value, Teradata TPump stops trying to log on, and uses whatever sessions are already logged on. If the sessions run out before the threshold is reached, Teradata TPump logs off all sessions, waits for the time determined by the SLEEP value (specified in the BEGIN LOAD command), and tries to log on again. |
||
Example
A sample script that uses partitioning follows:
.LOGTABLE TPLOG01;
.LOGON <TDPID>/<USERID>,<PASSWORD>;
DROP TABLE TPTBL01;
DROP TABLE TPTBL02;
DROP TABLE TPERR01;
CREATE TABLE TPTBL01, FALLBACK(
C1 CHAR(12) not null,
C2 CHAR(8) not null)
PRIMARY INDEX (C1);
CREATE TABLE TPTBL02, FALLBACK(
C1 CHAR(12),
C2 CHAR(8),
C3 CHAR(6))
UNIQUE PRIMARY INDEX (C1);
.BEGIN LOAD
ERRLIMIT 100 50
CHECKPOINT 15
TENACITY 2
ERRORTABLE TPERR01
ROBUST off
serialize on
;
.LAYOUT LAY02;
.FIELD cc1 * CHAR(12) key;
.FIELD cc2 * CHAR(8);
.FIELD cc3 * CHAR(6);
.filler space1 * char(1);
.partition part1 pack 10 sessions 10;
.partition part2 sessions 5 1 packmaximum;
.DML LABEL LABEL01 partition part1
DO INSERT FOR MISSING ROWS
ignore extra update rows
use(cc1, cc2);
UPDATE TPTBL01
SET C2 = :CC2
WHERE C1 = :CC1;
INSERT TPTBL01 (C1, C2)
VALUES (:CC1,:CC2);
.DML LABEL LABEL02 partition part2
serializeon( cc1 )
ignore extra update rows
DO INSERT FOR MISSING UPDATE ROWS;
UPDATE TPTBL02 SET C2 = :CC2 WHERE C1 = :CC1;
INSERT TPTBL02 (C1, C2, C3)
VALUES (:CC1,:CC2,:CC3);
.IMPORT INFILE TpumpData001.txt FORMAT TEXT
LAYOUT LAY02
APPLY LABEL01
APPLY LABEL02 where CC2 = '00000001';
.END LOAD;
.LOGOFF;
ROUTE
Purpose
The ROUTE command identifies the destination of various outputs produced by Teradata TPump.
Syntax
where
|
Syntax Element |
Description |
|
MESSAGES |
Preferred location where the messages be redirected (normally written to DDNAME SYSPRINT in z/OS or stdout in UNIX systems); that is, sent to an additional destination, or both |
|
fileid1 and fileid2 |
Alternate message destination in the external system UNIX and Windows Systems Fileid is the path name for a file. If the path name has embedded white space characters, enclose the entire path name in single or double quotes. If the path name is enclosed by single quotation marks and there is an embedded single quotation mark, then an escape character (single quotation mark) needs to precede the embedded single quotation mark. Likewise, if the path name is enclosed by double quotation marks and there is an embedded double quotation mark, then an escape character (double quotation mark) needs to precede the embedded double quotation mark. If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks. |
|
ECHO |
Additional destination, with a fileid specification Use the ECHO keyword to specify, for example, that messages be captured in a file (fileid2) while still being written to the terminal. Note: The ECHO OFF specification cancels the additional file specification of a previously established ECHO destination. |
Usage Notes
In z/OS, fileid is a true DDNAME; and in the UNIX OS, it is a file pathname. If DDNAME is specified, Teradata TPump writes data records to the specified destination. A DDNAME must obey the same rules for its construction as Teradata SQL column names except that the “at” sign (@) is allowed as an alphabetic character and the underscore ( _ ) is not allowed. The DDNAME must also obey the applicable rules of the external system and may reference a sequential or VSAM data set. If DDNAME represents a data source on magnetic tape, the tape may be either labelled or nonlabelled (if the operating system supports it).
On UNIX systems, an asterisk (*) can be used as the fileid1 or fileid2 specifications to route messages to the system console/standard output (stdout) device. The system console is the:
or
Example
.ROUTE MESSAGES TO FILE OUTPUT WITH ECHO TO FILE SYSOUT;
ECHO, when specified with OFF, stops routing output to the previously established echo destination.
Example
.ROUTE MESSAGES FILE OUTPUT;
The messages are written to the file designated by OUTPUT from this point unless redirected by another ROUTE command.
In UNIX systems, if “outfilename” is used to redirect “stdout,” and also as the file in a ROUTE WITH ECHO command, the results written to “outfilename” may be incomplete due to conflicting writes to the same file.
RUN FILE
Purpose
The RUN FILE command invokes the specified external source as the current source of commands and statements.
Syntax
where
|
Syntax Element |
Description |
|
fileid |
Data source of the external system The client system DD or equivalent statement specifies a file. UNIX and Windows Systems infilename (the path name for a file). If the path name has embedded white space characters, enclose the entire path name in single or double quotes. If the path name is enclosed by single quotation marks and there is an embedded single quotation mark, then an escape character (single quotation mark) needs to precede the embedded single quotation mark. Likewise, if the path name is enclosed by double quotation marks and there is an embedded double quotation mark, then an escape character (double quotation mark) needs to precede the embedded double quotation mark. If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks. z/OS a true DDNAME. If DDNAME is specified, Teradata TPump reads data records from the specified source. A DDNAME must obey the same rules for its construction as Teradata SQL column names, except that: The DDNAME must also obey the applicable rules of the external system and may reference a sequential or VSAM data set. If DDNAME represents a data source on magnetic tape, the tape may be either labelled or nonlabelled (if the operating system supports it). The “at” sign (@) is allowed as an alphabetic character and the underscore (_) is not allowed. |
|
charpos1 and charpos2 |
Start and end character positions of a field in each input record which contains extraneous information Teradata TPump ignores the specified field(s) as follows: |
Usage Notes
Once Teradata TPump executes the RUN FILE command, further commands and DML statements are read from the specified source until a LOGOFF command or end‑of‑file condition is encountered, whichever occurs first. An end‑of‑file condition automatically causes Teradata TPump to resume reading its commands and DML statements from the previously active source (or the previous RUN source when RUNs are nested), either SYSIN for z/OS, or stdin (normal or redirected) in the UNIX OS. After SYSIN/stdin processes any user‑provided invocation parameter, it remains the active input source.
If an end‑of‑file condition occurs on fileid, SYSIN/stdin is read because there are no more commands or statements in the PARM string.
The command source specified by a RUN FILE command may contain nested RUN FILE commands to 16 levels.
On UNIX systems, an asterisk (*) can be used as the fileid specification for the system console/standard input (stdin) device. The system console is the:
or
Example
.RUN FILE LOGON;
SET
Purpose
The SET command assigns a data type and a value to a utility variable. Variables need not be declared in advance to be the object of the SET command. If a variable does not already exist, the SET command creates it.
The SET command also dynamically changes the data type to that of the assigned value if it has already been defined. Variables used to the right of TO in the expression must have already been defined.
Syntax
where
|
Syntax Element |
Description |
|
var |
Name of the utility variable which is set to the evaluated expression following it |
|
expression |
Value and data type to which the utility variable is to be set |
Usage Notes
The utility variable can be substituted wherever substitution is allowed.
If the expression evaluates to a numeric value, the symbol is assigned an integer value, as in:
.SET FOONUM TO ‑151 ;
If the expression is a quoted string, the symbol is assigned a string value, as in:
.SET FOOCHAR TO '‑151' ;
The minimum and maximum limits for Floating Point data types are as follows:
4.0E‑75 <=abs(float variable)<7.0E75
Example
Teradata TPump supports concatenation of variables, using the SET command, such as:
.SET C TO 1;
.SET D TO 2;
.SET X TO &C.&D;
Example
In this example, X evaluates to 12. If a decimal point is added to the concatenated variables, as in:
.SET C TO 1;
.SET D TO 2;
.SET X TO &C..&D;
X then evaluates to 1.2.
SYSTEM
Purpose
The SYSTEM command allows access to the local operating system during Teradata TPump operations.
Syntax
where
|
Syntax Element |
Description |
|
‘oscommand' |
Command string (enclosed within single quotes) that is appropriate to the local operating system The SYSTEM command suspends the current Teradata TPump application in order to execute the command. When the command completes, the return code from the invoked command is displayed, and the &SYSRC variable is updated with the return code. |
Usage Notes
On z/OS clients, the command is passed to the PGM executor. The first token of the command string is interpreted as a load module and the remainder as a PARM string. As an example, the following statement calls the load module IEBUPDTE, passing the PARM string “NEW”.
.SYSTEM “IEBUPDTE NEW”;
This command calls IEBUPDTE in the same way it is called via the JCL statement:
//EXEC PGM=IEBUPDTE,PARM='NEW'
On z/OS, the program must be present in the STEPLIB or JOBLIB concatenation, be resident in one of the LPAs, or be located in the linklist concatenation.
Otherwise, the invocation will fail, with code SYS_ABTM (‑14) returned, resulting from an underlying abend S806‑04. Other types of failures also are possible.
On UNIX OS clients, the SYSTEM command invokes the standard UNIX OS interface to issue the command to the shell (sh), and waits for its completion.
TABLE
Purpose
The TABLE command identifies a table whose column names and data descriptions are used as the names and data descriptions of the input record fields. These are assigned in the order defined. The TABLE command must be used with the LAYOUT command.
Syntax
where
|
Syntax Element |
Description |
|
tableref |
Existing table whose column names and data descriptions are assigned, in the order defined, to fields of the input data records The column names of the table specified by the TABLE command must be Teradata SQL column names that do not require being enclosed in quotation marks. Tables cannot be created with invalid column names. Any nonstandard column name causes one of three kinds of errors, depending on the nature of the divergence from the standard. These errors are: |
Usage Notes
One or more TABLE commands may be intermixed with the FIELD command or FILLER command following a LAYOUT command.
This method of specifying record layout fields assumes each field, as defined by the data description of the corresponding column of tableref, is contiguous with the previous one, beginning at the next available character position beyond any previous field specifications for the input records. The fields must appear in the order defined for the columns of the table.
The object identified by the tableref parameter must be a table. It need not appear as a parameter of the BEGIN LOAD command, but the user must either be an owner of the object or have at least one privilege on it. If specified as an unqualified table name, the current default database qualifies it.
When serialization has been set to ON by the BEGIN LOAD command, the primary index columns for the specified table are considered key fields for serialization purposes.
When the TABLE command is used and the table contains a structured UDT type, Teradata TPump returns an external representation of the UDT and that requires the user to transform. The term “external type” means the data type of the external opaque container for a structured UDT and is the type returned by the from‑sql transform method.
TIMEOUTVALUE
Purpose
The TIMEOUTVALUE command specifies a maximum I/O completion wait time to Teradata WebSphere MQ Access Module.
Syntax
where
|
Syntax Element |
Description |
|
seconds |
A maximum I/O completion wait time to Teradata MQ Access Module. Valid range: 1-99999 (seconds) |
Usage Notes
When TIMEOUTVALUE command is presented, the value overwrites that of RWAIT option in Teradata WebSphere MQ Access Modules initial string.
UPDATE Statement and Atomic Upsert
Purpose
Teradata TPump supports the UPDATE Teradata SQL statement, which changes field values in existing rows of a table.
Syntax
where
|
Syntax Element |
Description |
|
tname |
Table or view to be updated This table was previously identified as tname in the BEGIN LOAD command. If tname is not explicitly qualified by database name, the current default database qualifies it. |
|
cname |
Column whose value is to be replaced by the value of expr The column named must not be a column of the primary index. |
|
expr |
Expression whose resulting value is to replace the current value of the identified column The expression can contain any combination of constants, current values of columns of the referenced row, or values from fields of input data records. References to fields of input data records are as follows: :fieldname where :fieldname is defined by a FIELD command or TABLE command of the layout referenced by an IMPORT using this UPDATE. |
|
WHERE condition |
Conditional clause specifying the row or rows to be updated The conditional clause can use values from fields of input data records by referring to their field names as follows: :fieldname where fieldname is defined by a FIELD command or TABLE command. Equality values for all the columns of the primary index must be explicitly specified in this clause. |
Usage Notes ‑ Update
The following notes describe how to use an UPDATE statement following a DML command. An UPDATE statement may also be used in the support environment; normal rules for UPDATE are followed in that case.