Purpose
The DEFINE command:
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 (B035‑1143). 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 mainframe‑attached systems, replace FILE with DDNAME for 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 UTF‑16. 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.
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:
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.
Data Type |
Length |
Description |
BIGINT |
8 bytes |
64‑bit signed binary |
BYTE(n) |
n bytes |
n bytes |
BYTEINT |
1 byte |
8‑bit signed binary |
CHAR, CHARS(n), and CHARACTERS(n) |
n bytes |
n ASCII characters |
DATE |
4 bytes |
32‑bit 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 |
128‑bit double precision, floating point For more information on the DECIMAL data type, see SQL 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 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 |
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 m <= n |
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 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 character‑set character is 2 bytes. |
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:
The format to accommodate multibyte character sets and data containing multibyte characters is:
where
Syntax Element |
Description |
“G” |
G’<....>’ where “<” and “>” represent the Shift‑Out (0x0E) and Shift‑In (0x0F) characters, respectively |
“XG” |
‘hhhh’XG where |
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.
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 =
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 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: 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 network‑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 network‑attached systems: DEFINE T1(GRAPHIC(4), NULLIF = 'ABCDEFGH');
DEFINE T1(GRAPHIC(4), NULLIF = '01234567');
Invalid examples on network‑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 |
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. |
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.
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.
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:
or
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.
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.
DATE |
|
Convert to: |
CHAR(10) |
Format: |
yyyy/mm/dd |
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 |
Format: (n = 4): |
hh:mm:ss.ssss |
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 |
Format (n = 4): |
yyyy‑mm‑dd hh:mm:ss.ssss |
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 |
Format (n = 4): |
hh:mm:ss.ssss {±} hh:mm |
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 |
Format (n = 4): |
yyyy‑mm‑dd hh:mm:ss.ssss{±}hh:mm |
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 |
Format (n = 4): |
yyyy |
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 |
Format (n = 4): |
yyyy‑mm |
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 |
Format (n = 4): |
mmmm |
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 |
Format (n = 4): |
dddd |
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 |
Format (n = 4): |
dddd hh |
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 |
Format (n = 4): |
dddd hh:mm |
INTERVAL DAY TO SECOND INTERVAL DAY (n) TO SECOND INTERVAL DAY TO SECOND (m) INTERVAL DAY (n) TO SECOND (m) |
|
Where |
|
Convert to: |
CHAR(n + 9 + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
dd hh:mm:ss |
Format (n = 4, m = 4): |
dddd hh:mm:ss.ssss |
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 |
Format (n = 4): |
hhhh |
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 |
Format (n = 4): |
hhhh:mm |
INTERVAL HOUR TO SECOND INTERVAL HOUR (n) TO SECOND INTERVAL HOUR TO SECOND (m) INTERVAL HOUR (n) TO SECOND (m) |
|
Where |
|
Convert to: |
CHAR(n + 6 + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
hh:mm:ss |
Format (n = 4, m = 4): |
hhhh:mm:ss.ssss |
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 |
Format (n = 4): |
mmmm |
INTERVAL MINUTE TO SECOND INTERVAL MINUTE (n) TO SECOND INTERVAL MINUTE TO SECOND (m) INTERVAL MINUTE (n) TO SECOND (m) |
|
Where |
|
Convert to: |
CHAR(n + 3 + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
mm:ss |
Format (n = 4, m = 4): |
mmmm:ss.ssss |
INTERVAL SECOND INTERVAL SECOND (n) INTERVAL SECOND (n,m) |
|
Where |
|
Convert to: |
CHAR(n + m + (1 if m > 0, 0 otherwise)) |
Format (n = 2, m = 0): |
ss |
Format (n = 4, m = 4): |
ssss.ssss |
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
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 (B035‑1143).
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.