Usage Notes - MultiLoad

Teradata MultiLoad Reference

Product
MultiLoad
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-07-11
dita:mapPath
cgb1488824663145.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

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

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 (B035-1143).

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 (B035-1143).

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.
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 (B035-1143).

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.

The following table in this section (ANSI/SQL DateTime Specifications) provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

The following table 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 (B035-1143).

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