17.00 - Decimal Data Type 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

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 Teradata Vantage™ - 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 the database.

Example 1

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 2

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 DataTime 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.

The following table shows how to use ANSI/SQL DateTime specifications.

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

yyyy-mm-dd hh:mm:ss

1998-09-04 11:37:58

yyyy-mm-dd hh:mm:ss.ssss

1998-09-04 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))

hh:mm:ss{±}hh:mm

11:37:58-08:00

hh:mm:ss.ssss{±}hh:mm

11:37:58.1234-08:00
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))

yyyy-mm-dd hh:mm:ss{±}hh:mm

1998-09-24 11:37:58+07:00

yyyy-mm-dd hh:mm:ss.ssss{±}hh:mm

1998-09-24 11:37:58.1234+07:00
INTERVAL YEAR

INTERVAL YEAR (n)

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n+1)

yy

98

yyyy

1998
INTERVAL YEAR TO MONTH

INTERVAL YEAR (n) TO MONTH

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n+ 3)

yy-mm

98-12

yyyy-mm

1998-12
INTERVAL MONTH

INTERVAL MONTH (n)

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n)

mm

12

mmmm

0012
INTERVAL DAY

INTERVAL DAY (n)

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n)

dd

31

dddd

0031
INTERVAL DAY TO HOUR

INTERVAL DAY (n) TO HOUR

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n + 3)

dd hh

31 12

dddd hh

0031 12
INTERVAL DAY TO MINUTE

INTERVAL DAY (n) TO MINUTE

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n + 6)

dd hh:mm

31 12:59

dddd hh:mm

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: 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, 0 otherwise))

dd hh:mm:ss

31 12:59:59

dddd hh:mm:ss

0031 12:59:59:59.1234
INTERVAL HOUR

INTERVAL HOUR (n)

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n)

hh

12

hhhh0012

INTERVAL HOUR TO MINUTE

INTERVAL HOUR (n) TO MINUTE

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n + 3)

hh:mm

12:59

hhhh:mm

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: 1-4

Default = 2

m = number of digits after decimal point

Valid values: 0-6

Default = 6

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

hh:mm:ss

12:59:59

hhhh:mm:ss.ssss

0012:59:59.1234
INTERVAL MINUTE

INTERVAL MINUTE (n)

n = number of digits

Valid values: 1-4

Default = 2

CHAR(n)

mm

59

mnnm

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: 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, 0 otherwise))

mm:ss

59:59

mm:ss.ssss

0059:59.1234
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, 0 otherwise))

ss

59

ssss.ssss

0059.1234