15.10 - Upsert Example - MultiLoad

Teradata MultiLoad Reference

prodname
MultiLoad
vrm_release
15.10
category
Programming Reference
featnum
B035-2409-035K

Upsert Example

Each record in the following upsert example contains the value of the primary index column (EmpNo) of a row of the Employee table whose PhoneNo column is to be assigned a new phone number from field Fone.

The example assumes that the current default database is Personnel, the database containing Employee.

.BEGIN IMPORT MLOAD TABLES Employee
.LAYOUT Layoutname; 
.FIELD EmpNum 1 INTEGER; 
.FIELD Fone * (CHAR (10)); 
.DML LABEL DMLlabelname
DO INSERT FOR MISSING UPDATE ROWS;
UPDATE Employee SET PhoneNo = :Fone WHERE EmpNo = :EmpNum;
INSERT Employee (EmpNo, PhoneNo) VALUES (:EmpNum, :Fone); 

When the update operation fails, the INSERT statement executes, per the upsert feature. In this case, each record contains the primary key value (EmpNum) of a row that is to be inserted successively into the Employee table whose columns are EmpNo and PhoneNo.

 

Purpose  

The END MLOAD command, which must be the last command of a Teradata MultiLoad task, signifies the end of the task script and initiates task processing by Teradata Database.

Syntax  

Purpose  

The EOC (End Of Console) command, which is primarily for developer use, indicates “end of console” when MultiLoad script is input from the console. Using the EOC command in MultiLoad batch mode will cause MultiLoad job to terminate.

Syntax  

Purpose  

The FIELD command specifies a field of the input record to be sent to Teradata Database. It can also specify a NULLIF expression.

Syntax  

where:

 

Syntax Element

Description

datadesc

Type and length of data in the field

Teradata MultiLoad generates the USING phrase accordingly, with the user-assigned field name, for subsequent insert, update, and delete operations.

For details on data types and data conversions, see SQL Data Definition Language for details.

DROP…

Character positions to be dropped from the specified fieldname1

These must be of a character data type.

Teradata MultiLoad drops the specified characters and presents the field to Teradata Database as VARCHAR data type.

fieldexpr

Concatenation of two or more items, either:

  • fields
  • character constants
  • string constants
  • or a combination of these, as in:

    fieldname2||fieldname2||fieldname2...

    The field names within a layout must be unique. Nested concatenations are not supported.

    Each fieldname2 that is actually a field name must be specified in its own FIELD or FILLER command with a startpos parameter and a datadesc parameter that specifies the character data type.

    The fieldname1 parameter in other FIELD commands can be referenced in fieldexpr expressions.

    When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the character and the string constants specified in the expression from the script character encoding to the client character encoding before concatenating the constants with the specified fields.

    For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems.

    Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters required in the job script. See International Character Set Support for details

    fieldname1

    Name of an input record field

    The fieldname1 specification can be referenced by:

  • Subsequent INSERT, UPDATE, or DELETE statements
  • The NULLIF nullexpr expression of another FIELD command
  • The conditional expression of a LAYOUT command or the APPLY clause of an IMPORT command
  • A fieldname1 specification must obey the same construction rules as Teradata SQL column names.

    nullexpr

    Condition used for selectively inserting a null value into the affected column

    The condition is specified as a conditional expression involving any number of fields, each represented by its fieldname, and constants.

    Each fieldname appearing in the conditional expression must be defined by either:

  • The startpos and datadesc parameters of the FIELD command
  • A FILLER command
  • A TABLE command
  • The fieldname specifications in a nullexpr condition cannot be defined by a fieldexpr of a FIELD command.

    Note: The fieldname1 parameter in other FIELD commands can be referenced in nullexpr conditions. A FastLoad-type value specification (NULLIF=value) cannot be used as the nullexpr specification. The nullexpr specification must be a complete conditional expression that includes a logical operator.

    When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the string constants and the import data referenced in the expression to the same character set before evaluating the expression.

    For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8when using the UTF-16 client character set on network-attached systems.

    Network Example: If the client character set is UTF-16 and the script character set is UTF-8, and the following commands are given, MLOAD translates the data in the C1 field to the UTF-8 form and compares it with the UTF-8 form of 'DELETED' to obtain the evaluation result.

    .field C1 * varchar(20);
    .field C2 * varchar(40) nullif c1 = 'DELETED';

    Mainframe Example: If the client character set is UTF-8 and the script character set is Teradata EBCDIC, and the following commands are given, Teradata MultiLoad translates the data in the C1 field from the UTF-8 form to the Teradata EBCDIC form and compares it to the Teradata EBCDIC form of 'removed' to obtain the valuation result.

    .field C1 * char(20);
    .field C2 * char(40) nullif c1 = 'removed';

    Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters required in the job script. See International Character Set Support for details.

    startpos

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

    The startpos can be specified as:

  • An unsigned integer, which is a character position starting with 1
  • An asterisk, which means the next available character position beyond the preceding field
  • Note: The field positions of input records can be redefined by specifying the same positions in multiple FIELD commands.

    Usage Notes  

    The following table describes the things to consider when using the FIELD command.

     

    Table 40: Using the FIELD Command 

    Topic

    Usage Notes

    Changing the Data Type is Not Allowed

    The datadesc parameter cannot be used to change the data type from character to decimal or period when redefining an input record field in fieldname1. Doing so causes the Teradata MultiLoad job to abort with an error message.

    Intermixing Commands

    Intermixing one or more FIELD commands with TABLE and FILLER commands is allowed. All of these commands must follow a LAYOUT command.

    NULLIF Performance

    Use the following:

    ... 
    .FIELD fc * CHAR(5);
    .FIELD fi * INTEGER;
    ...

    .DML LABEL ins;
    INSERT INTO tbl1 VALUES
    (...,NULLIF(:fc,'empty'),
    NULLIF(:fi,0),...);

    And, in the more complex situation use:

    ... 
    .FIELD fs * CHAR(1) ; 
    .FIELD fc * CHAR(5) NULLIF (fs <> 'M') AND (fs <> 'F');
    .FIELD fi * INTEGER NULLIF fi < 0;
    ...

    .DML LABEL ins; INSERT INTO tbl2
    VALUES(...,:fs,:fc,:fi,...);

     

    or, use:

    ... 
    .FIELD fs * CHAR(1) ;
    .FIELD fc * CHAR(5);
    .FIELD fi * INTEGER;
    ...

    .DML LABEL ins;
    INSERT INTO tbl2 VALUES(...,:fs,
      CASE WHEN (:fs = 'M')
        OR (:fs = 'F')
        THEN :fc ELSE NULL END,
      CASE WHEN (:fi >= 0)
        THEN :fi ELSE NULL END,...);

    Parameter Evaluation Sequence

    If both NULLIF and DROP LEADING/TRAILING BLANKS/NULLS are specified in the same FIELD command, Teradata MultiLoad evaluates the DROP clause after the NULLIF clause.

    For example, if the input for field1 is 'x' in the following FIELD command, the NULLIF expression would evaluate to false because the leading blanks are not dropped before the NULLIF evaluation:

    .FIELD FIELD1 * CHAR (5) NULLIF
    FIELD1 = 'x'
    DROP LEADING BLANKS;

    Specifying Decimal Data Types

    The following is the input length and field description for the decimal data type specifications which can be made in the datadesc parameter:

    DECIMAL(x) and DECIMAL(x,y)

  • Length: 1, 2, 4, 8, or 16 bytes for network; packed decimal for mainframe
  • Description: 128-bit double precision, floating point
  • For more information on the DECIMAL data type, see SQL Data Types and Literals.

    Geospatial Types

    Teradata MultiLoad does not support geospatial data represented by LOBs.

    maximum 64000

    Specifying ARRAY Data Types

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

    The following is a sample Teradata table definition that includes a one-dimensional ARRAY data type for the COL003 column:

    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 Teradata MultiLoad FIELD definition for the sample SOURCE_TABLE:

    .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 sample data sets 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 is two samples of 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 is 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 be 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.

    Specifying Graphic Data Types

    Following is the input length and field description for the graphic data type specifications can be made in the datadesc parameter:

    GRAPHIC(n)

  • Length: n*2 bytes, if n is specified; otherwise 2 bytes, as n=1 is assumed.
  • Description: n double-byte characters (n is the length of the input stream in terms of double-byte characters).
  • VARGRAPHIC(n)

  • Length: m + 2 bytes where m/2 <= 1600.
  • Description: 2-byte integer followed by m/2 double-byte characters.
  • LONG VARGRAPHIC

  • Length: m + 2 bytes where m/2 <= 16000.
  • Description: 2-byte integer followed by m/2 double-byte characters.
  • Note: LONG VARGRAPHIC also implies VARGRAPHIC (16000). Range is 0 to 16000 in a 32,000-byte field. For both VARGRAPHIC and LONG VARGRAPHIC, m, a value occupying the first 2 bytes of the input data, is the length of the input in bytes, not characters. Each multibyte character set character is 2 bytes.

    Specifying Period Data Types

    A period is an anchored duration. It represents a set of contiguous time granules within that duration. A period is implemented using a Period data type. Each period consists of two elements:

  • BEGIN (the beginning element)
  • END (the ending element)
  • The element type is one of the following DateTime data types. They are DATE, TIME, and TIMESTAMP.

    The five PERIOD data types include:

  • PERIOD(DATE)
  • PERIOD(TIME[(n)])
  • PERIOD(TIME[(n)] WITH TIME ZONE)
  • PERIOD(TIMESTAMP[(n)])
  • PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)
  • For more information on the PERIOD data type, see SQL Data Types and Literals.

    Specifying Relevant Fields

    Because all fields specified by FIELD commands are sent to Teradata Database, only specify those fields that are relevant to the Teradata MultiLoad task.

    startpos Specification

    The specified starting position:

  • Reflects removal of any columns per the CONTINUEIF clause of a LAYOUT command
  • Ignores any indicator bytes per the INDICATORS specification of a LAYOUT command
  • Is relative to character position 1
  • Therefore, the first data position of the input record is position 1.

    For more information about the CONTINUEIF and INDICATORS specifications, see the “LAYOUT” command description.

    Using ANSI/SQL DateTime Data Types

    When the DATEFORM command is used to specify ANSIDATE as the DATE data type, Teradata MultiLoad internally converts each DATE field to a CHAR(10) field. All ANSI/SQL DateTime TIME, TIMESTAMP, and INTERVAL data types must be converted to fixed-length CHAR data types to specify column/field names in a Teradata MultiLoad FIELD command.

    Table 41 provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

    Table 41 describes Date/Time information to consider when using the FIELD command.

    For the latest conversion specifications and format examples for each ANSI/SQL DateTime specification, see the INTERVAL type description in SQL Data Types and Literals.

     

    Table 41: ANSI/SQL DateTime Specifications 

    Data Type

    Variable Definition

    Conversion Example

    INTERVAL YEAR

    INTERVAL YEAR (n)

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n+1)

    Format (n = 2): yy
    Example: 98

    Format: (n = 4): yyyy
    Example: 1998

    INTERVAL YEAR TO MONTH

    INTERVAL YEAR (n) TO MONTH

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n + 4)

    Format (n = 2): yy-mm
    Example: 98-12

    Format: (n = 4): yyyy-mm
    Example: 1998-12

    INTERVAL MONTH

    INTERVAL MONTH (n)

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n+1)

    Format (n = 2): mm
    Example: 12

    Format: (n = 4): mmmm
    Example: 0012

    INTERVAL DAY

    INTERVAL DAY (n)

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n+1)

    Format (n = 2): dd
    Example: 31

    Format: (n = 4): dddd
    Example: 0031

    INTERVAL DAY TO HOUR

    INTERVAL DAY (n) TO HOUR

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n + 4)

    Format (n = 2): dd hh
    Example: 31 12

    Format: (n = 4): dddd hh
    Example: 0031 12

    INTERVAL DAY TO MINUTE

    INTERVAL DAY (n) TO MINUTE

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n + 7)

    Format (n = 2): dd hh:mm
    Example: 31 12:59

    Format: (n = 4): dddd hh:mm
    Example: 0031 12:59

    INTERVAL DAY TO SECOND

    INTERVAL DAY (n) TO SECOND

    INTERVAL DAY TO SECOND (m)

    INTERVAL DAY (n) TO SECOND (m)

    n = number of digits

    Valid values: 1-4

    Default = 2

    m = number of digits after decimal point

    Valid values: 0-6

    Default = 6

    CHAR(n + 10+ m + (1 if m > 0, otherwise 0))

    Format (n = 2, m = 0):
    hh:mm:ss
    Example:
    12:59:59

    Format: (n = 4, m = 4): hhhh:mm:ss.ssss
    Example: 0012:59:59.1234

    INTERVAL HOUR

    INTERVAL HOUR (n)

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n+1)

    Format: (n = 2): hh
    Example: 12|

    Format: (n = 4): hhhh
    Example: 0012

    INTERVAL HOUR TO MINUTE

    INTERVAL HOUR (n) TO MINUTE

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n + 4)

    Format: (n = 2): hh:mm
    Example: 12:59

    Format: (n = 4): hhhh:mm
    Example: 0012:59

    INTERVAL HOUR TO SECOND

    INTERVAL HOUR (n TO SECOND

    INTERVAL HOUR TO SECOND (m)

    INTERVAL HOUR (n) TO SECOND (m)

    n = number of digits

    Valid values: 1-4

    Default = 2

    m = number of digits after the decimal point

    Valid values: 0-6

    Default = 6

    CHAR(n + 7+ m + (1 if m > 0, otherwise 0))

    Format: (n = 2, m = 0):
    hh:mm:ss
    Example: 12:59:59

    Format: (n = 4, m = 4): hhhh:mm:ss.ssss
    Example: 0012:59:59.1234

    INTERVAL MINUTE

    INTERVAL MINUTE (n)

    n = number of digits

    Valid values: 1-4

    Default = 2

    CHAR(n+1)

    Format (n = 2): mm
    Example: 59

    Format: (n = 4): mmmm
    Example: 0059

    INTERVAL MINUTE TO SECOND

    INTERVAL MINUTE (n) TO SECOND

    INTERVAL MINUTE TO SECOND (m)

    INTERVAL MINUTE (n) TO SECOND (m)

    n = number of digits

    Valid values: 1-4

    Default = 2

    m = number of digits after decimal point

    Valid values: 0-6

    Default = 6

    CHAR(n + 4 + m + (1 if m > 0, otherwise 0))

    Format (n = 2, m = 0): mm:ss
    Example: 59:59

    Format: (n = 4, m = 4): mmmm:ss.ssss
    Example: 0059:59.1234

    INTERVAL SECOND

    INTERVAL SECOND (n)

    INTERVAL SECOND (n,m)

    n = number of digits

    Valid values: 1-4

    Default = 2

    m = number of digits after decimal point

    Valid values: 0-6

    Default = 6

    CHAR(n + 1 + m + (1 if m > 0, otherwise 0))

    Format (n = 2, m = 0): ss
    Example: 59

    Format: (n = 4, m = 4): ssss.ssss
    Example: 0059.1234

     

    TIME

    TIME (n)

    n = number of digits after decimal point

    Valid values: 0–6

    Default = 6

    CHAR(8 + n + (1 if n > 0, otherwise 0))

    Format (n = 0): hh:mm:ss
    Example: 11:37:58

    Format: (n = 4): hh:mm:ss.ssss
    Example: 11:37:58.1234

    TIMESTAMP

    TIMESTAMP (n)

    n = number of digits after decimal point

    Valid values: 0–6

    Default = 6

    CHAR(19 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    yyyy-mm-dd hh:mm:ss
    Example:
    1998-09-04 11:37:58

    Format (n = 4):
    yyyy-mm-dd hh:mm:ss.ssss
    Example:
    1998-09-04 11:37:58.1234

    TIME WITH TIME ZONE

    TIME (n) WITH TIME ZONE

    n = number of digits after decimal point

    Valid values: 0–6

    Default = 6

    CHAR(14 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    hh:mm:ss{±}hh:mm
    Example:
    11:37:58-08:00

    Format (n = 4):
    hh:mm:ss.ssss {±} hh:mm
    Example:
    11:37:58.1234-08:00

    TIMESTAMP WITH TIME ZONE

    TIMESTAMP (n) WITH TIME ZONE

    n = number of digits after decimal point

    Valid values: 0-6

    Default = 6

    CHAR(25 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    yyyy-mm-dd hh:mm:ss{±}hh:mm
    Example:
    1998-09-24 11:37:58+07:00

    Format (n = 4):
    yyyy-mm-dd hh:mm:ss.ssss{±} hh:mm
    Example:
    1998-09-24 11:37:58.1234+07:00

    Example  

    Concatenating Special Characters

    Use the following command structure to concatenate a character string such as 'A'||'%'||'@':

    .LAYOUT LAY1A; 
    .FIELD FIELD1 * INTEGER; 
    .FIELD FIELD2 * INTEGER; 
    .FIELD FIELD3 * CHAR (1); 
    .FIELD FIELD4 * CHAR (2); 
    .FIELD FIELD5 FIELD4||'%'||'@';

    Example  

    Multiple References to the Same Field

    Consider a set of input records that contains a 25-character address field in positions 15 through 39, with the street number in the first seven positions and the street name in the last 18 positions.

    The following example shows how to refer both to the address field as a whole and separately to the street name:

    .FIELD ADDRESS 15 CHAR(25); 
    .FIELD STREET  22 CHAR(18);

    Example  

    Invalid Concatenation

    A field that has been defined by a concatenation of fields cannot be concatenated.

    The following example shows the error message generated for this condition:

    0009 .LAYOUT LAY3S; 
    0010 .FIELD FIELD1 *  INTEGER; 
    0011 .FIELD FIELD2 *  INTEGER; 
    0012 .FIELD FIELD3 *  CHAR (1); 
    0013 .FIELD FIELD4 *  CHAR (2); 
    0014 .FIELD FIELD5  FIELD3||FIELD4; 
    0015 .FIELD FIELD6  FIELD3||FIELD4||FIELD5; 
    0016 .FIELD FIELD7  FIELD3||FIELD4||FIELD5||FIELD6;
    0017 .DML LABEL LABELA; 
    0018 INSERT TBL3S
    (FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7) VALUES
    (:FIELD1,:FIELD2,:FIELD3,:FIELD4,:FIELD5,:FIELD6,:FIELD7);
    0019 .IMPORT INFILE INPUT FREE 
        LAYOUT LAY3S 
        APPLY LABELA; 
    0020 .END MLOAD; 
         15:03:06 ‑ MON OCT 22, 1990 
         UTY0215 It is invalid to concatenate a field that 
         has been defined by a concatenation of fields.
         15:03:06 ‑ MON OCT 22, 1990 
         UTY1414 The previous error occurred on statement 
         number '15'. 

    Example  

    Using the GRAPHIC Data Types

    The following example shows how to use the GRAPHIC data types in support of kanji or multibyte character data. The FIELD description of the 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);

    Example  

    Converting Data

    The following valid example converts numeric data to different types of numeric data:

    Assuming you have a target table:

    CREATE TABLE MLTBL,FALLBACK
    F1 INTEGER,
    ABYTEINT BYTEINT,
    ASMALLINT SMALLINT,
    AINTEGER INTEGER,
    ABIGINT BIGINT,
    AFLOAT FLOAT)
    PRIMARY INDEX (F1);

    You can specify FIELD command for the input records:

    FIELD FF1 * INTEGER;
    FIELD FF2 * decimal(2,1);
    FIELD FF3 * decimal(4,1);
    FIELD FF4 * decimal(9,2);
    FIELD FF5 * decimal(18,2);
    FIELD FF6 * decimal(18,2);

    The decimal data type will be converted to BYTEINT, SMALLINT, etc.

    Purpose  

    The FILLER command describes a named or unnamed field as filler, which is not sent to Teradata Database.

    Syntax  

    where:

     

    Syntax Element

    Description

    datadesc

    type and length of data in the field

    fieldname

    optional name of an input record field

    The fieldname specification can be referenced by:

  • The NULLIF nullexpr expression of a FIELD command
  • The condition expression of the APPLY clause of an IMPORT command
  • A fieldname specification must obey the same construction rules as Teradata SQL column names.

    startpos

    starting position of the specified field in the data records of an external data source

    The startpos can be defined as:

  • An unsigned integer, which is a character position starting with 1
  • An asterisk, which means the next available character position beyond the preceding field
  • Usage Notes  

    Table 42 describes the things to consider when using the FILLER command.

     

    Table 42: FILLER Command Usage Notes 

    Topic

    Usage Notes

    Intermixing Commands

    One or more FILLER commands can be intermixed with TABLE and FIELD commands.

    All of these commands must follow a LAYOUT command.

    Specifying Graphic Data Types

    The following lists the input length and field description for the graphic data type specifications that can be made in the datadesc parameter:

    GRAPHIC(n)

  • Length: n*2 bytes, if n is specified; otherwise 2 bytes, as n=1 is assumed.
  • Description: n double-byte characters (1n is the length of the input stream in terms of double-byte characters).
  • LONG VARGRAPHIC

  • Length: m + 2 bytes where m/2 <= 16000.
  • Description: 2-byte integer followed by m/2 double-byte characters.
  • Note: LONG VARGRAPHIC also implies VARGRAPHIC (16000). Range is 0 to 16000 in a 32,000-byte field.

    startpos Specification

    The specified starting position:

  • Reflects removal of any columns per the CONTINUEIF clause of a LAYOUT command
  • Ignores any indicator bytes per the INDICATORS specification of a LAYOUT command
  • Is relative to character position 1
  • Therefore, the first data position of the input record is position 1.

    For more information about the CONTINUEIF and INDICATORS specifications, see the LAYOUT command description.

    Example  

    Concatenating a Field with a Filler

    Use the following command structure to concatenate a field with a filler:

    .LAYOUT LAY1A; 
    .FIELD  F1 * CHAR(2); 
    .FILLER F2 * CHAR(1); 
    .FIELD  F3 F1||F2

    Example  

    Using the GRAPHIC Data Types

    The following example shows how to use 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);

    Purpose  

    The IF, ELSE, and ENDIF commands provide conditional control of execution processes.

    Syntax  

    Usage Notes  

    Table 43 describes the things to consider when using the IF, ELSE, and ENDIF commands.

     

    Table 43: IF, ELSE, and ENDIF Usage Notes 

    Topic

    Usage Notes

    ELSE Clause

    Use the optional ELSE clause to execute commands when the condition is evaluated as false.

    Nesting IF Commands

    Teradata MultiLoad supports up to 100 nested IF commands.

    Numeric Results of the Conditional Expression

    The conditional expression is an expression that can be evaluated as either true or false.

    When evaluation of the expression returns a numeric result:

  • Zero is interpreted as false
  • Nonzero results are interpreted as true
  • Variable Substitutions

    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.

    Variables in the IF Expression

    The conditional expression can be either user-defined variables or predefined system variables.

    Example  

    Teradata MultiLoad is case sensitive when comparing &SYS system variables. In this example, the RUN FILE command does not execute because the substituted values returned are all capitals:

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

    Always consider this factor when creating a script to force the execution of a predetermined sequence of events. If 'FRI' is substituted in line 0003, the compare would work and the RUN FILE command would execute.

    Example  

    In the following example, 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:

    .SET CREATERC TO &SYSRC;
    .IF &CREATERC = 3803 /* Table &TABLE exists */ THEN;
    .RUN FILE RUN01;
    .ELSE;
    .IF &CREATERC <> 0 THEN;
    .LOGOFF &CREATRC;
    .ENDIF;
    .ENDIF;

    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.

    Purpose  

    The IMPORT command specifies a source for data input.

    Syntax  

    The IMPORT command syntax depends on whether Teradata MultiLoad is running on a mainframe-attached or network-attached client system. Several of the syntax elements are common to both configurations, while others are specific.

    For Mainframe-Attached Client Systems

     

    For Network-Attached Client Systems

    where:

     

    Syntax Element

    Description

    APPLY label

    Error treatment options specified by a previous DML LABEL command for subsequent INSERT, UPDATE, or DELETE statements

    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
  • Teradata WebSphere® MQ Access Module (client version)
  • Teradata WebSphere®MQ Access Module (server version)
  • For more information, see Teradata Tools and Utilities Access Module Reference (B035‑2425).

  • Teradata Access Module for JMS (libjmsam.so on AIX, Oracle Solaris SPARC, Solaris Opteron, Linux and z/Linux platforms, libjmsam.sl on HP-UX pa RISC and HP-UX 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 network-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 network-attached 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.

    'c'

    Optional specification of the delimiter character that separates fields in the variable-length text records of the input data source.

    If a 'c' specification is not used, the default is the pipe character (|).

    When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the effective delimiter from the script character encoding to the client character encoding before separating fields with it.

    For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems.

    Network Example: If the client character set is UTF-16, the script character set is UTF-8 and the following command is given:

    …FORMAT VARTEXT '-'…

    Teradata MultiLoad translates '-' from UTF-8 to UTF-16, and then separates the fields in the record according to the UTF-16 form of '-'.

    Mainframe Example: If the client character set is UTF-8, the script character set is Teradata EBCDIC, and the following command is given:

    …FORMAT VARTEXT '6A'xc…

    Teradata MultiLoad interprets x’6A’ according to Teradata EBCDIC, 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.

    Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters which might be required in the job script. See International Character Set Support for details.

    For example, the code point of '|' is x'4F' in most IBM EBCDIC code pages. If '|' is specified as the delimiter in the script or leave the delimiter to default in a system environment using that type of IBM EBCDIC code page and UTF-8 data uses x'7C' (which is '|' 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, not U+007C.
  • 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
  • DISPLAY ERRORS

    Optional keyword specification that writes input data records that produce errors to the standard error file.

    DISPLAY ERRORS 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.

    FOR n

    Number of records, as an integer, starting at recordm, to be processed.

    If a FOR n or a THRU k specification is not used, Teradata MultiLoad continues processing through the last record obtained from the data source.

    Note: When “FOR 0” is used, Teradata MultiLoad defaults FROM as 2 and THRU as 1. A warning is issued. This makes Teradata Multi load only the 2nd record of the data file to the target table.

    FORMAT…

    Format of the input record, where:

  • FASTLOAD specifies that each input record is a 2-byte integer, n, followed by n bytes of data, followed by an end-of-record marker (either X'0A' or X'0D').
  • BINARY specifies that each input record is a 2-byte integer, n, followed by n bytes of data.
  • TEXT specifies that each record consists of an arbitrary number of characters in the client session character set, followed by an end-of-record marker, which is:
  • On UNIX platforms, the newline character (identified in Unicode as LINE FEED U+000A)
  • On Windows platforms, the two-character sequence carriage return followed by line feed (identified in Unicode as CARRIAGE RETURN U+000D and LINE FEED U+000A, respectively)
  • For client session character sets other than UTF16, the end-of-record marker byte sequence is:

  • On UNIX platforms, X'0A'
  • On Windows platforms, X'0D0A'
  • For the UTF16 client session character set (in which each character is encoded in two bytes), the end-of-record marker byte sequence is:

  • On big endian UNIX platforms, X'000A'
  • On little endian UNIX platforms, X'0A00'
  • On Windows platforms, X'0D000A00'
  • Note: TEXT format should only be specified for character data like CHAR or ANSIDATE. Do not specify TEXT format for binary data, such as, INTEGER, BYTEINT, PERIOD, and other binary data. Depending on the actual byte values of the binary data, unexpected results may occur.

    Note: INDICATORS mode is not recommended when using TEXT record format. Please use UNFORMATTED record format instead.

  • UNFORMAT specifies that each input record is defined 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.

  • VARTEXT specifies that each input record is in variable-length text record format, with each field separated by delimiter characters, which:
  • cannot be characters that appear in the data
  • cannot be control characters, other than a TAB
  • If a FORMAT option is not specified, the default format is FASTLOAD.

    FREE

    Deallocation of the tape input device specified by ddname when the import operation completes on mainframe-attached client systems.

    When de-allocated, any attempt to open the input device, either in the same Teradata MultiLoad task or in another task within the same script, produces an undefined ddname error.

    The default is to not deallocate the device.

    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 specified, Teradata MultiLoad begins processing with the first record received from the data source.

    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 MultiLoad operation completes.

    INFILE ddname

    External data source that contains the input records on mainframe-attached client z/OS client systems. In z/OS, this is a DDNAME.

    If ddname is specified, Teradata MultiLoad reads data records from the specified source. If modulename is also specified, Teradata MultiLoad 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.

    INFILE filename

    Fully qualified UNIX or Windows path name for an input file on network-attached client systems

    If the path name has embedded white space characters, enclose the entire path name in single or double quotes.

    If INFILE filename is specified, Teradata MultiLoad reads data from the specified source. If INMOD modulename is also specified, Teradata MultiLoad passes the data it reads to the specified module.

    init-string

    Optional initialization string for the access module

    The initialization string can contain double quotes, but not single quotes.

    INMOD modulename

  • User exit routine that optionally reads, and always preprocesses, each record before passing the record to Teradata MultiLoad for processing on mainframe-attached client systems
  • Fully qualified UNIX or Windows pathname of the INMOD executable code on network-attached client systems
  • The modulename specification 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
  • The modulename specification must obey the applicable rules of the external system.

    Note: On some versions of UNIX operating systems, a ./ prefix character may have to be added to the modulename specification if the module is in the current directory.

    Note: On Windows platforms, if the INMOD module output messages are to stdout, the character set that INMOD uses is independent of the character set that MultiLoad uses, the display on stdout can be of mixed character sets. For example, IMMOD can output messages in ASCII and Multi load can output messages in UTF-16.

    LAYOUT layoutname

    Layout of the input record, as specified by a previous LAYOUT command.

    NOSTOP

    Optional keyword specification that inhibits the Teradata MultiLoad termination in response to an error condition associated with a variable-length text record.

    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, consisting of one or more characters. 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.

    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 MultiLoad continues processing through the last record obtained from the data source.

    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).

    USING (parms)

    Character string containing whatever parameters are to be passed to the corresponding user exit routine:

  • The parms string can include one or more character strings, each delimited on either end by an apostrophe or quotation mark.
  • The maximum size of the parms string is 1 KB.
  • 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, Teradata MultiLoad replaces the following 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 parms string, as a whole, must be enclosed in parentheses. On mainframe-attached client systems, the parentheses are included in the string passed to the user exit routine.
  • When the user exit routine is an old FastLoad INMOD, the parms string must be FDLINMOD.
  • WHERE condition

    Condition that determines whether the indicated label options are applied to the records and sent to Teradata Database per subsequent INSERT, UPDATE, or DELETE statements 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 FIELD commands
  • If a WHERE condition specification is not used, the default condition is true/yes.

    When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the string constants specified and the import data referenced in the condition to the same character set before evaluating the condition.

    For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems.

    Network Example: If the client character set is UTF-16, the script character set is UTF-8, and the following command is given:

    …APPLY lable1 WHERE C1 = 'INSERT';

    Teradata MultiLoad 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.

    Mainframe Example: If the client character set is UTF-8, the script character set is Teradata EBCDIC, and the following command is given:

    …APPLY lable2 WHERE C2 = 'DELETE';

    Teradata MultiLoad translates the data in the C2 field from the UTF-8 form to the Teradata EBCDIC form and compares it with the Teradata EBCDIC form of 'DELETE' to obtain the evaluation result.

    Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters which might be required in the job script. See International Character Set Support for details.

    Usage Notes  

    Table 44 describes the things to consider when using the IMPORT command.

     

    Table 44: IMPORT Usage Notes 

    Topic

    Usage Notes

    Data Type Specifications

    When using the VARTEXT specification, VARCHAR, VARBYTE and LONG VARCHAR are the only valid data type specifications which can be used in the Teradata MultiLoad layout FIELD and FILLER commands.

    Error Record Handling

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

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

    FREE/HOLD Option When Running Under z/OS

    The disposition of the output device specified in the JCL must be KEEP, not PASS, for the FREE/HOLD option to work in Teradata MultiLoad tasks running under z/OS.

    Import Task Command Restrictions

    The combined number of Teradata SQL statements under the DML commands cannot exceed 100 within a single Teradata MultiLoad import task.

    Sending an excessive number of statements to Teradata Database produces an error message indicating that there are too many DML steps for one Teradata MultiLoad import task.

    For an import task, a candidate statement or group of statements is applied if no condition is specified, or if the specified condition is true.

    Note, however, that for an import task, 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 APPLY clauses. (The referenced DML commands and their following DML statements must appear between the BEGIN MLOAD command that defines the import task and the referencing IMPORT commands.)

    Input Record Requirements

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

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

    Note that a delimiter character in the last field of a record is optional.

    Null Fields

    Two consecutive delimiter characters direct Teradata MultiLoad to null the field corresponding to the one right after the first delimiter character.

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

    Multiple APPLY Clauses

    In an import task, multiple APPLY clauses can be applied to the same data record in either of two ways. This features allows the same data record to be applied to different tables under the same or different conditions.

    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 refer to the label specification of a different DML statement or group of statements. Each label specification is applied to the same data record under the condition specified in the respective clause.

    Primary Indexes and Partitioning Column Sets

    IMPORT tasks require that all values of the primary index column be set and all values of the partitioning column be set for deletes and updates. IMPORT tasks do not support updates of the partitioning column set. IMPORT tasks do not support primary index updates.

    Record Length Validation

    By default, Teradata MultiLoad does not compare the actual record length of the import data with the record length indicated by the layout specifications for the job.

    If they are not the same, the default behavior of Teradata MultiLoad depends on whether the actual import data record length is less than or greater than the record length indicated by the layout specifications:

  • If the actual import data record length is less than the length indicated by the layout specifications, then Teradata MultiLoad terminates with an error indication.
  • If the actual import data record length is greater than the length indicated by the layout specifications, then Teradata MultiLoad ignores the extra fields and continues with the import task.
  • To change the default behavior and enforce a record-length validation check, use a MATCHLEN=on entry in the Teradata MultiLoad configuration file before invoking Teradata MultiLoad. In this case, Teradata MultiLoad terminates with an error message whenever the actual and specified record lengths are different.

    For information about using the Teradata MultiLoad configuration file, see “Teradata MultiLoad Configuration File” on page 46.

    VARTEXT Records

    When VARTEXT is specified, Teradata MultiLoad assumes that the input data is variable-length text fields separated by up to 10 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.

    Purpose  

    INSERT is a Teradata SQL statement that adds new rows to a table or view.

    Syntax  

    where:

     

    Syntax Element

    Description

    .*

    Default VALUES clause for the insert operation

    When the .* characters is used as a suffix with the tname specification, Teradata MultiLoad replaces these characters with a default VALUES clause before executing the command.

    Note: When using the .* default, tname must specify a table name and not a view name. The default option is most useful when used with the TABLE command, which builds the layout corresponding to the same table.

    cname

    Column of the specified table that is to receive the value from a field of matching input records

    If cname specifications are not entered, Teradata MultiLoad uses the column identifiers as they were defined by the CREATE TABLE statement.

    The value is identified by the corresponding entry in the fieldname list.

    fieldname

    Field of an input record, whose value is given to a column of the tname table that is identified by the corresponding cname specification of this command

    Alternatively, each fieldname clause may instead be an expression that includes one or more actual fieldname terms.

    tname

    Table or view that is to receive rows from Teradata MultiLoad input data records

    The tname specification must have been previously identified as tname1 in the BEGIN MLOAD command.

    VALUES

    The VALUES clause is of the form:

    VALUES (:column1, :column2,...)

    where: :column1, :column2,… are the names of the columns from tname in the order in which they were defined by the CREATE TABLE statement.

    Usage Notes  

    Table 45 describes the things to consider when using the INSERT statement.

     

    Table 45: INSERT Usage Notes 

    Topic

    Usage Notes

    ANSI/SQL DateTime Specifications

    The ANSI/SQL DATE, TIME, TIMESTAMP, and INTERVAL DateTime data types in Teradata SQL CREATE TABLE statements can be as column/field modifiers in INSERT statements. You must convert them to fixed-length CHAR data types when specifying the column/field names in the FIELD command.

    Object Restriction

    Only one tname object for an INSERT statement can be specified, and it must have been previously identified as a target object in a BEGIN MLOAD command.

    If the tname object is a view, it must not specify a join. Teradata MultiLoad operates only on single-table commands, so your INSERT statements must not contain joins.

    Required Privilege

    To use the INSERT statement, the INSERT privilege must be on the tname table or view.

    Specifying the Applicable DML Statements

    One way of specifying the applicable DML statements is to relate each field name to the name of the column to which the field’s data is applied.

    Another way tells Teradata MultiLoad to apply the first nonfiller field of a record that is sent to Teradata Database to the first defined column of the affected table, the second nonfiller field to the second column, and so on.

    In either case, a value for every column must be specified, either explicitly or by default.

    Using Unicode Data

    Do not use the tname.* version of an INSERT statement when using Unicode data from:

  • A KATAKANAEBCDIC session
  • A session with a character set name ending with _0I
  • Any session with a character set that does not support multibyte characters (for example, ASCII or EBCDIC)
  • These functions return the field names from the referenced tables and return byte/character counts that Teradata MultiLoad uses internally to construct the USING clause for the subsequent load operation.

    The internally generated USING clauses do not properly reflect the structure of the input data stream because of the byte-count and character-count conversions that occur when importing and exporting CHAR and VARCHAR data between the client system and Teradata Database.

    Example  

    The following examples show three ways to specify the relationship between the fields of input data records and the columns of the target table, using targetable as the target object name.

  • Using the tname .* Specification
  • .LAYOUT lname; 
    .TABLE  targetable; 
    .DML LABEL label; 
    INSERT INTO targetable .*; 
  • Using the cname Specification
  • .LAYOUT lname; 
    .FIELD first 1 somedatatype; 
    .FIELD f2nd * anydatatype;
    .
    .
    .
    .FIELD flast * datatype; 
    .DML LABEL label; 
    INSERT INTO targetable (col1, col2, ... colast) 
    VALUES (:f2nd, :first, ... :flast); 
  • Using the VALUES fieldname Specification
  • .LAYOUT lname; 
    .FIELD first 1 somedatatype; 
    .FIELD f2nd * anydatatype;
    .
    .
    .
    .FIELD flast * datatype; 
    .DML LABEL label; 
    INSERT INTO targetable VALUES (:first, :f2nd, ... :flast);

    Example  

    The following example supposes an input data source that contains a series of 10- to 40-byte records. Each record contains the primary key value (EmpNum) of a row that is to be inserted successively into the Employee table whose columns are EmpNo, Name, and Salary. The example assumes that the current default database is Personnel, the database containing the Employee table.

    .LAYOUT Layoutname; 
    .FIELD EmpNum 1 INTEGER; 
    .FIELD Name * (VARCHAR (30)); 
    .FIELD Sal * (DECIMAL (7,2)); 
    .DML LABEL DMLlabelname; 
    INSERT Employee (EmpNo, Name, Salary) VALUES (:EmpNum, :Name, :Sal); 

    Purpose  

    The LAYOUT command, used with an immediately following sequence of FIELD, FILLER, and TABLE commands, specifies the layout of the input data records.

    Syntax  

    where:

     

    Syntax Element

    Description

    CONTINUEIF condition

    Conditional phrase in which condition is of the form:

    position = value

    where

  • position is an unsigned integer (never an asterisk) that specifies the starting character position of the field of every input record that contains the continuation indicator. The position is relative to the first character position of the input record or input record fragment, which is always position 1.
  • value is the continuation indicator specified as a character constant or a string constant. Teradata MultiLoad uses the length of the constant as the length of the continuation indicator field.
  • The condition specified as position = value is case sensitive. Always specify the correct character case for this parameter.

    If the condition phrase is true, then Teradata MultiLoad forms a single record to be sent to Teradata Database by concatenating the next input record at the end of the current record. (The current record is the one most recently obtained from the external data source.)

    If the condition is false, then Teradata MultiLoad sends the current input record to Teradata Database either by itself or as the last of a sequence of concatenated records. Regardless of whether the condition evaluates to true or false, Teradata MultiLoad removes the tested string (the continuation indicator field) from each record. All CONTINUEIF processing necessary to construct a complete record is done before any other processing of the record.

    CONTINUEIF condition
    cont’d.

    When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the specified value, which is either a character constant or a string constant, from the script character encoding to the client character encoding before evaluating the condition. Teradata MultiLoad uses the length of the constant in the client character encoding as the length of the continuation indicator field.

    For example, the job script must be in Teradata EBCDIC when using the UTF-8client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems.

    Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters which might be required in the job script. See International Character Set Support for details.

    INDICATORS

    Condition that the data is in the indicator mode

    When the INDICATORS specification is used, Teradata MultiLoad sends all of the FIELD commands, including redefines, to Teradata Database.

    Notice:

    Inappropriate INDICATORS specifications can corrupt the target table on Teradata Database. If INDICATORS is specified in the LAYOUT command and the data file does not contain indicator bytes in each record, the target table is loaded with spurious data.

    Conversely, if INDICATORS is not specified and the data file contains indicator bytes in each record, the target table also is corrupted.

    Always make sure that INDICATORS specifications match the mode of the data being sent to Teradata Database.

    Note: INDICATORS processing is done only after any CONTINUEIF processing is completed for a record.

    Note: If IS NULL or IS NOT NULL is specified in the APPLY clause, INDICATORS specification is required for all the data formats except VARTEXT format.

    layoutname

    Name assigned to the layout for reference by one or more subsequent IMPORT commands

    A layoutname must obey the same construction rules as Teradata SQL column names.

    Usage Notes  

    Table 46 describes the things to consider when using the LAYOUT command.

     

    Table 46: LAYOUT Usage Notes 

    Topic

    Usage Notes

    CONTINUEIF and INDICATORS Processing

    When both CONTINUEIF and INDICATORS are specified:

  • Teradata MultiLoad processes all of the CONTINUEIF specifications before performing the INDICATORS processing
  • If the CONTINUEIF columns start in position 1, then any indicator bytes will follow the CONTINUEIF columns
  • Required Following Commands

    A LAYOUT command must be immediately followed by a combination of FIELD, FILLER, or TABLE commands. This sequence of commands, referenced by the layoutname, may describe one or more record formats contained in one or more client data sources. (See the redefinition options for FIELD, FILLER, and TABLE commands).

    The LAYOUT command sequence is terminated by the first subsequent command that is not a FIELD, FILLER, or TABLE command.

    Using the Same LAYOUT Command in More Than One Teradata MultiLoad Task

    Reference the same layoutname specification in more than one Teradata MultiLoad task, provided that:

  • Each task is delimited by BEGIN MLOAD and END MLOAD commands in a single job step
  • The LAYOUT command appears before any IMPORT commands that reference it
  • Purpose  

    Supplies parameters to the LOGMECH command beyond those needed by the logon mechanism, such as user ID and password, to successfully authenticate the user. The LOGDATA command is optional. Whether or not parameters are supplied and the values and types of parameters depend on the selected logon method.

    LOGDATA is only available on network-based platforms.

    Note: To prevent the password from appearing in the script, use Teradata Wallet. Refer to Security Administration and the appropriate installation guide for more information.

    Syntax

    where:

     

    Syntax Element

    Description

    logdata_string
    ‘logdata_string’

    Parameters for the logon mechanism specified using “LOGMECH” on page 163.

    For information about the logon parameters for supported mechanisms, see Security Administration.

    The string is limited to 64 KB and must be in the session character set. To specify a string containing white space or other special characters, enclose the data string in single quotes.

    Usage Notes  

    For more information about logon security, see Security Administration.

    Example  

    If used, the LOGDATA and LOGMECH commands must precede the LOGON command. The commands themselves may occur in any order.

    The following example demonstrates using the LOGDATA, LOGMECH, and LOGON commands in combination to specify the Kerberos logon authentication method and associated parameters:

    .logmech KRB5;
    .logdata joe@domain1@@mypassword;
    .logon cs4400s3;

    Purpose  

    Identifies the appropriate logon mechanism by name. If the mechanism specified requires parameters other than user ID and password for authentication, the LOGDATA command provides these parameters. The LOGMECH command is optional and available only on network-attached systems.

    Syntax  

    where:

     

    Syntax Element

    Description

    logmech_name

    Logon mechanism

    For a discussion of supported mechanisms, see Security Administration.

    The name is limited to 8 bytes; it is not case-sensitive.

    Usage Notes  

    Every session to be connected requires a mechanism name. If none is supplied, a default mechanism can be used instead, as defined on either the server or client system in an XML‑based configuration file.

    For more information about logon security, see Security Administration.

    Example  

    If used, the LOGDATA and LOGMECH commands must precede the LOGON command. The commands themselves may occur in any order.

    The following example demonstrates using the LOGDATA, LOGMECH, and LOGON commands in combination to specify the Windows logon authentication method and associated parameters:

    .logmech NTLM;
    .logdata joe@domain1@@mypassword;
    .logon cs4400s3;

    Purpose  

    The LOGOFF command disconnects all active sessions and terminates Teradata MultiLoad on the client system.

    Syntax  

    where:

     

    Syntax Element

    Description

    retcode

    Completion code returned to the client operating system

    If a value for retcode is not specified, Teradata MultiLoad returns the appropriate terminating return code.

    Usage Notes  

    Table 47 describes the things to consider when using the LOGOFF command.

     

    Table 47: LOGOFF Usage Notes 

    Topic

    Usage Notes

    Optional Completion Code

    Specify the optional completion code value, retcode, as a conditional or an arithmetic expression, evaluated to a single integer.

    The LOGOFF command is processed when the highest return code reached prior to the LOGOFF command is no more than 4 (warning). Any higher return code would have already terminated the Teradata MultiLoad job.

    If the LOGOFF command is processed, Teradata MultiLoad returns the higher of:

  • The retcode value specified as a LOGOFF command option
  • The highest return code reached prior to the LOGOFF command
  • For example, if the retcode value was specified as 2, but a warning had occurred earlier in the run, Teradata MultiLoad would return 4, not 2.

    If a serious error terminates the program before the LOGOFF command is processed, the return code output is the value generated by the error condition rather than the retcode value specified as a LOGOFF command option.

    Termination Return Codes

    When a Teradata MultiLoad job terminates and an optional retcode value has not been specified, the utility returns a code indicating the way the job completed:

  • Code 0—Normal completion. The job completed successfully and according to the specified plan.
  • Code 4—Warning. A warning condition occurred; for example, a job deviated from normal or from the specified plan, but still completed successfully. A warning may indicate deviation from the plan; for example, the number of sessions specified were not actually used, or a part of the job did not run. Warning conditions do not terminate the job.
  • Code 8—User error. A user error, such as a syntax error in the job script, terminated the job.
  • Code 12—Severe error. A fatal error terminated the job. A fatal error is any error other than a user error.
  • Code 16—No message destination is available.
  • When Permitted

    The LOGOFF command is permitted at any point in the input script. It logs users off immediately.

    Example  

    Assume that:

  • Each Teradata MultiLoad job requires successful execution of a Teradata SQL statement, such as a CREATE TABLE statement.
  • The statement fails with an unacceptable completion code.
  • If BADRC is set to &SYSRC after the failed Teradata SQL statement, you can use the following command to terminate the Teradata MultiLoad utility and return the unacceptable code to the client system:

    .LOGOFF &BADRC;

    Executing this command also drops the restart log table. If execution is terminated before the LOGOFF command is encountered, the restart log table is not dropped, so as to support a restart at a later time.

    Purpose  

    The LOGON command establishes a Teradata SQL session with Teradata Database. The ACCEPT and SET commands are valid commands preceding LOGON and LOGTABLE commands.

    Note: To prevent the password from appearing in the script, use Teradata Wallet. Refer to Security Administration and the appropriate installation guide for more information.

    Syntax  

    Standard LOGON Syntax

    On z/OS, with the use of the User Logon Exit routine in TDP, the user name is not required. For more information, see Teradata Director Program Reference.

    Single Sign-On LOGON Syntax

    Note: When logon encryption is enabled on the gateway, single sign-on is disabled on the client and standard logon syntax is used instead.

    Note: Use of the period preceding the LOGON command is optional.

    where:

     

    Syntax Element

    Description

    acctid

    Account identifier of up to 30 characters associated with the user name

    If an acctid is not specified, Teradata MultiLoad uses the default identifier defined when the user was created.

    password

    Password associated with the user name

    Note: Passwords that contain special characters must be enclosed in double quotes. The strings in double quotes will not be touched in the LOGON command.

    tdpid

    Optional character string that identifies the name of a TDP

    If the tdpid is not specified, Teradata MultiLoad uses the default TDP established by the system administrator.

    For mainframe-attached systems, the tdpid string must be in the form:

    TDPn

    where n is the TDP identifier.

    username

    User identifier

    Usage Notes  

    Table 48 describes the things to consider when using the LOGON command.

     

    Table 48: LOGON Usage Notes 

    Topic

    Usage Notes

    Logon Parameters

    For standard logon, the parameters (tdpid, username, password, and acctid) are used in all sessions established with Teradata Database. The LOGON command may occur only once.

    For single sign-on, if the Gateway to Teradata Database is configured to use single sign-on (SSO), and are already logged on to a Teradata client machine, then the machine name, user name, and password are not required in the LOGON command. The user name and password combination which were specified logging onto a Teradata client machine are authenticated with network security for a single sign-on such that valid Teradata users will be permitted to log on to Teradata Database. The use of SSO is strictly optional, unless the Gateway has been configured to accept only SSO-style logons.

    Note: On z/OS, with the use of User Logon Exit routine in TDP, userid is not required. Otherwise, userid is required. See Teradata Director Program Reference for more information.

    To connect to a Teradata Database system other than the one currently logged onto, the tdpid must be included in the LOGON command. If the tdpid is not specified, the default contained in clispb.dat will be used. Refer to Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems for information about setting defaults.

    To be interpreted correctly, the tdpid must be followed by the slash separator (‘/’), to distinguish the tdpid from a Teradata Database user name. For example, to connect to serverxyz, enter one of the following:

    .LOGON serverxyz/;

    .LOGON serverxyz/,,'acctinfo';

    If an account ID is to be used, the optional account ID must be specified in the LOGON command.

    Using LOGON with the LOGTABLE Command

    Both the LOGON and LOGTABLE commands are required.

    LOGON and LOGTABLE commands may appear in any order, but must precede other commands except RUN commands used to identify the file containing the LOGON command.

    If the LOGON command is entered first, Teradata MultiLoad warns that the LOGTABLE command also is required.

    Note: When Teradata MultiLoad attempts to connect the Main SQL session the first time and the Teradata Database is down, Teradata MultiLoad displays an error message and terminates.

    Note: When Teradata MultiLoad attempts to connect the Auxiliary SQL session, or the data sessions and the Teradata Database is down. Teradata MultiLoad tries to connect 16 times; if the Teradata Database is still down, Teradata MultiLoad displays an error message and terminates.

    Example  

    The following example presents both the LOGON command and LOGTABLE command as they typically occur:

    .logtable logtable001;
    .logon tdpx/me,paswd;

    Purpose  

    The LOGTABLE command specifies a restart log table for the Teradata MultiLoad checkpoint information. Teradata MultiLoad uses the information in the restart log table to restart jobs that are halted because of a Teradata Database or client system failure.

    The ACCEPT and SET commands are valid commands preceding LOGON and LOGTABLE commands.

    Syntax  

    where:

     

    Syntax Element

    Description

    dbname

    Name of the database under which the log table exists

    The default is the database name associated with the user name specified in the LOGON command. Teradata MultiLoad searches for the tname table in that database unless another database name is specified in this option.

    tname

    Name of the restart log table

    Usage Notes  

    Table 49 describes the things to consider when using the LOGTABLE command.

     

    Table 49: LOGTABLE Usage Notes 

    Topic

    Usage Notes

    Changing the dbname Specification

    The LOGTABLE dbname option must be used to change the dbname specification for a Teradata MultiLoad operation. A subsequent Teradata SQL DATABASE statement, which must appear after the LOGTABLE/LOGON command, to change the dbname specification cannot be used.

    Dropping the Restart Log Table

    In the case of a paused Teradata MultiLoad job, do not drop the restart log table until Teradata MultiLoad completes the import or delete task.

    For more information, see “Implications of Dropping Required Teradata MultiLoad-Created Tables” on page 41.

    Maintaining the Restart Log Table

    Teradata MultiLoad automatically maintains the restart log table. If the table is manipulated in any way, the restart capability is invalidated.

    The only valid user maintenance function is to drop the restart log table. Never delete rows from the table.

    The Restart Log Table

    The table specified as the Teradata MultiLoad restart log table does not have to be fully qualified.

    Sharing the Restart Log Table

    Do not share the restart log table between two or more Teradata MultiLoad jobs. Each Teradata MultiLoad job must have its own restart log table to ensure that the job runs correctly.

    If a distinct restart log table is not used for each Teradata MultiLoad job, the results are unexpected. One or more of the affected jobs may not be able to be restarted.

    Specifying a New or Existing Table

    If a table that does not exist is specified, Teradata MultiLoad creates the table and uses it as the restart log during this invocation of the utility.

    If a table that already exists is specified, then Teradata MultiLoad checks the table to determine whether the current invocation of the utility is a restart operation.

    Using LOGTABLE with the LOGON command

    Both the LOGTABLE and LOGON command are required.

    LOGTABLE and LOGON commands may appear in any order, but must precede other commands except RUN commands used to identify the file containing the LOGON command.

    If the LOGON command is entered first, Teradata MultiLoad warns that the LOGTABLE command is also required.

    The following example presents both the LOGTABLE and LOGON commands as they typically occur.

    .logtable Mine.Logtable001;
    .logon tdpx/me,paswd;

    Purpose  

    The PAUSE ACQUISITION command pauses the Teradata MultiLoad job during the acquisition phase of an import task. (The PAUSE ACQUISITION command cannot be used in a Teradata MultiLoad delete task.)

    Syntax  

    Usage Notes  

    Table 50 describes the things to consider when using the PAUSE ACQUISITION command.

     

    Table 50: Pause Acquisition Usage Notes 

    Topic

    Usage Notes

    Command Frequency and Placement

    The Teradata MultiLoad job can use more than one PAUSE ACQUISITION command, as long as each one is associated with a different import task. Only one PAUSE ACQUISITION command can be used in each import task.

    In a Teradata MultiLoad job script, PAUSE ACQUISITION commands must appear:

  • After the BEGIN MLOAD command
  • Before the END MLOAD command
  • If a PAUSE ACQUISITION command appears before the first IMPORT command, then Teradata MultiLoad pauses the job before starting the acquisition phase.

    If a PAUSE ACQUISITION command appears after the last IMPORT command, then Teradata MultiLoad pauses the job before starting the application phase.

    If a PAUSE ACQUISITION command appears between two IMPORT commands, then Teradata MultiLoad begins the acquisition phase, processes the first IMPORT command, and pauses the job before processing the second IMPORT command.

    Operational Requirements

    PAUSE ACQUISITION commands can be used to separate a Teradata MultiLoad job into two or more distinct operations provided that it has either:

  • One or more acquisition phase operations that load import data into the Teradata MultiLoad work tables.
  • or

  • An application phase operation that inserts the work table data into the target tables or views on Teradata Database.
  • Pause Conditions

    When a Teradata MultiLoad job pauses, the utility:

  • Displays a message indicating that the job was paused by user request.
  • Terminates the job with a return code of 4.
  • Restarting the Job

    To restart a paused Teradata MultiLoad job, remove the associated PAUSE ACQUISITION statement from the Teradata MultiLoad job script and resubmit the job. Teradata MultiLoad then resumes processing acquisition phase IMPORT commands, and either:

  • Continues into the application phase
  • or

  • Pauses again in response to another PAUSE ACQUISITION command
  • Purpose  

    When a Teradata MultiLoad task has been suspended or aborted before the end of the application phase, the RELEASE MLOAD statement removes the access locks from the target tables in Teradata Database and inhibits any attempts to restart the Teradata MultiLoad utility.

    Note: RELEASE MLOAD is a Teradata SQL statement that is recognized by BTEQ and Teradata MultiLoad. RELEASE MLOAD may be used in a Teradata MultiLoad job script or in an interactive Teradata MultiLoad session. It may also be used in BTEQ. Log on to BTEQ to enter the RELEASE MLOAD statement. Do not use a leading period (.) when using the RELEASE MLOAD statement in BTEQ.

    Syntax  

    where:

     

    Syntax Element

    Description

    dbname

    Name of the database where the target table to release exists.

    The default is the database name associated with the user name specified in the BTEQ LOGON command. BTEQ searches for the tablename table in that database unless this option specifies another database name.

    IN APPLY

    Teradata MultiLoad task that was in the application phase when it was suspended

    Note: The IN APPLY option cannot be used to release the locks that were placed on the Teradata MultiLoad target tables during the acquisition phase. In this case, use the RELEASE MLOAD statement without the IN APPLY option.

    tablename

    Name of the Teradata MultiLoad target table

    Note: The RELEASE MLOAD statement frees the target tables, but does not delete the error tables, the work tables, or the restart log table. They remain in the database, and must be dropped manually to free up the space and avoid conflicts when resubmitting the Teradata MultiLoad job.

    Usage Notes  

    Table 51 describes the things to consider when using the RELEASE MLOAD statement.

     

    Table 51: RELEASE MLOAD Usage Notes 

    Topic

    Usage Notes

    Locks

    To release the target tables, the Teradata MultiLoad release function must first obtain an exclusive lock on each specified table. This is not possible, and the RELEASE MLOAD statement will fail if either a database lock or a Teradata MultiLoad write lock has been placed on any of the specified tables.

    Teradata MultiLoad normally places write locks on the target tables:

  • Near the end of the acquisition phase of an import task, when data acquisition is complete and the data sort operation is about to begin
  • During the preliminary phase of a delete task when the DELETE statement is sent to Teradata Database
  • If the RELEASE MLOAD statement is executed before the Teradata MultiLoad task reaches these critical points, the release function completes before the utility can place write locks on the target tables.

    Messages Returned

    A release completed message indicates that all of the specified tables were releasable and the release function completed without error.

    If the release function encounters a table that cannot be released, it terminates with an error message identifying the table and the reason it could not be released.

    Acquisition phase error conditions include:

  • Table does not exist
  • Table has some other lock
  • Table is not a Teradata MultiLoad target table
  • The requesting user does not have the required privilege
  • Application phase error conditions include all of the acquisition phase error conditions plus:

  • Table has fallback
  • Table has a NUSI
  • Table has permanent journals
  • Table is not in the application phase
  • In this case, either:

  • Execute another RELEASE MLOAD statement, specifying only the tables that were releasable
  • Take corrective action, based on the error condition, and then execute another RELEASE MLOAD statement
  • Releasing Application Locks

    The IN APPLY option of the RELEASE MLOAD statement releases the application locks on the target tables that satisfy one of the following conditions:

  • No changes have been made to the table
  • The table is empty and has no permanent journals
  • The table has no fallback, no NUSIs, and no permanent journals
  • If none of these conditions is true, and the table has no permanent journals, then Teradata MultiLoad changes the application lock to a restoration lock that prevents Teradata MultiLoad from restarting and allows the following table accesses:

  • DELETE ALL
  • DROP FALLBACK
  • DROP INDEX
  • DROP TABLE
  • SELECT with access lock
  • If permanent journals are defined on the table, the only option is to drop the target table. If the target table belongs to a replication group and change data capture is active (for example, the replication group status is not Defined nor Terminated), then the following steps must be completed to drop the table:

    1 Put the replication group in Suspended status.

    2 Use the ALTER REPLICATION GROUP statement to remove the table from the replication group.

    3 Drop the table.

    Required Privilege

    Only the owner of the database or a user with one of the following privileges on the specified tables can use the RELEASE MLOAD statement:

  • Insert
  • Update
  • Delete
  • Table Requirements

    All of the tables that specified must have been involved in a Teradata MultiLoad task. If a specified table is not involved in a Teradata MultiLoad task, Teradata Database rejects the RELEASE MLOAD statement.

    Purpose  

    The ROUTE MESSAGES command specifies alternate destinations for Teradata MultiLoad utility output messages.

    Syntax  

    where:

     

    Syntax Element

    Description

    ECHO

    Additional destination, with a fileid specification

    For example, use the ECHO keyword to specify 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.

    fileid1 and fileid2

    Alternate message destinations in the external system

  • In z/OS, this is a DDNAME. (See Table 52.)
  • In UNIX OS and Windows, this is the path name for a file. If the path name has embedded white space characters, the entire pathname in single or double quotes must be endorsed.
  • If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks.

    If the same destination with both fileid1 and fileid2 parameters is specified, Teradata MultiLoad duplicates the messages at each destination.

    For more information, see the “ACCEPT” or “RUN FILE” command descriptions.

    Usage Notes  

    Table 52 describes the things to consider when using the ROUTE MESSAGES command.

     

    Table 52: ROUTE MESSAGES Usage Notes 

    Topic

    Usage Notes

    Default Message Destinations

    If the ROUTE MESSAGES command is not used, Teradata MultiLoad writes output messages to:

  • DDNAME SYSPRINT in z/OS
  • stdout in UNIX OS and Windows
  • z/OS fileid Usage Rules

    If a DDNAME is specified, Teradata MultiLoad writes messages to the specified source.

    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.
  • The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set.

    The DDNAME represents a data source on magnetic tape, the tape may be either labeled or non-labeled, as supported by the operating system.

    Specifying the System Console/Standard Output Device

    Use the asterisk character (*) as the fileid1 or fileid2 specifications to route messages to the system console/standard output (stdout) device.

    The system console is the:

  • Display screen in interactive mode
  • Standard output device in batch mode
  • For more information about the display screen and standard output devices, see “File Requirements” on page 31.

    Example  

    .ROUTE MESSAGES FILE OUTPUT;

    The messages are written to the file designated by OUTPUT from this point unless redirected by another ROUTE MESSAGES command.

    Note: On network-attached systems, if outfilename is used both to redirect stdout and as the fileid in a ROUTE MESSAGES WITH ECHO command, the results written to outfilename may be incomplete due to conflicting writes to the same 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 external system DD (or similar) statement specifies a file.

  • In z/OS, this is a DDNAME. (See Table 53.)
  • In UNIX OS and Windows, this is the path name for a file. If the path name has embedded white space characters, the entire pathname must be endorsed in single or double quotes.
  • If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks.

    IGNORE charpos1 and charpos2

    Start and end character positions of a field in each input record that contains extraneous information.

    For example:

  • Use charpos1 to ignore only the single specified character.
  • Use charpos1 THRU to ignore all characters from charpos1 through the end of the record.
  • Use THRU charpos2 to ignore all characters from the beginning of the record through charpos2.
  • Use charpos1 THRU charpos2 to ignore all characters from charpos1 through charpos2.
  • Usage Notes  

    Table 53 describes the things to consider when using the RUN FILE command.

     

    Table 53: RUN FILE Usage Notes 

    Topic

    Usage Notes

    Executing the RUN FILE Command

    After Teradata MultiLoad executes the RUN FILE command, it reads additional commands 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 MultiLoad to resume reading its commands and DML statements from the previously active source:

  • SYSIN for z/OS
  • stdin (normal or redirected) for UNIX OS and Windows
  • Note: SYSIN/stdin remains the active input source after Teradata MultiLoad processes any user-provided invocation parameters.

    Nested RUN Commands

    The source specified by a RUN FILE command can have up to 16 levels of nested RUN commands.

    -i scriptencoding parameter

    When the -i scriptencoding parameter is used, the specified encoding form and byte order apply to all the command files specified by the .RUN FILE command and any nested RUN FILE commands.

    Specifying the System Console/Standard Input Device

    Use the asterisk character (*) as the fileid specification for the system console/standard input (stdin) device.

    The system console is the:

  • Keyboard in interactive mode
  • Standard input device in batch mode
  • For more information about the keyboard and standard input devices, see “File Requirements” on page 31.

    z/OS fileid Usage Rules

    If a DDNAME is specified, Teradata MultiLoad reads data records from the specified source.

    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.
  • The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set.

    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.

    Purpose  

    The SET command assigns a data type and a value to a Teradata MultiLoad variable.

    Syntax  

    where:

     

    Syntax Element

    Description

    expression

    New value for the utility variable var

    var

    Name of the Teradata MultiLoad variable to be set to the evaluated expression

    Usage Notes  

    Table 54 describes the things to consider when using the SET command.

     

    Table 54: SET Usage Notes 

    Topic

    Usage Notes

    Changing the Data Type

    The SET command also dynamically changes the data type to that of the assigned value if it had already been defined.

    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:

    4.0E-75 <=abs(float variable)<7.0E75

    Declaring Variables

    Variables need not be declared in advance to be the object of the SET command. If a variable does not already exist, Teradata MultiLoad creates it.

    Variables used to the right of TO in the expression must be declared in advance.

    Variable Substitution

    Teradata MultiLoad variable can be substituted wherever substitution is allowed.

    Examples  

    Teradata MultiLoad supports concatenation of variables, using the SET command, such as:

    .SET C TO 1;
    .SET D TO 2;
    .SET X TO &C.&D;

    In this example, X evaluates to 12.

    If a decimal point is added to the concatenated variables, then X evaluates to 1.2, as in:

    .SET C TO 1;
    .SET D TO 2;
    .SET X TO &C..&D;

    Purpose  

    The SYSTEM command submits an operating system command to the client environment during a Teradata MultiLoad operation.

    Syntax  

    where:

     

    Syntax Element

    Description

    oscommand

    Any legal command in the client operating system

    Usage Notes  

    The SYSTEM command suspends the current Teradata MultiLoad operation to execute the client operating system command.

    When the client operating system command completes, Teradata MultiLoad displays the return code from the invoked command and updates the &SYSRC variable.

    Purpose  

    The TABLE command identifies a table whose column names and data descriptions are used as the names and data descriptions of fields of the input records. These are assigned in the order defined.

    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

    Usage Notes  

    Table 55 describes the things to consider when using the TABLE command.

     

    Table 55: TABLE Usage Notes 

    Topic

    Usage Notes

    Column Names in the Referenced Table

    The column names of the specified table must be Teradata SQL column names that need not be enclosed in quotation marks.

    Tables cannot be created with invalid column names, and any nonstandard column name produces an error condition, depending on the nature of the divergence from the standard. The errors are:

  • Embedded blanks cause a syntax error, depending on the non‑blank contents of the name.
  • Invalid characters cause an invalid name error.
  • Reserved words cause a syntax error that mentions invalid use of the reserved word.
  • Intermixing TABLE Commands with FIELD or FILLER commands

    One or more TABLE commands with the FIELD or FILLER following a LAYOUT command can be intermixed.

    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 MLOAD or BEGIN DELETE MLOAD command, but must either be the 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.

    TABLE command and UDT type

    When the TABLE command is used and the table contains a structured UDT type, Teradata MultiLoad 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.

    Purpose  

    The UPDATE command is a version of the Teradata SQL UPDATE statement that changes field values in existing rows of a table.

    Syntax  

    where:

     

    Syntax Element

    Description

    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
  • Values from fields of input data records
  • tname

    Table or view to be updated

    This table was previously identified as tname1 in the BEGIN MLOAD command.

    If tname is not explicitly qualified by database name, the current default database qualifies it.

    WHERE condition

    Conditional clause that specifies 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.

    Usage Notes  

    Table 56 describes the things to consider when using the UPDATE statement.

     

    Table 56: UPDATE Usage Notes 

    Topic

    Usage Notes

    OR Construct

    The OR construct cannot be used in an UPDATE statement.

    To accomplish the result normally achieved with the OR construct, use two separate UPDATE statements and use the APPLY clause of the IMPORT command to apply them conditionally.

    Referencing Field Names

    Make references to fields of the input data records for the expr and WHERE conditional as follows:

    :fieldname

    where fieldname is defined by a FIELD or TABLE command of the layout referenced by an IMPORT command using this UPDATE statement.

    For the WHERE condition clause, the equality values for all the columns of the primary index must be explicitly specified.

    Required Privileges

    To use the UPDATE statement, the UPDATE privilege on the tname table or view is required.

    Specifying Multiple UPI Columns in an Import Task

    If multiple UPI columns in an import task are specified, specify them all in the WHERE clause of the UPDATE statement.

    In this case, the WHERE clause is fully qualified, thereby allowing Teradata MultiLoad to optimize the processing.

    Update Object Restrictions

    If the object of the UPDATE statement is a view, it must not specify a join. Teradata MultiLoad operates only on single tables, so UPDATE statements must not contain any joins.

    Only one object may be identified and that must be a target object as specified in the BEGIN MLOAD command.

    Example  

    The following example depicts an input data source that contains a series of 14-byte records. Each record contains the value of the primary index column (EmpNo) of a row of the Employee table whose PhoneNo column is to be assigned a new phone number from field Fone. The example assumes that the current default database is Personnel, the database containing the Employee table.

    .LAYOUT Layoutname; 
    .FIELD EmpNum 1 INTEGER; 
    .FIELD Fone * (CHAR (10)); 
    .DML LABEL DMLlabelname; 
    UPDATE Employee SET PhoneNo = :Fone WHERE EmpNo = :EmpNum;

    Purpose  

    The VERSION command, which is primarily for developer use, displays version information for each utility component, including MultiLoad, Teradata ICU, Teradata CLI and Teradata Data Connector.

    Syntax  

    Example  

    0001 .version;
         MLDVER = MLDCLI   15.10.00.01 2014/05/20
         MLDVER = MLDCNTL  14.10.00.00 2012/10/16
         MLDVER = MLDEXEC  15.00.00.03 2013/12/03
         MLDVER = MLDLANG  15.10.00.00 2014/03/20
         MLDVER = MLDMAIN  14.10.00.01 2012/02/15
         MLDVER = MLDMISC  15.00.00.00 2013/12/17
         MLDVER = MLDNOTFY 15.00.00.01 2013/03/21
         MLDVER = MLDPREP  15.00.00.05 2013/12/17
         MLDVER = MLDSTMTS 15.10.00.02 2014/04/02
         UT$VER = UMBCNTLR 15.00.00.04 2013/12/02
         UT$VER = UMBDLOC  15.00.00.07 2014/01/08
         UT$VER = UMBEXEC  15.00.00.00 2013/06/27
         UT$VER = UMBOSDEP 14.10.00.03 2012/03/12
         UT$VER = UMBSTFM  15.00.00.02 2013/10/05
         UT$VER = UMBSTIN  15.00.00.04 2013/11/11
         UT$VER = UMBSTLG  15.00.00.00 2013/06/27
         UT$VER = UMBSTOP  15.00.00.01 2013/09/03
         UT$VER = UMBSTQ   14.00.00.01 2011/05/26
         UT$VER = UMBSTX   13.01.00.00 2008/09/10
         UT$VER = UMUCNTIN 15.00.00.01 2013/06/02
         UT$VER = UMUOUT   14.00.00.03 2011/11/04
         UT$VER = UTCLI    15.10.00.02 2014/05/06
         UT$VER = UTYCRMGR 14.10.00.02 2012/03/12
         UT$VER = UTYCVTR  15.00.00.00 2013/10/05
         UT$VER = UTYEXEVL 15.00.00.06 2014/03/11
         UT$VER = UTYEXPRS 15.00.00.03 2013/12/03
         UT$VER = UTYIO    15.10.00.00 2014/05/20
         UT$VER = UTYKUNX  14.10.00.00 2012/04/25
         UT$VER = UTYLOADM 14.10.00.03 2012/03/12
         UT$VER = UTYLOGW  13.01.00.00 2008/09/16
         UT$VER = UTYMBCS  14.10.00.03 2012/03/21
         UT$VER = UTYMISC  15.00.00.02 2013/11/08
         UT$VER = UTYMSG   14.10.00.01 2012/02/15
         UT$VER = UTYMVSQ  13.01.00.00 2008/09/10
         UT$VER = UTYNFY   14.10.00.00 2012/10/31
         UT$VER = UTYPARSR 15.00.00.10 2014/03/20
         UT$VER = UTYSMPR  15.00.00.02 2013/11/12
         UT$VER = UTYVSUB  15.00.00.04 2014/01/16
          CLIV2     : 15.10.00.00
          MTDP      : 15.00.00.09
          MOSIos    : 15.00.00.00
          MOSIDEP   : 15.00.00.00
          OSENCRYPT : N/A
          OSERR     : 14.00.00.00
         ICUVER = TDICU, 15.10.00.00
         PMVER = Teradata Data Connector, 15.10.00.00
         PMVER = PMPROCS, 15.00.00.10
         PMVER = PMRWFMT, 15.00.00.00
         PMVER = PMTRCE, 13.10.00.02
         PMVER = PMMM, 13.00.00.01
         PMVER = PMUDDI, 15.00.00.04
         PMVER = DCUDDI, 15.00.00.11
         PMVER = PMHEXDMP, 14.10.00.00
         PMVER = PMUNXDSK, 15.10.00.02