15.00 - Specifying Data Types - Parallel Data Pump

Teradata Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

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:

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

    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:

  • FILLER commands in the LAYOUT
  • Input position gaps or overlaps
  • Concatenated fields
  • DROP clauses
  • 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.

     

    Table 28: 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
    Example: 11:37:58

    Format: (n = 4): hh:mm:ss.ssss
    Example: 11:37:58.1234

    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): yyyymmdd hh:mm:ss
    Example: 19980904 11:37:58

    Format (n = 4):
    yyyymmdd hh:mm:ss.ssss
    Example:
    19980904 11:37:58.1234

    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
    Example: 11:37:5808:00

    Format (n = 4): hh:mm:ss.ssss {±} hh:mm
    Example: 11:37:58.123408:00

    TIMESTAMP WITH TIME ZONE

    TIMESTAMP (n) WITH TIME ZONE

    n = number of digits after decimal point

    Valid values: 06

    Default = 6

    CHAR(25 + n + (1 if n > 0, otherwise 0))

    Format (n = 0):
    yyyymmdd hh:mm:ss{±}hh:mm
    Example:
    19980924 11:37:58+07:00

    Format (n = 4):
    yyyymmdd hh:mm:ss.ssss{±} hh:mm
    Example:
    19980924 11:37:58.1234+07:00

    INTERVAL YEAR

    INTERVAL YEAR (n)

    n = number of digits

    Valid values: 14

    Default = 2

    CHAR(n)

    Format (n = 2): yy
    Example: 98

    Format: (n = 4): yyyy
    Example: 1998

    INTERVAL YEAR TO MONTH

    INTERVAL YEAR (n) TO MONTH

    n = number of digits

    Valid values: 14

    Default = 2

    CHAR(n + 3)

    Format (n = 2): yymm
    Example: 9812

    Format: (n = 4): yyyymm
    Example: 199812

    INTERVAL MONTH

    INTERVAL MONTH (n)

    n = number of digits

    Valid values: 14

    Default = 2

    CHAR(n)

    Format (n = 2): mm
    Example: 12

    Format: (n = 4): mmmm
    Example: 0012

    INTERVAL DAY

    INTERVAL DAY (n)

    n = number of digits

    Valid values: 14

    Default = 2

    CHAR(n)

    Format (n = 2): dd
    Example: 31

    Format: (n = 4): dddd
    Example: 0031

    INTERVAL DAY TO HOUR

    INTERVAL DAY (n) TO HOUR

    n = number of digits

    Valid values: 14

    Default = 2

    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

    n = number of digits

    Valid values: 14

    Default = 2

    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)

    n = number of digits

    Valid values: 14

    Default = 2

    m = number of digits after decimal point

    Valid values: 06

    Default = 6

    CHAR(n + 9 + m + (1 if m > 0, otherwise 0))

    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 HOUR

    INTERVAL HOUR (n)

    n = number of digits

    Valid values: 14

    Default = 2

    CHAR(n)

    Format: (n = 2): hh
    Example: 12

    Format: (n = 4): hhhh
    Example: 0012

    INTERVAL HOUR TO MINUTE

    INTERVAL HOUR (n) TO MINUTE

    n = number of digits

    Valid values: 14

    Default = 2

    CHAR(n + 3)

    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)

    n = number of digits

    Valid values: 14

    Default = 2

    m = number of digits after the decimal point

    Valid values: 06

    Default = 6

    CHAR(n + 6 + m + (1 if m > 0, otherwise 0))

    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)

    n = number of digits

    Valid values: 14

    Default = 2

    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)

    n = number of digits

    Valid values: 14

    Default = 2

    m = number of digits after decimal point

    Valid values: 06

    Default = 6

    CHAR(n + 3 + m + (1 if m > 0, otherwise 0))

    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)

    n = number of digits

    Valid values: 14

    Default = 2

    m = number of digits after decimal point

    Valid values: 06

    Default = 6

    CHAR(n + m + (1 if m > 0, otherwise 0))

    Format (n = 2, m = 0): ss
    Example: 59

    Format: (n = 4, m = 4): ssss.ssss
    Example: 0059.1234