Usage Notes - FastExport

Teradata® FastExport Reference

Product
FastExport
Release Number
16.20
Published
September 2020
Language
English (United States)
Last Update
2020-09-11
dita:mapPath
lki1527114222329.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2410
lifecycle
previous
Product Category
Teradata Tools and Utilities

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

FIELD Command Usage Notes 
Topic Usage Notes
Command Placement and Frequency A FIELD command must be preceded by a LAYOUT command.

One or more FIELD commands, or a combination of FIELD command and FILLER command, define the composition of the input data record to supply values for the USING modifier of the SELECT statement.

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 FastExport layout definition for the sample SOURCE_TABLE 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 samples of data for the COL003 column:

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

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

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

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

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

  1 byte for the left parenthesis

+ 1 to 7 bytes for the first element

+ 1 byte for the comma

+ 1 to 7 bytes for the second element

+ 1 byte for the right parenthesis

__________________________________

17 bytes

The following are two samples 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 are two samples of data for the COL005 column:

Sample data 1: (-2147483648,+2147483647)

Sample data 2: (0,0)

Use the Teradata SQL "HELP TYPE" command to find out the maximum length for the ARRAY data type. For example, the information for the sample PHONENUMBERS_ARY, DECIMAL_ARY, and INTEGER_ARY ARRAY data types can look like 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 DECIMAL Data Types The following input length and field descriptions apply for the DECIMAL data type specifications which make in the datadesc parameter.

DECIMAL (x) and DECIMAL (x,y)

  • Length: 1, 2, 4, 8, or 16 bytes (network); packed decimal (mainframe)
  • Description: 128-bit double precision floating point

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

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.

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

Using ANSI/SQL DateTime Data Types When the DATEFORM command is used to specify ANSIDATE as the DATE data type, FastExport 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 FastExport FIELD command.

The following table provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

The following are descriptions of the ANSI/SQL Date Time Specifications.

DATE
Convert to: CHAR(10)
Format: yyy/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: hh:mm:ss
Example: 11:37:58
Format: 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): yyy-mmm-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: 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: 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: 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-mm
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.)
Conver to: CHAR(n)
Format (n = 2): dd
Example: 31
Format (n = 4): dddd
Example: 0031
INTERVAL DAY TO HOUR
INTERVAL DAY (n) TO HOUR
Wheren is the number of digits, 1 through 4. (Default = 2.)
Convert to: CHAR(n + 3)
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
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): dd hh:mm:ss
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:
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