IMPORT - Parallel Data Pump

Teradata Parallel Data Pump Reference

Product
Parallel Data Pump
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-3021
lifecycle
previous
Product Category
Teradata Tools and Utilities

IMPORT

Purpose  

The IMPORT command identifies a source for data input. By referencing the LAYOUT command and DML command, IMPORT ties the previous commands together. The input data source used for IMPORT depends on whether the Teradata TPump utility is running on an IBM z/OS client, or on a network‑attached client platform, as shown in the following syntax diagram.

Syntax  

For MainframeAttached Client Systems:

For NetworkAttached Client Systems:

where

 

Syntax Element

Description

INFILE ddname

External data source that contains the input records on mainframeattached 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 nonlabeled, as supported by the operating system.

    AXSMOD name

    Name of the access module file to be used to import data. These access modules include:

  • OLE DB Access Module: oledb_axsmod.dll on Windows platforms
  • Named Pipes Access Module: See the Teradata Tools and Utilities Access Module Reference (B035‑2425) for the name of the access module file for each platform.
  • Teradata WebSphere® MQ Access Module (client version): See the Teradata Tools and Utilities Access Module Reference (B035‑2425) for the name of the access module file for each platform.
  • Teradata WebSphere®MQ Access Module (server version): See the Teradata Tools and Utilities Access Module Reference (B035‑2425) for the name of the access module file for each platform.
  • Teradata Access Module for JMS (libjmsam.so on AIX, Solaris SPARC, Solaris Opteron, Linux and z/Linux platforms, libjmsam.sl on HPUX pa RISC and HPUX Itanium platforms and libjmsam.dll on Windows platforms)
  • A personal shared library file name can be used if custom access module is used.

    The AXSMOD option is not required for importing disk files on either networkattached or mainframeattached client systems, or magnetic tape files on mainframeattached client systems. It is required for importing magnetic tape and other types of files on networkattached client systems.

    To specify the OLE DB Access Module, Named Pipes Access Module, or the WebSphere MQ Access Module for specific platforms, see Teradata Tools and Utilities Access Module Reference.

    'initstring'

    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 networkattached 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 mainframeattached 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 userwritten 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 mainframeattached 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.

    Note: When an INMOD routine is used with the INFILE specification, Teradata TPump performs the file read operation, and the INMOD routine acts as a passthrough filter.

    Because the FDLcompatible INMOD routine must always perform the file read operation, an FDLcompatible INMOD routine cannot be used with the INFILE specification of a Teradata TPump IMPORT command.

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

    Note: 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 UTF16.

    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 mainframeattached client systems, the parentheses are included in the string passed to the user exit routine.

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

    Note: 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:

    FASTLOADA 2byte integer, n, followed by n bytes of data and an endofrecord marker (either X'0A' or X'0D').

    BINARYA 2byte integer, n, followed by n bytes of data.

    TEXTAn arbitrary number of bytes, followed by an end‑of‑record marker which is a:

  • Line feed (X'0A') on UNIX platforms.
  • Carriagereturn 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.

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

    UNFORMATdefined by FIELD, FILLER, and TABLE commands of the specified layout.

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

    VARTEXTin variablelength text record format, with each field separated by delimiter character(s). The delimiter can be a single or multicharacter 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.

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

    Note: 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 variablelength 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 UTF8 client character set, or UTF‑16 client character set can be used with the job script in UTF8), 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 UTF8, if the following command is given:

    … FORMAT VARTEXT '' ...

    Teradata TPump translates '' from the UTF8 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 UTF8 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 UTF8 encoding scheme of U+007C, 0x7C (which is '|' in 7bit ASCII), as the delimiter character for the record.

     

    Notice:

    When using the UTF‑8 client set on the mainframe, examine the definition 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 variablelength 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 Teradata 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 userdefined constants and variables
  • The fieldname1 specified in commands
  • When VARTEXT is specified, the Teradata TPump utility assumes that the input data is variablelength text fields separated by field delimiter character(s). The utility parses each input data record on a fieldbyfield 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 UTF8 client character set, or UTF‑16 client character set can be used with the job script in UTF8), 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 UTF8, if the following command is given

    … APPLY lable1 WHERE C1 = 'INSERT';

    Teradata TPump translates the data in the C1 field to the UTF8 form and compares it with the UTF8 form of 'INSERT' to obtain the evaluation result.

    Similarly, on the mainframe, when the client character set is UTF8 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 UTF8 form to the Teradata EBCDIC form and perform the comparison with the Teradata EBCDIC form of 'DELETE'.

    Notice:

    When using the UTF‑8 client set on the mainframe, be sure to examine the definition in 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 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 doesn't specify the error file name, the default error destination is the standard error file.

    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.

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

    Usage Notes  

    A maximum of 100 IMPORT commands can be used in a single Teradata TPump load task. A single load comprises the set of commands and statements bounded by a BEGIN LOAD‑END LOAD command pair. If the number of IMPORTs sent to Teradata Database for the load exceeds 100, TPump terminates with an error message. It is recommended to use a lower number of IMPORTs per load in order to limit the amount of memory needed to keep track of job‑related statistics.

    The maximum number of INSERT, UPDATE, and DELETE statements that can be referenced in an IMPORT is 128. The 128th DML which would cause the insertion of the DML sequence number of 128 for the DMLSEQ field in the error table could lead to Teradata Database 3520 error.

    The only DML statements that are candidates for application by an IMPORT command are those within the scope of DML commands whose labels appear in one or more of the IMPORT command’s APPLY clauses. The referenced DML commands and their following DML statement(s) must appear between the BEGIN LOAD command that defines the task and the referencing IMPORT commands. A statement or group of statements is applied if no condition is specified, or if the specified condition is true.

    Teradata TPump permits multiple statements to be applied to the same data record in either of two ways. First, if an APPLY clause refers to a label whose scope includes multiple DML statements, each of these statements is applied to the same data record under the same condition specified in the clause. Second, if multiple APPLY clauses are used, each can then refer to the label of a different DML statement or group of statements. Each label’s statements are applied to the same data record under that condition specified in the respective clause. These features allow the same data record to be applied to different tables under the same or differing conditions.

    VARTEXT Record Usage

    When VARTEXT is specified, Teradata TPump assumes that the input data is variable‑length text fields separated by a field delimiter character. It parses each input data record on a field‑by‑field basis, and creates a VARCHAR field for each input text field.

    When using the VARTEXT specification, VARCHAR, VARBYTE, and LONG VARCHAR are the only valid data type specifications to use in Teradata TPump layout FIELD and FILLER commands.

    Two consecutive delimiter characters direct Teradata TPump to null the field corresponding to the one immediately following the first delimiter character.

    Also, if the last character in a record is a delimiter character, and there is at least one more field to be processed, then Teradata TPump nulls the field corresponding to the next one to be processed, as defined in the layout FIELD and FIELD command.

    The total number of fields in each input record must be equal to or greater than the number of fields described in the Teradata TPump layout FIELD and FIELD commands.

    If it is less, Teradata TPump generates an error message. If it is more, Teradata Database ignores the extra fields.

    The last field of a record does not have to end with a delimiter character. It can end with a delimiter character, but it is not required.

    When Teradata TPump encounters an error condition in an input record, it normally discards the record and terminates. When loading variable‑length text records, inhibit either or both of these functions by specifying the error‑handling options:

  • DISPLAY ERRORS
  • NOSTOP
  • If NOSTOP is specified, Teradata TPump will not terminate even if an error is encountered.

    By specifying both options and redirecting STDERR to a file location instead of the terminal screen, the Teradata TPump job runs to completion and saves all the error records. Then the error records can be manually modified and loaded into the table.

    All IMPORT commands for a Teradata TPump task must appear between the BEGIN LOAD and END LOAD commands for the task.

    Teradata TPump imposes several syntax rules for the parms string for an INMOD user exit routine. On entry to any INMOD user exit routine for Teradata TPump, the conventional parameter register points to a parameter list of two 32‑bit addresses used to communicate with the INMOD.

    At the end of an IMPORT, an environmental variable is established for each DML command executed. Teradata TPump variables are not constrained to 30 characters. These variables contain the activity counts associated with each statement. The variables created are of the form:

    &IMP <n>_<Apply label>_<x> 

    where

    n = the number of the IMPORT, from 1 through 100.

    Apply label = the label of the clause containing the DML command in question.

    x = the number of the statement within the containing APPLY clause.

    The following script is an example of a Teradata TPump job using the APPLY keyword to create conditional clauses to apply DML INSERTs, UPDATEs, and UPSERTs to the IMPORT.