SQL Pocket Guide, 3rd Edition

Data Types: Decimal

Decimal data types are rather more consistent across platforms than the datetime types. The following sections describe the more commonly used decimal types.

DB2’s DECFLOAT Type

DB2 9.5 and higher support a new DECFLOAT type that is based on the IEEE 754r standard. DB2 supports two precision choices:

DECFLOAT(16)
DECFLOAT(34)

DECFLOAT(16) gives 16 digits of precision, requiring eight bytes of storage; DECFLOAT(34) gives 34 digits and requires 16 bytes of storage.

The range for DECFLOAT(16) is:

from –9.999999999999999 × 10³⁸⁴

to –1.0 × 10^–383,

and from 1.0 × 10^–383

to 9.999999999999999 × 10³⁸⁴.

The range for DECFLOAT(34) is:

from –9.999999999999999999999999999999999 × 10⁶¹⁴⁴

to –1.0 × 10^–6143,

and from 1.0 × 10^–6143

to 9.999999999999999999999999999999999 × 10⁶¹⁴⁴.

The DECFLOAT type supports five rounding modes:

ROUND_CEILING

Rounds upward, always in a positive direction.

ROUND_FLOOR

Rounds downward, always in a negative direction.

ROUND_HALF_UP

Rounds to the nearest up or down value. Values of 0.5 round upward.

ROUND_HALF_EVEN

Rounds to the nearest value. Values of 0.5 round up or down so as to make the final digit an even digit.

ROUND_DOWN

Rounds toward zero.

You specify the rounding mode at the database level, using the parameter decflt_rounding. You must restart the database for any change to take effect.

DECIMAL/NUMBER Type

All platforms support the use of DECIMAL for storing numeric base-10 data (such as monetary amounts):

DECIMAL
DECIMAL(precision)
DECIMAL(precision, scale)

In Oracle, ...