15.00 - ARRAY Data Types Specifications - Parallel Data Pump

Teradata Parallel Data Pump Reference

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

ARRAY Data Types Specifications

A column that is defined as an ARRAY data type in a Teradata table must be specified as a VARCHAR data type in the Teradata TPump FIELD statement. The external representation for an ARRAY data type is VARCHAR.

The following is a sample Teradata table definition which includes ARRAY data types:

CREATE SET TABLE SOURCE_TABLE ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      EMP_ID INTEGER,
      EMP_NO BYTEINT,
      COL003 SYSUDTLIB.PHONENUMBERS_ARY,
      COL004 SYSUDTLIB.DECIMAL_ARY,
      COL005 SYSUDTLIB.INTEGER_ARY)
UNIQUE PRIMARY INDEX ( EMP_ID );

The following is a sample definition for the PHONENUMBERS_ARY data type:

CREATE TYPE PHONENUMBERS_ARY AS CHAR(10) CHARACTER SET LATIN ARRAY [2];

The following is a sample definition for the DECIMAL_ARY data type:

CREATE TYPE DECIMAL_ARY AS DECIMAL(5,2) ARRAY[2];

The following is a sample definition for the INTEGER_ARY data type:

CREATE TYPE INTEGER_ARY AS INTEGER ARRAY[2];

The following is a sample LAYOUT definition for the sample SOURCE_TABLE

table:

  .LAYOUT EMPDATAWITHARRAY;
  .FIELD  EMP_ID * INTEGER;
  .FIELD  EMP_NO * BYTEINT;
  .FIELD  COL003 * VARCHAR(47);
  .FIELD  COL004 * VARCHAR(17);
  .FIELD  COL005 * VARCHAR(25;

In the above example, the COL003 column is defined as VARCHAR(47), because it's the maximum representation for the COL003 column in the table.

The following is the calculation for the maximum representation for the COL003 column:

1 byte for the left parenthesis

+ 1 byte for the single quote

+ 10 to 20 bytes for the first element

+ 1 byte for the single quote

+ 1 byte for the comma

+ 1 byte for the single quote

+ 10 to 20 bytes for the second element

+ 1 byte for the single quote

+ 1 byte for the right parenthesis

‑‑‑‑

47 bytes

The following are two samples of data for the COL003 column:

Sample data 1: ('3105551234','3105551234')

Sample data 2: ('''''''''''''''''''''','''''''''''''''''''''')

Sample data 1 contains 2 elements of phone numbers. Sample data 2 contains 2 elements of all single quote characters.

In the above example, the COL004 column is defined as VARCHAR(17), because it's the maximum representation for the COL004 column in the table.

The following is the calculation for the maximum representation for the COL004 column:

1 byte for the left parenthesis

+ 1 to 7 bytes for the first element

+ 1 byte for the comma

+ 1 to 7 bytes for the second element

+ 1 byte for the right parenthesis

‑‑‑‑

17 bytes

The following are two samples data for the COL004 column:

Sample data 1: (‑123.45,888.10)

Sample data 2: (+123.45,‑888.10)

In the above example, the COL005 column is defined as VARCHAR(25), because it's the maximum representation for the COL005 column in the table.

The following is the calculation for the maximum representation for the COL005 column:

1 byte for the left parenthesis

+ 1 to 11 bytes for the first element

+ 1 byte for the comma

+ 1 to 11 bytes for the first element

+ 1 byte for the right parenthesis

‑‑‑‑

25 bytes

The following are two samples of data for the COL005 column:

Sample data 1: (‑2147483648,+2147483647)

Sample data 2: (0,0)

Use the Teradata SQL "HELP TYPE" command to find out the maximum length for the ARRAY data type. For example, the information for the sample PHONENUMBERS_ARY, DECIMAL_ARY, and INTEGER_ARY ARRAY data types can look as follows:

help type PHONENUMBERS_ARY;
 
 *** Help information returned. One row.
 *** Total elapsed time was 1 second.
 
             Name PHONENUMBERS_ARY
    Internal Type A1
    External Type CV
       Max Length            47
       Array(Y/N) Y
       Dimensions             1
     Element Type CF
         UDT Name ?
      Array Scope [1:2]
     Total Digits    ?
Fractional Digits    ?
     Contains Lob N
         Ordering F
Ordering Category M
 Ordering Routine LOCAL
             Cast N
        Transform Y
           Method Y
        Char Type  1
 
HELP TYPE DECIMAL_ARY;
 
 *** Help information returned. One row.
 *** Total elapsed time was 1 second.
 
                     Name DECIMAL_ARY
            Internal Type A1
            External Type CV
               Max Length            17
     Decimal Total Digits    ?
Decimal Fractional Digits    ?
             Contains Lob N
                 Ordering F
        Ordering Category M
         Ordering Routine LOCAL
                     Cast N
                Transform Y
                   Method Y
                Char Type  1
               Array(Y/N) Y
               Dimensions             1
             Element Type D
                 UDT Name ?
              Array Scope [1:2]
 
HELP TYPE INTEGER_ARY;
 
 *** Help information returned. One row.
 *** Total elapsed time was 1 second.
 
                     Name INTEGER_ARY
            Internal Type A1
            External Type CV
               Max Length            25
     Decimal Total Digits    ?
Decimal Fractional Digits    ?
             Contains Lob N
                 Ordering F
        Ordering Category M
         Ordering Routine LOCAL
                     Cast N
                Transform Y
                   Method Y
                Char Type  1
               Array(Y/N) Y
               Dimensions             1
             Element Type I
                 UDT Name ?
              Array Scope [1:2]

As indicated in the returned information from the HELP TYPE command, the maximum length for the sample PHONENUMBERS_ARY ARRAY data type is 47 bytes. The maximum length for the sample DECIMAL_ARY ARRAY data type is 17 bytes. The maximum length for the sample INTEGER_ARY ARRAY data type is 25 bytes.

For more information about the external representations for the ARRAY data type, see SQL Data Types and Literals (B035‑1143).

FILLER

Purpose  

The FILLER command describes a named or unnamed field as filler which is not to be sent to Teradata Database. Only fields relevant to this Teradata TPump task need to be specified.

Syntax  

where

 

Syntax Element

Description

fieldname

Name of an input record field to which a nullexpr of a FIELD command refers; or to which a “condition” expression of the IMPORT command’s APPLY clause refers

The only reason for naming a filler field is to enable one of these expressions to refer to it. A fieldname must obey the same rules for its construction as Teradata SQL column names.

The reason for describing a field that is not to be sent to Teradata Database and is not used in any of the expressions mentioned in the previous paragraph is to make it possible to specify startpos as an asterisk for subsequent fields of the input records. If the use of the asterisk is not important, fields that do not participate in the Teradata TPump do not need to be defined.

startpos

Starting position of a field of the data records in an external data source

It may be specified as an unsigned decimal integer, which is a character position starting with 1, or as an asterisk, which is the next available character position beyond the preceding field.

Note that where input records may be continued by use of the CONTINUEIF condition, a startpos specified as an unsigned integer refers to a character position in the final concatenated result from which the continuation indicators have been removed. Refer to the description of the condition parameter of the LAYOUT command.

datadesc

Type and length of data in the field

Usage Notes

One or more FILLER commands may be intermixed with the FIELD command or the TABLE command. These commands must follow a LAYOUT command.

Example  

This example illustrates the use of the GRAPHIC data types in support of kanji or multibyte character data. The FILLER statement describing the input data set or file can contain GRAPHIC data types.

.LAYOUT KANJIDATA;
.FIELD EMPNO   * SMALLINT;
.FIELD LASTNAME * GRAPHIC(30);
.FILLER FIRSTNAME * GRAPHIC(30);
.FIELD JOBTITLE * VARGRAPHIC(30);

IF, ELSE, and ENDIF

Purpose  

Teradata TPump provides a structure of IF, ELSE, and ENDIF commands for the conditional control of execution processes. Conditional execution works as follows:

Syntax  

where

 

Syntax Element

Description

conditional expression

Userdefined variables or predefined system variables following the IF command, whose condition (TRUE or FALSE) triggers the execution of alternative groups of statements

statements to execute if TRUE

Statements to be executed whenever the conditional expression following the IF command evaluates as TRUE

statements to execute if FALSE

Statements following the optional ELSE command which execute only when the conditional expression following the IF command evaluates as FALSE

statements to resume with

Statements following the ENDIF command to terminate the conditional statement execution process and resume the normal command sequence

Usage Notes  

The conditional expression in the IF command may consist of either user‑defined variables or predefined system variables.

The ELSE command clause is optional. ELSE is used only when there are statements to be executed when the condition is evaluated as false. Conditional expression is an expression which can be evaluated as either true or false. When evaluation of the expression returns a numeric result, 0 is interpreted as false; nonzero results are interpreted as true. See “Utility Variables” on page 56.

Teradata TPump supports the nesting of IF commands to a level of 100.

Any ELSE or ENDIF commands must be present in their entirety and cannot be composed simply of variables in need of substitution.

Commands and statements following an IF, ELSE, or ENDIF structure that are not executed are not parsed and do not have their variables substituted.

Example  

Teradata TPump is case sensitive when doing a compare on an &SYS system variable. The RUN FILE command does not execute because the substituted values returned in this example are all in uppercase. This factor must be considered when creating a script to force the execution of a predetermined sequence of events. If, in line 0003, 'FRI' was used, the compare would work and the RUN FILE command would execute.

0003 .IF '&SYSDAY' = 'Fri' THEN;
14:10:28  FRI MAY 09, 1997
UTY2402 Previous statement modified to:
0004 .IF 'FRI' = 'Fri' THEN;
0005 .RUN FILE UTNTS38;
0006 .ENDIF;  

Example  

In Example 2, the user has created the table named &TABLE and a variable named CREATERC, into which is set the system return code resulting from the execution of the CREATE TABLE statement. If the table name has not already been used, and the return code is not zero, the return code evaluates to an error condition and the job logs off with the error code displayed.

0010 .SET CREATERC TO &SYSRC;
0011 .IF &CREATERC = 3803 /* Table &TABLE already exists */ THEN;
UTY2402 Previous statement modified to:
0012 .LOGOFF 08;
0013 .RUN FILE RUN01;
0014 .ELSE
0015 .IF &CREATERC <> 0 THEN
0016 .LOGOFF &CREATRC;
0017 .ENDIF

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