DEFINE - FastLoad

Teradata FastLoad Reference

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

Purpose  

The DEFINE command:

  • Describes the fields in a record of input data that are inserted in the Teradata FastLoad table
  • Identifies the name of the input data source or use of an INMOD routine
  • Teradata FastLoad translates the DEFINE command specifications into a Teradata SQL USING clause, and links the USING clause with a subsequent INSERT statement. The Teradata FastLoad job must include DEFINE specifications for each field in a record from the input data source before executing an INSERT statement.

    Note: Though every field used in an INSERT statement must have been previously defined in a DEFINE command, every field so defined need not be used in an INSERT statement.

    Teradata FastLoad also uses the DEFINE command data type specifications to determine the format and record length of stored data.

    Syntax  

    where

     

    Syntax Element

    Description

    fieldname

    Name of a field in a record of the input data source, from left to right.

    You cannot use FILE, DDNAME, or INMOD for a fieldname.

    Note: Some or all of the field names can be omitted if the accompanying INSERT statement uses the “wild card” table name specification (tname.*) for all of the columns in the table. For the syntax of the tname.* specification, see the INSERT statement description later in this chapter.

    datatype

    Keyword or keyword phrase that specifies the data type of the field.

    For details on data types and data conversions, see SQL Data Types and Literals (B0351143).

    The valid data types for records in a Teradata FastLoad table are:

    BIGINT

    BYTE (n)

    BYTEINT

    CHARACTERS (n)

    DATE

    DECIMAL (x) or DECIMAL (x,y)

    FLOAT

    GRAPHIC (n)

    INTEGER

    LONG VARCHAR

    LONG VARGRAPHIC

    NUMBER (p)

    NUMBER

    NUMBER(p,s)

    NUMBER(*,s)

    PERIOD(DATE)

    PERIOD(TIME[(n)])

    PERIOD(TIME[(n)] WITH TIME ZONE)

    PERIOD(TIMESTAMP[(n)])

    PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)

    SMALLINT

    VARBYTE (n)

    VARCHAR (n)

    VARGRAPHIC (n)

    NULLIF=value

    Keyword phrase that loads the Teradata Database field with a null value if the defined client field contains the specified value.

    The value specification can be 1 to 80 bytes in length, and it pertains only to BYTE, CHAR and GRAPHIC types. It does not pertain to integer and float data types.

    The NULLIF option occurs only with the DEFINE command.

    FILE=filename

    Keyword phrase specifying the name of the data source that contains the input data.

    fileid must refer to a regular file. Specifically, pipes are not supported.

    Note: For mainframeattached systems, replace FILE with DDNAME for
    z/OS. DDNAME is the sequential data set for z/OS.

    Note: In UNIX and Windows, the fileid FastLoad supports file name of size of up to 1024.The filename can contain the following characters: parenthesis, comma, or equal sign.

    INMOD=name

    Keyword phrase specifying the name of a user exit routine that provides input data records.

    For more information about using INMOD routines, see “INMOD and Notify Exit Routines” on page 61.

    Note: If INMOD module output messages to stdout, the character set that INMOD uses is independent of the character set that Teradata FastLoad uses; the display on stdout can be of mixed character sets. For example, IMMOD can output messages in ASCII and Teradata FastLoad can output messages in UTF16.

    Note: In UNIX and Windows, FastLoad supports the INMOD name of size of up to 1024. The INMOD name can contain the following characters: parens, comma, or equal sign.

    Usage Notes  

    The following table 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
  • Table 28 lists the input length and field description for each data type specification.

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

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

     

    Table 28: Input Length and Field Descriptions 

    Data Type

    Length

    Description

    BIGINT

    8 bytes

    64bit signed binary

    BYTE(n)

    n bytes

    n bytes

    BYTEINT

    1 byte

    8bit signed binary

    CHAR, CHARS(n), and CHARACTERS(n)

    n bytes

    n ASCII characters

    DATE

    4 bytes

    32bit integer in YYYMMDD format as a decimal value

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

    128bit double precision, floating point

    For more information on the DECIMAL data type, see SQL Data Types and Literals (B0351143).

    FLOAT

    8 bytes

    64bit, double precision, floating point

    GEOSPATIAL DATA

    maximum 64000

    FastLoad does not support Geospatial data represented by LOBs.

    INTEGER

    4 bytes

    32bit, signed binary

    LONG VARCHAR

    m + 2 characters where m = 32000

    16bit integer, count m, followed by m ASCII characters

    Note: 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 SQL Data Types and Literals (B035‑1143).

    PERIOD(DATE)

    max=8 bytes

    4byte, signed integer flipped to client form. This integer represents a date in the same manner as for a DATE data type

    (Example: (10000*(year1900)) + (100*month) + day).

    precision (n/a)

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

    PERIOD(TIME[(n)])

    max =12 bytes

    Second: 4byte, 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: 4byte, 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: 4byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal.

    Year: 2byte, 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: 4byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal.

    Year: 2byte, 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

    16bit, signed binary

    VARCHAR(n)

    m + 2 bytes where m = 32000

    16bit integer, count m, followed by m ASCII characters

    VARBYTE(n)

    m + 2 bytes where m <= n

    16bit 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 doublebyte characters (1n is the length of the input stream in terms of doublebyte characters)

    VARGRAPHIC(n)

    m + 2 bytes where m/2 <= n

    2byte integer followed by m/2 doublebyte characters

    Note: 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 characterset 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 network‑attached systems
  • where

     

    Syntax Element

    Description

    “G”

    G’<....>’

    where

    “<” and “>” represent the ShiftOut (0x0E) and ShiftIn (0x0F) characters, respectively

  • All characters in between must be valid characters for the character set
  • The number of characters within the ShiftOut/ShiftIn must be an even number.
  • “XG”

    hhhh’XG

    where

  • hh” represents a pair of hexadecimal digits (09 and AF)
  • 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 Teradata Database. The Teradata 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:

    Table 29 lists the limitations by data type.

     

    Table 29: 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 AF.

    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=941015);

    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 Teradata 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: 1E36 to 1E+35
  • UNIX OS: 4.94065645841246544e324 to 1.79769313486231470e+308
  • Windows: 3.4e38 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 mainframeattached systems, the quoted string must be preceded by “G” or followed by “XG”.

    For networkattached 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 mainframeattached systems:

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

    Invalid examples on mainframeattached systems:

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

    Valid examples on networkattached systems:

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

    Invalid examples on networkattached 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, 180 bytes enclosed in single quotes.

    For “XC” format, up to 80 pairs of hexadecimal digits, enclosed in singlequotes 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 Teradata 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.* ;

    Note: 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 Table 36 on page 124.

  • 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) ;

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

     

    Table 30: ANSI/SQL DateTime Specifications 

    DATE

     

    Convert to:

    CHAR(10)

    Format:
    Example:

    yyyy/mm/dd
    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):
    Example:

    hh:mm:ss
    11:37:58

    Format: (n = 4):
    Example:

    hh:mm:ss.ssss
    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):
    Example:

    yyyymmdd hh:mm:ss
    19980904 11:37:58

    Format (n = 4):
    Example:

    yyyymmdd hh:mm:ss.ssss
    19980904 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):
    Example:

    hh:mm:ss{±}hh:mm
    11:37:5808:00

    Format (n = 4):
    Example:

    hh:mm:ss.ssss {±} hh:mm
    11:37:58.123408: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):
    Example

    yyyymmdd hh:mm:ss{±}hh:mm
    19980924 11:37:58+07:00

    Format (n = 4):
    Example:

    yyyymmdd hh:mm:ss.ssss{±}hh:mm
    19980924 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):
    Example:

    yy
    98

    Format (n = 4):
    Example:

    yyyy
    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):
    Example:

    yymm
    9812

    Format (n = 4):
    Example:

    yyyymm
    199812

    INTERVAL MONTH

    INTERVAL MONTH (n)

    Where n is the number of digits, 1 through 4. (Default = 2.)

    Convert to:

    CHAR(n)

    Format (n = 2):
    Example:

    mm
    12

    Format (n = 4):
    Example:

    mmmm
    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):
    Example:

    dd
    31

    Format (n = 4):
    Example:

    dddd
    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):
    Example:

    dd hh
    31 12

    Format (n = 4):
    Example:

    dddd hh
    0031 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):
    Example:

    dd hh:mm
    31 12:59

    Format (n = 4):
    Example:

    dddd hh:mm
    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):
    Example:

    dd hh:mm:ss
    31 12:59:59

    Format (n = 4, m = 4):
    Example:

    dddd hh:mm:ss.ssss
    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):
    Example:

    hh
    12

    Format (n = 4):
    Example:

    hhhh
    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):
    Example:

    hh:mm
    12:59

    Format (n = 4):
    Example:

    hhhh:mm
    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):
    Example:

    hh:mm:ss
    12:59:59

    Format (n = 4, m = 4):
    Example:

    hhhh:mm:ss.ssss
    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):
    Example:

    mm
    59

    Format (n = 4):
    Example:

    mmmm
    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):
    Example:

    mm:ss
    59:59

    Format (n = 4, m = 4):
    Example:

    mmmm:ss.ssss
    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):
    Example:

    ss
    59

    Format (n = 4, m = 4):
    Example:

    ssss.ssss
    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 SQL Data Types and Literals (B0351143).

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