17.00 - Syntax - 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
Mainframe-Attached Client Systems

Workstation-Attached Client Systems

where the following is true:

INFILE ddname
External data source that contains the input records on mainframe-attached z/OS client systems. In z/OS, this is a DDNAME.
If DDNAME is specified, Teradata TPump reads data records from the specified source. If modulename is also specified, Teradata TPump passes the records it reads to the specified module.
The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set.
A DDNAME must obey the same construction rules as Teradata SQL column names except that:
  • The “at” character (@) is allowed as an alphabetic character
  • The underscore character (_) is not allowed
If the DDNAME represents a data source on magnetic tape, the tape may be either labeled or non-labeled, as supported by the operating system.
AXSMOD name
Name of the access module file to be used to import data. These access modules include:
  • Teradata Access Module for OLE DB: oledb_axsmod.dll on Windows platforms
  • Teradata Access Module for Named Pipes:
    • np_axsmod.so on all supported UNIX platforms
    • np_axsmod.dll on Windows platforms
  • Teradata Access Module for WebSphere MQ (client version):
    • libmqsc.so on all supported UNIX platforms
    • libmqsc.dll on Windows platforms
  • Teradata Access Module for WebSphere MQ (server version):
    • libmqs.so on all supported UNIX platforms
    • libmqs.dll on Windows platforms
  • Teradata Access Module for JMS:
    • libjmsam.so on all supported UNIX platforms
    • libjmsam.dll on Windows platforms
A personal shared library file name can be used if a custom access module is used.
The AXSMOD option is not required for importing disk files on either workstation-attached or mainframe-attached client systems, or magnetic tape files on mainframe-attached client systems. It is required for importing magnetic tape and other types of files on workstation-attached client systems.
'init-string'
Optional initialization string for the access module.
The initialization string can contain double quotes, but not single quotes.
INFILE filename
Fully qualified UNIX or Windows path name for an input file on workstation-attached client systems.
If the path name has embedded white space characters, the entire path name must be enclosed in single or double quotes.
To specify the INFILE filename, the data is read from the specified source. To specify the INMOD modulename, the data is passed to the specified module.
HOLD
Default condition to not deallocate the input tape device specified by ddname when the import operation completes on mainframe-attached client systems.
Instead, the HOLD specification deallocates the device when the entire Teradata TPump operation completes.
FREE
Deallocation of the tape input device specified by ddname when the import operation completes on mainframe-attached client systems.
When deallocated, any attempt to open the input device, either in the same Teradata TPump utility task or in another task within the same script, produces an undefined ddname error.
The default is to not deallocate the device.
INMOD modulename
Optional user-written routine for preprocessing the input data.
In z/OS, the modulename is the name of a load module. In UNIX and Windows systems, it is the pathname for the INMOD executable code file.
The modulename must obey the applicable rules of the external system.
A modulename must obey the same construction rules as Teradata SQL column names except that on mainframe-attached client systems:
  • The “at” character (@) is allowed as an alphabetic character
  • The underscore character (_) is not allowed
When both the INFILE fileid and the INMOD modulename parameters are specified, the input file is read and the data is passed to the INMOD routine for preprocessing.
If the INFILE fileid parameter is not specified, the INMOD routine must provide the input data record.
When an INMOD routine is used with the INFILE specification, Teradata TPump performs the file read operation, and the INMOD routine acts as a pass-through filter.
Because the FDL-compatible INMOD routine must always perform the file read operation, an FDL-compatible INMOD routine cannot be used with the INFILE specification of a Teradata TPump IMPORT command.
  • On some versions of UNIX OS, ./ prefix characters may have to be added to the modulename specification if the module is in the current directory.
  • On Windows platforms, if INMOD module output messages to stdout, the character set that INMOD uses is independent of the character set that Teradata TPUMP uses, the display on stdout can be of mixed character sets. For example, IMMOD can output messages in ASCII and Teradata TPUMP can output messages in UTF-16.
USING (parms)
Character string with the parameters passed to the user exit routine.
The parms string can include one or more character strings, each delimited on either end by an apostrophe or quotation mark.
Maximum size of the parms string is 1K bytes.
Parentheses within delimited character strings or comments have the same syntactical significance as alphabetic characters.
Before passing the parms string to the user exit routine, the following items are replaced with a single blank character:
  • Each comment.
  • Each consecutive sequence of white space characters, such as blank, tab and so on, that appears outside of delimited strings.
The entire parms string must be enclosed in parentheses. On mainframe-attached client systems, the parentheses are included in the string passed to the user exit routine.
The parms string must be FDLINMOD for the user exit routines written for the prior Pascal version of the FastLoad utility (program FASTMAIN).
FROM m
Logical record number, as an integer, of the record in the identified data source where processing is to begin.
If a FROM m specification is not used, Teradata TPump begins processing with the first record received from the data source.
FOR n
Number of records, as an integer, starting at record m to be processed.
If a FOR n or a THRU k specification is not used, Teradata TPump continues processing through the last record obtained from the data source.
When “FOR 0” is used, Teradata TPump defaults “FROM as 2” and “THRU as 1”. A warning is issued, and Teradata TPump loads only the second record of the data file to the target table.
THRU k
Logical record number, as an integer, of the record in the identified data source where processing is to end.
If a THRU k or a FOR n specification is not used, Teradata TPump continues processing through the last record obtained from the data source.
FORMAT
Record format of the input file.
The format can be:
  • FASTLOAD – A 2-byte integer, n, followed by n bytes of data and an end-of-record marker (either X'0A' or X'0D').
  • BINARY – A 2-byte integer, n, followed by n bytes of data.
  • TEXT – An arbitrary number of bytes, followed by an end-of-record marker which is a:
    • Line feed (X'0A') on UNIX platforms.
    • Carriage-return and line-feed pair (X'0D0A') on Windows platforms.
    TEXT format should only be specified for character data. Do not specify TEXT format for binary data, such as, PERIOD data.
    • INDICATORS mode is not recommended when using TEXT record format. Please use UNFORMATTED record format instead.
    • TEXT data requires all CHAR or ANSIDATE data types.
  • UNFORMAT – defined by FIELD, FILLER, and TABLE commands of the specified layout.
    When using UNFORMAT formatting in z/OS, ensure that the data stream and data source are consistent with the layout defined in the utility script. Discrepancies in the length of the data stream could result in data corruption.
  • VARTEXT – In variable-length text record format, with each field separated by delimiter character(s). The delimiter can be a single or multi-character sequence (or string). If the delimiter is not specified, the default is the character sequence consists of a single pipe character (|).
If the script character set is different from the client session character set, the delimiter is converted from the script character set to the client session character set before it is passed to Data Connector.
  • Any character sequence that appears in the data cannot be used as a delimiter. No control character other than a tab character can be used in a delimiter.
  • On the mainframe platform, when access module is not used, the default is the input data read record by record and the LAYOUT is applied to each read record.
'c'
Optional specification of the delimiter characters that separate fields in the variable-length text records of the input data source.
The default, if a 'c' specification is not used, is the vertical bar character ( | ).
When the character set of the job script is different from the client character set used for the job (for example, on z/OS the job script must be in Teradata EBCDIC when using the UTF-8 client character set, or UTF-16 client character set can be used with the job script in UTF-8), Teradata TPump will translate the effective delimiter from the script character set form to the client character set form before separating the fields with it.
For example, when the client character set is UTF-16 and the script character set is UTF-8, if the following command is given:
… FORMAT VARTEXT '-' ...
Teradata TPump translates '-' from the UTF-8 form to the UTF-16 form and then separate the fields in the record according to the UTF-16 form of '-'.
Similarly, on the mainframe, when the client character set is UTF-8 and the script character set is Teradata EBCDIC, if the following command is given:
… FORMAT VARTEXT '6A'xc ...
Teradata TPump interprets x'6A' according to Teradata EBCDIC and translates it to the corresponding Unicode code point, U+007C "VERTICAL LINE,” and uses the UTF-8 encoding scheme of U+007C, 0x7C (which is '|' in 7-bit ASCII), as the delimiter character for the record.
When using the UTF-8 client set on the mainframe, examine the definition Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125 to determine the code points of the special characters required. Different versions of EBCDIC do not always agree as to the placement of these characters.

For example, the code point of '|' in most IBM EBCDIC code pages is x'4F'. If a '|' is specified as the delimiter in the script or the delimiter to default in a system environment using such an IBM EBCDIC code page is left, (which is essentially the same as specifying '|'), but the UTF-8 data uses x'7C' ('|' in Unicode) as the delimiter, the job will run into errors because the code point of x'4F' in Teradata EBCDIC maps to U+008D but not U+007C.

DISPLAY ERRORS
Optional keyword specification that writes input data records that produce errors to the standard error file.
NOSTOP
Optional keyword specification that inhibits the Teradata TPump termination in response to an error condition associated with a variable-length text record.
LAYOUT layoutname
Layout of the input record, as specified by a previous command.
APPLY label
Error treatment options specified by a previous DML LABEL command for subsequent INSERT, UPDATE, or DELETE statements.
WHERE condition
Condition that determines whether the indicated label options are applied to the records and sent to the database, where:
  • condition true = yes
  • condition false = no
The condition specification can reference:
  • Any combination of fields defined in the currently active layout
  • System and user-defined constants and variables
  • The fieldname1 specified in commands
When VARTEXT is specified, the Teradata TPump utility assumes that the input data is variable-length text fields separated by field delimiter characters. The utility parses each input data record on a field-by-field basis, and creates a VARCHAR field for each input text field.
When the character set of the job script is different from the client character set used for the job (for example, on z/OS the job script must be in Teradata EBCDIC when using the UTF-8 client character set, or UTF-16 client character set can be used with the job script in UTF-8), Teradata TPump translates the string constants specified in the condition and the import data referenced in the condition to the same character set before evaluating the condition.
For example, when the client character set is UTF-16 and the script character set is UTF-8, if the following command is given
… APPLY lable1 WHERE C1 = 'INSERT';
Teradata TPump translates the data in the C1 field to the UTF-8 form and compares it with the UTF-8 form of 'INSERT' to obtain the evaluation result.
Similarly, on the mainframe, when the client character set is UTF-8 and the script character set is Teradata EBCDIC, if the following command is given:
… APPLY lable2 WHERE C2 = 'DELETE';
Teradata TPump translates the data in the C2 field from the UTF-8 form to the Teradata EBCDIC form and perform the comparison with the Teradata EBCDIC form of 'DELETE'.
When using the UTF-8 client set on the mainframe, be sure to examine the definition in Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125 to determine the code points of the special characters required. Different versions of EBCDIC do not always agree as to the placement of these characters. The mappings between Teradata EBCDIC and Unicode can be referred to in Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125.
EFILE <efilename>
Optional keyword specification that writes input data records that produce errors to the user specified error file. If the user does not specify the error file name, the default error destination is the standard error file for workstation-attached platforms, and the SYSOUT for mainframe-attached platforms.
TRIM
The TRIM option allows the user to request that no trimming is to be done, or that leading, trailing, or both leading and trailing pad characters are to be trimmed. The default pad character is blank (space).
If only TRIM keyword is specified, nothing will be trimmed.
QUOTE
The QUOTE option allows the user to specify whether input data values will never be quoted (QUOTE NO), optionally be quoted (QUOTE OPTIONAL), or always be quoted (QUOTE YES).
If data values are to be optionally or always quoted, the user can specify the enclosing open and close quote. The default is the quotation mark (“) for both open quote and close quote. The open quote ('q') and close quote ('r') can be different. If only 'q' is specified, it is assumed to be both the open quote and close quote. If open quote, close quote, or both include apostrophe(s), any apostrophes must be doubled in the QUOTE specification.
Currently, only a single character quote is supported.
If only QUOTE keyword is specified, it is equal to nothing is QUOTEed.
For the strings 'c','p','q', and 'r' with embedded single quotation marks, use the single quotation mark to escape the character within the string if single quotation mark exists within the string.