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.
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 |
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' |
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).
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)) |
Those formulas can look complex, so let's boil it down, ignoring p for the moment:
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.(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.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 |
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:
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).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.
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)) |
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:
SET DECIMALDIGITS
command can be used.DECIMALDIGITS
option on the BEGIN EXPORT
command can be used.MaxDecimalDigits = q
attribute can be used.Additional information on the DECIMAL data type, including internal and external representation, may be found in:
Additional information on the data types of expression results, as well as data type conversions, may be found in:
Additional information on MaxDecimal and the DBSControl utility may be found in:
Additional information on access to Maximum DECIMAL Precision via CLIv2 may be found in:
Additional information on the Teradata utilities that support Maximum DECIMAL Precision may be found in: