DEFINE | Usage Notes | Teradata FastLoad - Usage Notes - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-18
dita:mapPath
ije1544831946874.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2411
lifecycle
previous
Product Category
Teradata Tools and Utilities
The table in this section describes the things to consider when using the DEFINE command.
  • Data Type Descriptions
    Depending on the data type phrase of the CREATE TABLE statement, Teradata FastLoad stores data with CHAR(n) and VARCHAR(n) data type specifications as follows:
    • When the DEFINE command data type attribute is CHAR(n) and the:

      CREATE TABLE datadesc attribute is CHAR(n), Teradata FastLoad stores the data in fixed-length format, entire field.

      CREATE TABLE datadesc attribute is CHAR(m), Teradata FastLoad stores the data in fixed-length format, padded if m> n, truncated if m < n.

      CREATE TABLE datadesc attribute is VARCHAR(m), Teradata FastLoad stores the data in variable-length format with blanks trimmed.

    • When the DEFINE command datatype attribute is VARCHAR(n) and the:

      CREATE TABLE datadesc attribute is VARCHAR(m), Teradata FastLoad stores the data in variable-length format, no padding, blanks not trimmed.

      CREATE TABLE datadesc attribute is CHAR(m), Teradata FastLoad stores the data in padded or truncated format, as required.

  • Input Length and Field Descriptions

    The following table lists the input length and field description for each data type specification.

    • In a UTF-16 session, a character size is 2 bytes. For CHAR(n) or VARCHAR(n) field of the CREATE TABLE, the corresponding field size in DEFINE command must be double, that is CHAR(n*2) or VARCHAR(n*2), respectively.
    • In a UTF-8 session, a character size is from 1 to 3 bytes. For CHAR(n) or VARCHAR(n) field of the CREATE TABLE, the corresponding field size in DEFINE command must be triple, that is CHAR(n*3) or VARCHAR(n*3), respectively.
Input Length and Field Descriptions 
Data Type Length Description
BIGINT 8 bytes 64-bit signed binary
BYTE(n) n n bytes
BYTEINT 1 byte 8-bit signed binary
CHAR, CHARS(n), and CHARACTERS(n) n bytes n n ASCII characters
DATE 4 bytes 32-bit integer in YYYMMDD format as a decimal value
If a DATEFORM command has been used to specify ANSIDATE as the DATE data type, Teradata FastLoad internally converts each DATE field to a CHAR(10) field.
DECIMAL(x) and DECIMAL(x,y) 1, 2, 4, 8, or 16 bytes for network;

packed decimal for mainframe

128-bit double precision, floating point

For more information on the DECIMAL data type, see Teradata Vantage™ - Data Types and Literals, B035-1143.

FLOAT 8 bytes 64-bit, double precision, floating point
GEOSPATIAL DATA maximum 64000 FastLoad does not support Geospatial data represented by LOBs.
INTEGER 4 bytes 32-bit, signed binary
LONG VARCHAR m + 2 characters where m = 32000 16-bit integer, count m, followed by m ASCII characters
LONG VARCHAR is interpreted as VARCHAR (64000), which means that the combination of the client-side session character set and the server-side storage character set can cause a LONG VARCHAR specification in a DML USING clause to mean something other than VARCHAR(64000). Therefore, it is recommended that LONG VARCHAR be specified only if it is known that both the server-side and client-side character sets are single-byte.
NUMBER(p,s)

NUMBER(p)

or

NUMBER

NUMBER(*,y)

maxsize = 19 The first two forms are fixed point NUMBER. Other forms are floating point NUMBER.

For more information on the NUMBER data type, NUMBER see Teradata Vantage™ - Data Types and Literals, B035-1143.

PERIOD(DATE) max=8 bytes 4-byte, signed integer flipped to client form. This integer represents a date in the same manner as for a DATE data type

(Example: (10000*(year-1900)) + (100*month) + day).

precision (n/a)

The precision value specified must be between 0 and 6 inclusive.

PERIOD(TIME[(n)]) max =12 bytes Second: 4-byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal.

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte to client form. This byte represents the number of minutes

precision n

The precision value specified must be between 0 and 6 inclusive.

PERIOD(TIME[(n)] WITH TIME ZONE) max=16 bytes Second: 4-byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal.

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte. This byte represents the number of minutes.

Time Zone_Hour: 1 unsigned byte. This byte represents the hours portion of the time zone displacement along with whether the displacement is + or -.

Time Zone Minute: 1 unsigned byte. This byte represents the minutes portion of the time zone displacement.

precision n

The precision value specified must be between 0 and 6 inclusive.

PERIOD(TIMESTAMP[(n)]) max=20 bytes Second: 4-byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal.

Year: 2-byte, signed short integer flipped to client form. This byte represents the year value.

Month: 1 unsigned byte. This byte represents the month value.

Day: 1 unsigned byte. This byte represents the day of the month.

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte. This byte represents the number of minutes.

precision n

The precision value specified must be between 0 and 6 inclusive.

PERIOD(TIMESTAMP[(n)] WITH TIME ZONE) max=24 bytes Second: 4-byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal.

Year: 2-byte, signed short integer flipped to client form. This byte represents the year value.

Month: 1 unsigned byte. This byte represents the month value.

Day: 1 unsigned byte. This byte represents the day of the month.

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte. This byte represents the number of minutes.

Time Zone_Hour: 1 unsigned byte. This byte represents the time zone displacement in hours along with whether the displacement is + or -.

Time Zone Minute: 1 unsigned byte. This byte represents the time zone displacement in minutes.

precision n

The precision value specified must be between 0 and 6 inclusive.

SMALLINT 2 bytes 16-bit, signed binary
VARCHAR(n) m + 2 bytes where m = 32000 16-bit integer, count m, followed by m ASCII characters
VARBYTE(n) m + 2 bytes where mn 16-bit integer, count m, followed by m bytes of data
GRAPHIC(n) (n*2) bytes, if n is specified; otherwise, 2 bytes, as n = 1 is assumed n double-byte characters (1n is the length of the input stream in terms of double-byte characters)
VARGRAPHIC(n) m + 2 bytes where m/2 ≤ n 2-byte integer followed by m/2 double-byte characters
For both VARGRAPHIC and LONG VARGRAPHIC, m, a value occupying the first two bytes of the input data, is the length of the input in bytes, not characters. Each multibyte character-set character is 2 bytes.
  • GRAPHIC Data Types

    GRAPHIC data types define multibyte character set data. Teradata FastLoad accepts GRAPHIC data in its input data when a site is defined for kanji.

    The DEFINE command supports these types of input data:
    • GRAPHIC
    • VARGRAPHIC
    • LONG VARGRAPHIC

      The format to accommodate multibyte character sets and data containing multibyte characters is:

    • “G” and “XG” for mainframe-attached systems
    • “XG” and the standard character string format for workstation-attached systems
      where the following is true:
      Syntax Element Description
      “G” G’<....>’

      where the following is true:

      “<” and “>” represent the Shift-Out (0x0E) and Shift-In (0x0F) characters, respectively:
      • All characters in between must be valid characters for the character set
      • The number of characters within the Shift-Out/Shift-In must be an even number.
      “XG” hhhh’XG
      where the following is true:
      • hh” represents a pair of hexadecimal digits (0-9 and A-F)
      • Each pair of hexadecimal digits represents a single GRAPHIC character.
      • Since a maximum of 80 bytes may be specified in a NULLIF clause, this translates to 80 pairs of hexadecimal digits.
  • NULLIF Data Type Restrictions and Limitations

    The NULLIF option nulls a column in a table when the data field is a certain value. A field with a value of zero, for example, could represent a null date. To meet this requirement, enter the field definition in the DEFINE command as:

    DueDate (DATE, NULLIF =  0)

    Teradata FastLoad compares the value entered in the NULLIF clause with the actual data row. If they match, the utility sets the appropriate indicator bit to ON for the column in that row and sends both the row and the indicator bit string to the database. The database then inserts a null value into the column.

    VARCHAR fields are checked to ascertain if the length of the NULLIF string matches the 2-byte length indicator field (in the data row). The values are compared only if they are equal. If a value is a NULLIF value that equals 10 bytes, Teradata FastLoad compares it with the first 10 bytes of the corresponding field in the data row.

    FastLoad does not support NULLIF clause on period data type columns.

    The following minimum and maximum values may not apply in some applications because of the runtime environment of the individual platform:

    The following table lists the limitations by data type.
    Limitations by Data Type 
    Data Type Limitations Examples
    BYTE Up to 80 hexadecimal digits, enclosed by single quotes and must be an even number. “XB” is required after the hex string.

    The total number of bytes must not exceed two times the number of bytes specified in the data description.

    Characters must be within the range of 0-9 or A-F.

    Valid examples:
    DEFINE T1(BYTE (7), NULLIF = ’01’XB);
    DEFINE T1(BYTE (7), NULLIF =
      ’0123456789ABCD’XB);

    Invalid examples:

    DEFINE T1(BYTE (7), NULLIF = ’0’XB) ;
    DEFINE T1(BYTE (7), NULLIF = ’0M’XB) ;
    BYTEINT Must be within the range of -128 to 127. Valid examples:
    DEFINE T1(BYTEINT, NULLIF = 123) ;
    DEFINE T1(BYTEINT, NULLIF = -123) ; 

    Invalid examples:

    DEFINE T1(BYTEINT, NULLIF = 129) ;
    DEFINE T1(BYTEINT, NULLIF = -129) ;
    CHAR, CHARS, and CHARACTERS For normal string format, from 1 to 80 bytes enclosed in single quotes.

    For “XC” format, up to 80 pairs of hexadecimal digits enclosed in single quotes. This must be an even number and the “XC” is required. Each pair of hexadecimal digits corresponds to a single character.

    The total number of characters defined in the NULLIF option must not exceed the number of characters specified by the data definition. Character compare operations are case sensitive, and apply only to the first 80 bytes.

    Valid examples:
    DEFINE T1(CHAR (7), NULLIF = ’ ’) ;
    DEFINE T1(CHAR (7), NULLIF = ’ABCDEFG’) ;

    Invalid example:

    DEFINE T1(CHAR (7), NULLIF = ’ABCDEFGH’) ;
    DEFINE T1(CHAR (7), NULLIF = ’ABCDEFGH’XC) ;
    DATE INTEGER format only and cannot be negative. Valid examples:
    DEFINE T1(INTEGER, NULLIF = 123) ;
    DEFINE T1(DATE, NULLIF = 941015) ;

    Invalid example:

    DEFINE T1(DATE, NULLIF=94-10-15);
    DECIMAL Must be specified by a zoned number less than or equal to 38 digits.

    The number of digits specified in the NULLIF option must not exceed the number of digits entered in the data definition. If the NULLIF value contains more digits after the decimal point than are defined, results are undefined.

    Valid examples:
    DEFINE T1(DECIMAL (5,2), NULLIF = 0) ;
    DEFINE T1(DECIMAL   (5,2), NULLIF = 123.45) ; 

    Invalid example:

    DEFINE T1(DECIMAL (6,0), NULLIF = 1234567) ;
    FLOAT Floating point values are represented differently on the database and on some of the other platforms. Consequently, compare operations with exported floating point numbers may not function properly.

    The format for floating point numbers is:

    xxx.xxx or xx.xxE(+/-)yy or xE(+/-)yy or xxx

    The range of valid floating point values on the various platforms is:
    • z/OS: 1E-36 to 1E+35
    • UNIX OS: 4.94065645841246544e-324 to 1.79769313486231470e+308
    • Windows: 3.4e-38 to 3.4e+38
    A valid example:
    DEFINE T1(FLOAT, NULLIF = 123) ;
    GRAPHIC and VARGRAPHIC From 1 to 80 characters and must be enclosed in single quotes.

    For mainframe-attached systems, the quoted string must be preceded by “G” or followed by “XG”.

    For workstation-attached systems, the quoted string may be followed by “XG”, but cannot be preceded by “G”.

    When using the "G" format, the total number of characters defined by the NULLIF clause must not exceed two times the number of bytes specified in the data description.

    When using the "XG" format, the total number of hexadecimal digits defined by the NULLIF clause must not exceed four times the number of bytes specified in the data description.

    The GRAPHIC or VARGRAPHIC string has this form:

    G’<ABC>’
    where <ABC> is the quoted string of valid MBC and the characters < and > represent 0x0E and 0x0F.
    Valid examples on mainframe-attached systems:
    DEFINE T1(GRAPHIC(4), NULLIF = G'<ABCDEFGH>');
    DEFINE T1(GRAPHIC(4), NULLIF = G'<01234567>');

    Invalid examples on mainframe-attached systems:

    DEFINE T1(GRAPHIC(4), NULLIF =    G'<01234567ABCD>');
    DEFINE T1(GRAPHIC(4), NULLIF = G'ABCD0123');

    Valid examples on workstation-attached systems:

    DEFINE T1(GRAPHIC(4), NULLIF = 'ABCDEFGH');
    DEFINE T1(GRAPHIC(4), NULLIF = '01234567');

    Invalid examples on workstation-attached systems:

    DEFINE T1(GRAPHIC(4), NULLIF = G'<ABCDEFGH>');
    DEFINE T1(GRAPHIC(4), NULLIF = G'<01234567>');
    INTEGER Integer fields and date fields must be within the range of

    -2147483648 to 2147483647.

    Valid examples:
    DEFINE T1(INTEGER, NULLIF = 123) ;
    DEFINE T1(DATE, NULLIF = 941015) ;
    SMALLINT Small integer fields must be within the range of

    -32768 to 32767.

    Valid examples:
    DEFINE T1(SMALLINT, NULLIF = 123) ;
    DEFINE T1(SMALLINT, NULLIF = -123) ; 

    Invalid examples:

    DEFINE T1(SMALLINT, NULLIF = 32768) ;
    DEFINE T1(SMALLINT, NULLIF = -32769) ;
    VARBYTE 80 hex digits, enclosed by single quotes and must be an even number. “XB” is required after the hex string.
    VARCHAR For normal string format, 1-80 bytes enclosed in single quotes.

    For “XC” format, up to 80 pairs of hexadecimal digits, enclosed in single-quotes and must be an even number. The “XC” is required, and each pair of hexadecimal digits corresponds to a single character.

  • Numeric Fields

    The MAXIMUM and MINIMUM range for all fields is the default for each machine implementation. These values generally agree with the database except in cases where they are limited by the implementation. These values are documented by the manufacturer or can be found in the “C” language guide for that machine.

  • Using Table Definitions to Define Data

    Use either of the following commands to retrieve a list of field names from the referenced table:

    HELP TABLE  tname  ;
      INSERT  tname.* ;
    Do not use both of these commands together. In addition, do not use the tname.* version of an INSERT statement when using Unicode data from the following types of sessions. For more information about this precaution, see Usage Notes.
    • 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).

      When this format of the INSERT statement, is used Teradata FastLoad constructs a list of field names from the table definition. During the insert operation, the utility gets the field names and their data types from the CREATE TABLE statement used to define the table and from the table definition.

      The following example uses an INSERT statement to get a list of field names from a table called Employee:

      LOGON dbc/peterson,veep ;
      BEGIN LOADING Employee ERRORFILES   Etable1, Etable2 ;
      DEFINE FILE = Accounts ;
      INSERT Employee.*;

      The following example uses a HELP command to get a list of field names from a table called Employee:

      LOGON dbc/peterson,veep ;
        BEGIN LOADING Employee ERRORFILES   Etable1, Etable2 ;
      DEFINE FILE = INFILE ;
      HELP TABLE Employee ;
      INSERT INTO Employee (EmpNum, Name)   VALUES (:EmpNum, :Name) ;
      With either of these examples, a DEFINE command specifying either the input data source or INMOD parameter must also be entered.

      When a DEFINE command does not fit on one input line, enter either:

    • The command on several lines

      or

    • Several DEFINE commands

      In either case, Teradata FastLoad concatenates the field definitions until an INSERT statement is entered.

      Also, when more than one DEFINE command is entered, the field definitions in all must appear in the same order as they do in the input data record, just as they would if they were entered in a single DEFINE command. And, only a FILE or INMOD declaration can be in one of the DEFINE commands.

  • Using ANSI/SQL DateTime Data Types

    When the DATEFORM command is used to specify ANSIDATE as the DATE data type, Teradata FastLoad 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 FastLoad DEFINE command.

    After the conversion to fixed-length CHAR data type, if UTF-16 session character set is used, the size should be doubled, and if UTF-8 session character set is used, the size should be tripled.

    For the conversion specifications and format examples for each ANSI/SQL DateTime specification, see the following.
    DATE
    Convert to: CHAR(10)
    Format: yyyy/mm/dd
    Example: 1998/01/01
    TIME
    TIME (n)
    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: 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)
    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: 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
    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: 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
    Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: 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
    INTERVAL YEAR
    INTERVAL YEAR (n)
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n)
    Format (n = 2): yy
    Example: 98
    Format (n = 4): yyyy
    Example: 1998
    INTERVAL YEAR TO MONTH
    INTERVAL YEAR (n) TO MONTH
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n+ 3)
    Format (n= 2): yy - mm
    Example: 98-12
    Format (n= 4): yyyy
    Example: 1998-12
    INTERVAL MONTH
    INTERVAL MONTH (n)
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n)
    Format (n = 2): mm
    Example: 12
    Format (n = 4): mmmm
    Example: 0012
    INTERVAL DAY
    INTERVAL DAY (n)
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n)
    Format (n = 2): dd
    Example: 31
    Format (n = 4): dddd
    Example: 0031
    INTERVAL DAY TO HOUR
    INTERVAL DAY (n) TO HOUR
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n + 3)
    Format (n= 2): dd hh
    Example: 31 120
    Format (n = 4): dddd hh
    Example: 031 12
    INTERVAL DAY TO MINUTE
    INTERVAL DAY (n) TO MINUTE
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n + 6)
    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)
    Where:
    • n is the number of digits, 1 through 4. (Default = 2.)
    • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: CHAR(n + 9 + m + (1 if m > 0, 0 otherwise))
    Format (n = 2, m = 0): Format (n = 2, m = 0):
    Example: 31 12:59:59
    Format (n = 4, m = 4): dddd hh:mm:ss.ssss
    Example: 0031 12:59:59:59.1234
    INTERVAL HOUR
    INTERVAL HOUR (n)
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n)
    Format (n = 2): hh
    Example: 12
    Format (n = 4): hhhh
    Example: 0012
    INTERVAL HOUR TO MINUTE
    INTERVAL HOUR (n) TO MINUTE
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n + 3)
    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)
    Where:
    • n is the number of digits, 1 through 4. (Default = 2.)
    • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: CHAR(n + 6 + m + (1 if m > 0, 0 otherwise))
    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)
    Where n is the number of digits, 1 through 4. (Default = 2.)
    Convert to: CHAR(n)
    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)
    Where:
    • n is the number of digits, 1 through 4. (Default = 2.)
    • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: CHAR(n + 3 + m + (1 if m > 0, 0 otherwise))
    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)
    Where:
    • n is the number of digits, 1 through 4. (Default = 2.)
    • m is the number of digits after the decimal point, 0 through 6. (Default = 6.)
    Convert to: CHAR(n + m + (1 if m > 0, 0 otherwise))
    Format (n = 2, m = 0): ss
    Example: 59
    Format (n = 4, m = 4): ssss.ssss
    Example: 0059.1234
  • Using 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. A period has two elements BEGIN (the beginning element) and END (the ending element) which have an element type that is one of the three DateTime data types.

    For the CHAR data type, “n” represents the size of the field. For PERIOD data types, this is not the case. “n” represents the precision (number of digits in the fractional part of seconds).

    PERIOD data is always represented externally in binary format

  • Using 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 FastLoad field definitions specified with the DEFINE command for SOURCE_TABLE table:

     EMP_ID  (INTEGER),
      EMP_NO (BYTEINT),
      COL003   (VARCHAR(47)),
      COL004   (VARCHAR(17)),
      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 is two samples of data for the COL003 column:

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

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

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

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

    The following is the calculation for the maximum representation for the COL004 column:
    • 1 byte for the left parenthesis
    • + 1 to 7 bytes for the first element
    • + 1 byte for the comma
    • + 1 to 7 bytes for the second element
    • + 1 byte for the right parenthesis
    • ----
    • 17 bytes

    The following 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 look as follows:

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

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

    For more information about the external representations for the ARRAY data type, see Teradata Vantage™ - Data Types and Literals, B035-1143.

  • Using Session Character Set KANJISJIS_0S

    When the session character set used is KANJISJIS_0S, if the CHAR(n) or VARCHAR(n) of the table to be loaded is defined as UNICODE character set, the corresponding field size in the DEFINE command should be doubled, that is CHAR(n*2) or VARCHAR(n*2), respectively.

    If the CHAR(n) or VARCHAR(n) of the table to be loaded is defined as LATIN character set, the corresponding field size in the DEFINE command remains the same, that is CHAR(n) or VARCHAR(n), respectively.