How many digits in a DECIMAL?

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
bwb
Teradata Employee

How many digits in a DECIMAL?

Teradata currently supports up to 38 digits in DECIMAL columns. There are several ways to control how many digits you're working with, and they interact in various ways.

DECIMAL(n,m)

The most obvious way to specify the number of digits desired is with the data type clause, DECIMAL(n,m), where 1<=n<=38 and 0<=m<=n. This is the way columns are defined, and also the way input sent from a client to the Teradata database is defined (for example, via the USING modifier). DECIMAL columns are stored by the Teradata database in 1, 2, 4, 8, or 16 bytes, depending on the value of n:

Number of Digits Number of Bytes
1 to 2 1
3 to 4 2
5 to 9 4
10 to 18 8
19 to 38 16

Bytes used to store DECIMAL columns

The value is expressed as a two's complement scaled binary number, with little-endian byte order.

The external representation on network clients is the same as the internal representation. Byte order is appropriate for the client platform (for example, little-endian on Intel and big-endian on SPARC).

The external representation on mainframe clients (for example, IBM mainframe) is packed decimal. Packed decimal is expressed in sign-magnitude form. The rightmost nibble is the sign (the bolded entries show the preferred representation):

Sign Hexadecimal Values
+ X'A' X'C' X'E' X'F'
- X'B' X'D'

Packed Decimal Sign Values

The other nibbles each contain one decimal digit, represented by X'0' through X'9' (if "n" is even, the leftmost nibble is padded with X'0'). The total size of the packed decimal value is (n+2)/2 bytes, for a maximum of 20 bytes. IBM hardware supports packed decimal operations on up to 16-byte packed decimal values (31 digits).

MaxDecimal

Teradata defines the resulting data type for arithmetic and other operations, based on the data types of the participating operands.

If DECIMAL operand(s) are present in an expression, a DBA-specified value, the DBSControl setting MaxDecimal, becomes important in determining the precision and scale of the expression result. MaxDecimal specifies the maximum precision of the DECIMAL result of an expression. The valid values for MaxDecimal are 0, 15, 18, and 38 (0 is the default and is treated the same as 15).

Let's use arithmetic operations on two DECIMAL operands, which will always generate a DECIMAL result, as an example:


Left Operand Right Operand Operator Result Data Type

 
DECIMAL(n,m) DECIMAL(k,j) +,- DECIMAL(min(p,(1+max(m,j)+max(n-m,k-j))),max(m,j))
    * DECIMAL(min(p,n+k),(m+j))
    /,MOD DECIMAL(p,max(m,j))

Result of Arithmetic Operations on Two DECIMAL Operands

Those formulas can look complex, so let's boil it down, ignoring p for the moment:

  • For addition and subtraction, we need to have enough digits to the right of the decimal point to hold all of the fractional digits of either operand (the rightmost max(m,j)). We also need to have enough digits total to hold the fractional digits (the leftmost max(m,j)) plus the whole digits (max(n-m,k-j)), plus one for a possible carry. For example, adding two DECIMAL(5,2) values will result in a DECIMAL(6,2) value.
  • For multiplication, we need enough total digits to hold the product (n+k) and enough digits to the right of the decimal point to hold all of the fractional digits (m+j). For example, multiplying two DECIMAL(5,2) values will result in a DECIMAL(10,4) value.
  • For division and modulo, we need as many digits as we can get for the quotient (p), retaining as many fractional digits as either operand had (max(m,j)). For example, dividing one DECIMAL(5,2) value by another will result in a DECIMAL(p,2) value.

The intent of the above is to avoid overflows whenever possible, while maintaining maximum precision.

Now, let's reintroduce p:


MaxDecimal n and k p

 
0 or 15 n and k <= 15 15
  (n or k > 15) and (n and k <= 18) 18
  n or k > 18 38
18 n and k <= 18 18
  n or k > 18 38
38 m and k = any value 38

Value of p

Based on the MaxDecimal setting, p essentially limits the total number of digits in the result.

MaxDecimal was introduced when the maximum number of digits allowed in DECIMAL values was increased from 15 to 18. It's purpose (assuming the default setting was used) was to maintain backward compatibility by avoiding a change in the result data type when the operands were no larger than DECIMAL(15). When the maximum number of digits allowed in DECIMAL values was further increased from 18 to 38, MaxDecimal was extended, to achieve the equivalent backward compatibility when the operands were no larger than DECIMAL(18).

Limiting the total number of digits becomes important when client representation is taken into account:

  • On network clients (and in the Teradata database), DECIMAL(15) and DECIMAL(18) both take 8 bytes, while anything larger than DECIMAL(18) takes 16 bytes. A client application must be able to deal with the difference when retrieving DECIMAL(n) data when n > 18. Using MaxDecimal 18 can reduce the immediate need for client application changes (only applications dealing when DECIMAL(n), n > 18, need to be aware of the difference).
  • On mainframe clients, DECIMAL(15) takes 8 bytes, DECIMAL(18) takes 10 bytes, and DECIMAL(38) takes 20 bytes. It was important when the maximum went from DECIMAL(15) to DECIMAL(18) that client applications didn't have to change immediately (as long as the operands involved had no DECIMAL(n), n > 15).

Note that MaxDecimal only affects the result data type of expressions, nothing else. It is possible to load data into the Teradata database with as many as 38 decimal digits, regardless of the MaxDecimal setting. Similarly, it is possible to extract DECIMAL column data (no operations involved) from the Teradata database with as many as 38 decimal digits, regardless of the MaxDecimal setting. Depending on the client application and platform, this can lead to problems, as we'll see next.

Maximum DECIMAL Precision

On a request-by-request basis, a client application can specify the maximum DECIMAL precision it is prepared to accept in results. This setting is independent of the DBSControl MaxDecimal setting, and can take values ranging from 0 to 38 (a value of 0 requests the default, 18 digits). The setting operates as an implicit CAST of DECIMAL results:


Maximum DECIMAL Precision (q) DECIMAL(n,m)

 
q >= n DECIMAL(n,m)
q < n CAST to DECIMAL(q,min(q,m))

Maximum DECIMAL Precision and DECIMAL Results

If an overflow occurs as a result of the implicit CAST, a 2616 error (numeric overflow) is returned.

Mainframe client applications might typically set Maximum DECIMAL Precision to 31, since 31 digits (16 bytes) is the maximum precision supported by the packed decimal hardware operations.

Note that Maximum DECIMAL Precision only affects the data type of DECIMAL(n) results, when n > q. It has no effect on the data type of DECIMAL data sent to the Teradata database. Also, Maximum DECIMAL Precision has no effect on results in field mode, since in field mode the DECIMAL data will have been formatted as a displayable character string using the appropriate FORMAT information.

Maximum DECIMAL Precision settings are available to all CLIv2-based applications. A DBCAREA field accepts the setting, and CLIv2 copies the setting into the Options parcel for communication with the Teradata database.

Some Teradata utilities that export data provide access to the Maximum DECIMAL Precision setting:

  • In BTEQ, the SET DECIMALDIGITS command can be used.
  • In FastExport, the DECIMALDIGITS option on the BEGIN EXPORT command can be used.
  • In Teradata Parallel Transporter (Export and Selector Operators), the MaxDecimalDigits = q attribute can be used.

References

Additional information on the DECIMAL data type, including internal and external representation, may be found in:

  • Teradata Database SQL Data Types and Literals, B035-1143

Additional information on the data types of expression results, as well as data type conversions, may be found in:

  • Teradata Database SQL Functions, Operators, Expressions, and Predicates, B035-1145

Additional information on MaxDecimal and the DBSControl utility may be found in:

  • Teradata Database Utilities - Volume 1 A-K, B035-1102

Additional information on access to Maximum DECIMAL Precision via CLIv2 may be found in:

  • Teradata Call-Level Interface Version 2 Reference for Channel-Attached Systems, B035-2417
  • Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems, B035-2418

Additional information on the Teradata utilities that support Maximum DECIMAL Precision may be found in:

  • Basic Teradata Query Reference, B035-2414
  • Teradata FastExport Reference, B035-2410
  • Teradata Parallel Transporter Reference, B035-2436
  • Teradata Parallel Transporter User Guide, B035-2445
2 REPLIES
N/A

Re: How many digits in a DECIMAL?

Thanks for the post.
A general question -
Can you highlight increase in maximum decimal digits for each Teradata release?
bwb
Teradata Employee

Re: How many digits in a DECIMAL?

You're welcome.

The maximum supported number of decimal digits increased:
* From 15 to 18 in V2R2.0 (roughly, 1997).
* From 18 to 38 in V2R12.0 (roughly, 2007).