Specifying Data Types
Use the datadesc parameter to specify the type and length of data in the field. Teradata TPump generates the USING phrase accordingly with the user‑assigned field name to which the body of the DML statement refers.
For complete details on data types and data conversions, see SQL Data Types and Literals (B035‑1143).
The following is a short list of the input length and field description for the data type specifications which can be made in the datadesc parameter:
Graphic Data Type Specifications
GRAPHIC(n)
Where n is the length of the input stream in terms of double‑byte characters.
Length: n*2 bytes, if n is specified; otherwise 2 bytes, as n=1 is assumed.
Description: n double‑byte characters.
The following example illustrates the use of the GRAPHIC data types in support of kanji or multibyte character data. The FIELD statement can contain GRAPHIC data types.
.LAYOUT KANJIDATA;
.FIELD EMPNO * SMALLINT;
.FIELD LASTNAME * GRAPHIC(30);
.FILLER FIRSTNAME * GRAPHIC(30);
.FIELD JOBTITLE * VARGRAPHIC(30);
VARGRAPHIC(n)
Where n is the length of the input stream in terms of double‑byte characters.
Length: m + 2 bytes where m/2 <= 32000.
Description: 2‑byte integer followed by m/2 double‑byte characters.
LONG VARGRAPHIC
Length: m + 2 bytes where m/2 <= 32000.
Description: 2 byte integer followed by m/2 double‑byte characters.
Note: For both VARGRAPHIC and LONG VARGRAPHIC, m, a value occupying the first 2 bytes of the input data, is the length of the input in bytes, not characters. Each multibyte character set character is 2 bytes.
Note: LONG VARGRAPHIC also implies VARGRAPHIC (32000). Range is 0 to 32000 in a 64,000‑byte field.
Decimal Data Type Specifications
DECIMAL(x) and DECIMAL(x,y)
Length: 1, 2, 4, 8 or 16 bytes for network; packed decimal for mainframe
Description: 128‑bit double precision, floating point
Period Data Type Specifications
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:
The element type is one of the following DateTime data types.
For more information on the PERIOD data type, see SQL Data Types and Literals (B035‑1143).
NULLIF Performance
Using a large number of NULLIF clauses can cause a significant increase in the CPU usage on the system where Teradata TPump is running. This rise in CPU usage may increase the time the job takes to run.
An increase in CPU usage is most noticeable when the following do not exist:
To avoid an increase in CPU usage on the system running Teradata TPump, transfer the processing of NULLIF expressions to Teradata Database.
Example
Instead of specifying the following:
...
.FIELD fc * CHAR(5) NULLIF fc = 'empty';
.FIELD fi * INTEGER NULLIF fi = 0;
...
.DML LABEL ins;
INSERT INTO tbl1 VALUES(...,:fc,:fi,...);
Use this instead:
...
.FIELD fc * CHAR(5);
.FIELD fi * INTEGER;
...
.DML LABEL ins;
INSERT INTO tbl1 VALUES(...,NULLIF(:fc,'empty'),NULLIF(:fi,0),...);
Example
In more complex situations, as in the following example:
...
.FIELD fs * CHAR(1) ;
.FIELD fc * CHAR(5) NULLIF (fs <> 'M') AND (fs <> 'F');
.FIELD fi * INTEGER NULLIF fi < 0;
...
.DML LABEL ins;
INSERT INTO tbl2 VALUES(...,:fs,:fc,:fi,...);
Use this instead:
...
.FIELD fs * CHAR(1) ;
.FIELD fc * CHAR(5);
.FIELD fi * INTEGER;
...
.DML LABEL ins;
INSERT INTO tbl2 VALUES(...,:fs,
CASE WHEN (:fs = 'M') OR (:fs = 'F') THEN :fc ELSE NULL END,
CASE WHEN (:fi >= 0) THEN :fi ELSE NULL END,...);
Using ANSI/SQL DateTime Data Types
When the DATEFORM command is used to specify ANSIDATE as the DATE data type, each DATE field is internally converted 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 and field names in a Teradata TPump FIELD command.
Table 28 shows how to use ANSI/SQL DateTime specifications.
Data Type |
Variable Definition |
Conversion Example |
TIME TIME (n) |
n = number of digits after decimal point Valid values: 0–6 Default = 6 |
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) |
n = number of digits after decimal point Valid values: 0–6 Default = 6 |
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 |
n = number of digits after decimal point Valid values: 0–6 Default = 6 |
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 |
n = number of digits after decimal point Valid values: 0‑6 Default = 6 |
CHAR(25 + n + (1 if n > 0, otherwise 0)) Format (n = 0): yyyy‑mm‑dd hh:mm:ss{±}hh:mm Format (n = 4): |
INTERVAL YEAR INTERVAL YEAR (n) |
n = number of digits Valid values: 1‑4 Default = 2 |
CHAR(n) Format (n = 2): yy Format: (n = 4): yyyy |
INTERVAL YEAR TO MONTH INTERVAL YEAR (n) TO MONTH |
n = number of digits Valid values: 1‑4 Default = 2 |
CHAR(n + 3) Format (n = 2): yy‑mm Format: (n = 4): yyyy‑mm |
INTERVAL MONTH INTERVAL MONTH (n) |
n = number of digits Valid values: 1‑4 Default = 2 |
CHAR(n) Format (n = 2): mm Format: (n = 4): mmmm |
INTERVAL DAY INTERVAL DAY (n) |
n = number of digits Valid values: 1‑4 Default = 2 |
CHAR(n) Format (n = 2): dd Format: (n = 4): dddd |
INTERVAL DAY TO HOUR INTERVAL DAY (n) TO HOUR |
n = number of digits Valid values: 1‑4 Default = 2 |
CHAR(n + 3) Format (n = 2): dd hh Format: (n = 4): dddd hh |
INTERVAL DAY TO MINUTE INTERVAL DAY (n) TO MINUTE |
n = number of digits Valid values: 1‑4 Default = 2 |
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) |
n = number of digits Valid values: 1‑4 Default = 2 m = number of digits after decimal point Valid values: 0‑6 Default = 6 |
CHAR(n + 9 + m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): hh:mm:ss Format: (n = 4, m = 4): hhhh:mm:ss.ssss |
INTERVAL HOUR INTERVAL HOUR (n) |
n = number of digits Valid values: 1‑4 Default = 2 |
CHAR(n) Format: (n = 2): hh Format: (n = 4): hhhh |
INTERVAL HOUR TO MINUTE INTERVAL HOUR (n) TO MINUTE |
n = number of digits Valid values: 1‑4 Default = 2 |
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) |
n = number of digits Valid values: 1‑4 Default = 2 m = number of digits after the decimal point Valid values: 0‑6 Default = 6 |
CHAR(n + 6 + m + (1 if m > 0, otherwise 0)) Format: (n = 2, m = 0): hh:mm:ss Format: (n = 4, m = 4): hhhh:mm:ss.ssss |
INTERVAL MINUTE INTERVAL MINUTE (n) |
n = number of digits Valid values: 1‑4 Default = 2 |
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) |
n = number of digits Valid values: 1‑4 Default = 2 m = number of digits after decimal point Valid values: 0‑6 Default = 6 |
CHAR(n + 3 + m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): mm:ss Format: (n = 4, m = 4): mmmm:ss.ssss |
INTERVAL SECOND INTERVAL SECOND (n) INTERVAL SECOND (n,m) |
n = number of digits Valid values: 1‑4 Default = 2 m = number of digits after decimal point Valid values: 0‑6 Default = 6 |
CHAR(n + m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): ss Format: (n = 4, m = 4): ssss.ssss |