17.00 - ARRAY Data Types Specifications - Parallel Data Pump

Teradata® Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-3021-220K

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

The following is a sample Teradata table definition which includes ARRAY data types:

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 LAYOUT definition for the sample SOURCE_TABLE table:

  .LAYOUT EMPDATAWITHARRAY;
  .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 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 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.