Are you sure that your business never loses money because of rounding errors? Are you sure that your business application produces accurate results? Have you ever wanted an application to crunch numbers faster? Are your application development costs out of control because of incompatibilities between data-type semantics across languages, platforms, and vendors?
For help with all of these problems, DB2 for Linux, UNIX, and Windows offers DECFLOAT, a new decimal floating-point data type based on IEEE754r. DECFLOAT provides the highest precision and range for decimal arithmetic in DB2 and supports five rounding modes. DECFLOAT delivers greater performance by leveraging hardware support on IBM POWER6™ processor-based systems. With native hardware and language support, all arithmetic, conversion, and rounding operations are performed in decimal floating point. By exploiting the new hardware and language support for this standard data type, applications that perform business math can gain significant performance. Because decimal floating point is an emerging standard across databases, languages, and hardware, application development costs are significantly reduced.
DECFLOAT leverages hardware support on POWER6 processor-based systems and relies on software emulation for other platforms. Various specifications are evolving that are founded on the IEEE754r specification. Here are some of these specifications:
- C (ISO/IEC JTC1/SC22/WG14 — C programming language)
- C++ (ISO/IEC JTC1/SC22/WG21 — C++ programming language)
- JAVA (Java™ Specification Request 13 Decimal Arithmetic Enhancement for Java)
- XML (precisionDecimal type in XML schema 1.1 Part 2: Data types)
For more up-to-date information on one of these languages, refer to the General Decimal Arithmetic page, which includes a great deal of decimal floating-point information, including the status of various specifications, products, languages, and a downloadable software implementation for operations on the IEEE754r decimal floating-point types.
Whether you are an independent software vendor (ISV), bank, or an application developer, the DECFLOAT data type can benefit you in several ways.
There is a pain point for ISVs — they have to write complex logic to work with proprietary decimal data types that are offered by different database vendors. This results in high development costs.
To mitigate these high development costs, ISVs can develop code that is based on the IEEE754r floating-point arithmetic standard so the application can seamlessly interact with different databases that implement the standard.
In addition, math-intensive workloads run faster on a hardware-based decimal floating-point implementation.
There is a pain point for business applications — decimal math in software slows down math-intensive computations and impacts business.
Business applications (particularly those that relate to stock exchange and banking processes) can benefit from the improved performance offered by the hardware-based DECFLOAT data type.
In business applications, DB2 now offers the flexibility to choose a rounding mode for the database. For example, your tax and interest computations can occur inside the database.
There is a pain point for application developers — C and C++ do not have native support for the decimal data type. To make things worse, each relational database management system (RDBMS) vendor provides a proprietary DECIMAL data type implementation that offers different semantics than the DB2 DECIMAL data type. This leads to high application development costs that are associated with resolving these varying semantics.
Therefore, it is important that the decimal floating-point data type is being specified by different programming languages, especially for the C, C++ and, Java languages. Complex application environments that are written in different programming languages see decimal floating point as THE numeric data type.
A unified view of the data type across vendors, platforms, and languages keeps development costs lower by not requiring the application to cope with the variances and nuances of each implementation.
Imagine a Java application that runs on top of Call Level Interface (CLI) or Java database connectivity (JDBC), which in turn talks to DB2 for IBM z/OS® and DB2 for Linux, UNIX, and Windows, with the latter running on a POWER6 processor (see Figure 1).
Figure 1. Java application running on top of CLI or JDBC
All the layers in this example can use the same standard decimal floating-point data type to eliminate rounding differences and other specific data-type issues.
The most common ALTER operation involves business applications that need to be moved from DECIMAL to DECFLOAT, an industry-standard data type that provides great performance as a result of its hardware support.
Which DECFLOAT level of precision should you ALTER to?
The two supported values for specifying DECFLOAT precision are DECFLOAT(16) and DECFLOAT(34), which corresponds, respectively, to 16 and 34 digits of precision, with a storage requirement of 8 and 16 bytes, respectively. In determining which DECFLOAT to migrate to, you should consider the precision requirements, weighed against the storage-space needs.
Hint: If your application does a lot of aggregation operations, such as SUM or AVG, then DECFLOAT(34) might yield better performance than DECFLOAT(16). The reason for this is that aggregate operations on DECFLOAT(16) promote each input value to DECFLOAT(34) and return a DECFLOAT(34) results (see Example 1):
Example 1. Altering salary column from DECIMAL to DECFLOAT(34)
The employee salary table has a column of type DECIMAL(18, 2) employee (id INT, salary DECIMAL(18,2)) ALTER TABLE employee ALTER COLUMN salary SET DATA TYPE DECFLOAT(34)
Table 1 shows the range for DECFLOAT values:
Table 1. Range for DECFLOAT values
-9.999999999999999 x 10384 to -1.0 x 10-383 :|
1.0 x 10-383 to 9.999999999999999 x 10384
-9.999999999999999999999999999999999 x 106144 to -1.0 x 10-6143 :|
1.0 x 10-6143 to
9.999999999999999999999999999999999 x 106144
Rounding happens when the resulting precision of an expression is wider than the supported precision. DECFLOAT supports all the rounding modes that most applications need. These modes are fully compatible with the IEEE standard and, thus, are supported on most hardware implementations as well.
The following definitions relate to the supported rounding modes (see Table 2 for an example of these rounding modes):
- ROUND_CEILING: Round towards +infinity. If all discarded digits are zero or the sign is negative, there is no change to the result. Otherwise, the result coefficient increments by one (rounded up).
- ROUND_FLOOR: Round towards âinfinity. If all discarded digits are zero or the sign is positive, there is no change to the result. Otherwise, the sign is negative and the result coefficient increments by one.
- ROUND_HALF_UP: Round to nearest. If equidistant, round up one. If the discarded digits represent greater than, or equal to, half (0.5) the value of one in the next left position, the result coefficient increments by one (rounded up). Otherwise, the discarded digits are ignored.
- ROUND_HALF_EVEN: Round to nearest. If equidistant, round so that the final digit is even. If the discarded digits represent greater than half (0.5) the value of one in the next left position, then the result coefficient increments by one (rounded up). If they represent less than half, then the result coefficient is not adjusted (that is, the discarded digits are ignored). Otherwise, if they represent exactly half, the result coefficient is unaltered when its rightmost digit is even and increments by one (rounded up) when its rightmost digit is odd (to make an even digit). This rounding mode is also referred to as banker's rounding and gives the perception of fairness in rounding. This is the default in DB2, in compliance with the IEEE specification.
- ROUND_DOWN: Round toward zero (truncation). The discarded digits are ignored.
Table 2. Rounding modes
The rounding mode can be modified by using the database configuration parameter, decflt_rounding. For the change to take effect, you must deactivate the database (see Example 2).
Example 2. Modifying the database configuration parameter
The US tax system uses the rounding mode of ROUND_HALF_UP. An application computing taxes would change rounding mode from ROUND_HALF_EVEN (the default) to ROUND_HALF_UP as under: db2 connect to taxdb; db2 update db cfg using decflt_rounding ROUND_HALF_UP; db2 connect reset; db2 deactivate db taxdb; -- new connections from this point on use ROUND_HALF_UP as the rounding mode db2 connect to taxdb;
Trailing zeros in decimal floating-point values are preserved. For instance, 1.0 and 1.00 are two distinct representations. This yields two comparison semantics for the DECFLOAT data type, as shown below.
Numeric value compare: Trailing zeros are ignored in comparisons. For example, 1.0
equals 1.00. By default, this comparison is used for index, sort, table partitioning,
predicate evaluation, and other functions — in short, every place where
comparison is done implicitly or in predicates (see Example 3).
Example 3. Numeric value compare
create table stockPrice (stock DECFLOAT(16)); insert into stockPrice values (4.2), (4.2000), (4.6125), (4.20); select * from stockPrice where stock = 4.2; Returns three tuples 4.2, 4.2000, 4.20 select * from stockPrice where stock > 4.20; Returns the single tuple 4.6125 select * from stockPrice order by stock; Returns all the tuples, 4.2, 4.2000, 4.20, 4.6125 with the first 3 tuples returned in an undefined order
TotalOrder compare: Trailing zeros are significant in comparison. For example, 1.0
> 1.00. Every DECFLOAT value has an order within TotalOrder comparison
As per TotalOrder semantics, the order of different values goes as shown in the following example:
-NAN < -SNAN < -INFINITY < -0.10 < -0.100 < -0 < 0 < 0.100 < 0.10 < INFINITY < SNAN < NAN
Note that negative zero compares less than positive zero with TotalOrder compare.
It is possible to request the TotalOrder compare by using the TOTALORDER() built-in function in predicates (see Example 4).
Example 4. TotalOrder Compare
With stock prices, it might be important to know the precision of the data. For instance, if stocks are usually specified at five digits of precision and a stock is $4.2; it is unclear whether the price is $4.2000 or $4.2999 or somewhere between these two values. create table stockPrice (stock DECFLOAT(16)); insert into stockPrice values (4.2), (4.2000), (4.6125), (4.20); select * from stockPrice where TOTALORDER(stock, 4.2000) = 0; Returns the tuple 4.2000 only select * from stockPrice where TOTALORDER(stock, 4.20) = 1; Returns two tuples 4.2 and 4.6125 that are greater than 4.20
The order in which arithmetically-equal values that have different trailing zeros are returned is unspecified. Thus, ORDER BY with DECFLOAT column values 1.0 and 1.00 return the two tuples in any order, which indicates that the two values are equal. Similarly, DISTINCT returns either 1.0 or 1.00.
The following new built-in functions are added to support the DECFLOAT data type:
- TOTALORDER(expr1, expr2): Compares expr1 with expr2 as per the IEEE specificationâs TotalOrder semantics and returns a SMALLINT value of -1, 0, or 1 that indicates whether expr1 is less than, equal to, or greater than expr2.
- NORMALIZE_DECFLOAT (expr): Normalizes the input argument expr by removing redundant trailing zeros.
- QUANTIZE(expr1, expr2): The QUANTIZE function returns a DECFLOAT value that is equal in value (except for any rounding) and sign to expr1 and that has an exponent that is set equal to the exponent of expr2. You can use the QUANTIZE function to implement rounding to the desired digits of precision, for example, round to the nearest cent by using the DECFLOAT rounding mode, as demonstrated in Example 5.
Example 5. Built-in functions usage
The items table has the following values for itemPrice column: 8.230, 13.000, 13 and 8.235 Assume that the database rounding mode (decflt_rounding) is set to ROUND_HALF_EVEN 1. a) Return all tuples that have the itemPrice exactly 13.000 SELECT * FROM items WHERE TOTALORDER(itemPrice, 13.000) = 0; Returns 13.000 b) Return all tuples that have the itemPrice 13 dollars SELECT * FROM items WHERE itemPrice = 13; Returns 13, 13.000 2. Trim all the trailing zeros from itemPrice SELECT NORMALIZE_DECFLOAT(itemPrice) FROM items; Returns 8.23, 13, 13, 8.235 3. Round itemPrice to two digits after decimal point (nearest cent) SELECT QUANTIZE(itemPrice, 1.00) FROM items; Returns 8.23, 13.00, 13.00, 8.24
The common client's CLI, Microsoft .NET and JDBC, all support the DECFLOAT data type. In addition, command-line processor (CLP) also supports DECFLOAT.
Applications can synchronize the rounding mode between the client and server as follows:
- Get the server rounding mode by querying the CURRENT DECFLOAT ROUNDING MODE special register.
- Set the same rounding mode on the client, for example, the CLI attribute sql_attr_decfloat_rounding_mode.
This ensures that rounding modes are the same on the client and server. An attempt to set the server rounding mode using the SET CURRENT DECFLOAT ROUNDING MODE statement to a different value from the one specified by the database configuration parameter, decflt_rounding, returns an error. Thus, the SET CURRENT DECFLOAT ROUNDING MODE statement is supported so that you can verify that the rounding mode is the same across the client and server.
This section discusses some observations about DECFLOAT that might not be very obvious. It also includes some things that initially puzzled IBM testers.
Question: I enter the value -0 (negative zero) into a DECFLOAT column, but the negative sign is being ignored and, instead, the value 0 (positive zero) is inserted, why?
Answer: Literal handling in DB2 treats 0 as an INTEGER and, when the negation operation is applied to it, the result is still 0. To insert -0 in the database, use DECFLOAT('-0') as the expression to be inserted.
Question: I did a computation involving DECFLOAT values that have large magnitudes. I believe the computation overflowed, but my application kept going with a warning. Why?
Answer: As per IEEE754r, an arithmetic exception that is raised during a DECFLOAT operation still returns a value. In SQL, this translates to returning a warning sqlcode along with a special value result, as specified by the IEEE specification.
Question: Does the ROUND() built-in function use the DECFLOAT rounding mode for DECFLOAT inputs?
Answer: No. ROUND supports only ROUND_HALF_UP as the rounding mode, even for DECFLOAT values. Use QUANTIZE() to round as per the DECFLOAT rounding mode.
Question: I tried to change the rounding mode using the SET CURRENT DECFLOAT ROUNDING MODE statement, but it failed. Why?
Answer: The special register CURRENT DECFLOAT ROUNDING MODE gets its value from the database configuration decflt_rounding parameter and might not attempt to set a value that is different from the one specified by decflt_rounding. The statement is supported for DB2 family compatibility, and to allow clients to verify that the rounding mode is the same across the client and server connection.
Question: When I have literals in a scientific notation, they do not seem to be represented exactly. For instance, when I tried to insert 1e6000 into a DECFLOAT(34) column, I got an overflow error. Why?
Answer: The existing literal support in DB2 is unchanged with DECFLOAT. Thus, existing literals are either INTEGER, DECIMAL, or DOUBLE literals. The only exception is in regards to DECFLOAT special values, which are treated as DECFLOAT literals. Although DECIMAL literals naturally promote to DECFLOAT without any loss of precision, DOUBLE literals are first approximated to a binary floating point and are then promoted to decimal floating point. When dealing with values in the scientific notation, use an expression such as DECFLOAT(â1e6000â) to force a string to DECFLOAT cast.
Table 3 offers a comparison between the DECFLOAT and DECIMAL data types.
Table 3. DECFLOAT and DECIMAL comparison
|Standards||Semantics governed by proprietary implementations; high cost of application development in coping with variances||Semantics governed by industry standards (IEEE754r), thus, conforming applications should behave similarly, which leads to lower application development costs|
|Language support||Most C and C++ compilers do not implement packed DECIMAL data types||In addition to many other languages, C and C++ support for decimal floating point is emerging. Currently, XL C/C++ 9.0 and gcc 4.2 implement the decimal floating-point types.|
|Performance||Slower, because all math is performed in software||Faster because of hardware support for decimal floating point (currently on POWER6)|
|Storage space||Between 1 and 16 bytes fixed storage length, depending on column definition||Either 8 bytes or 16 bytes of fixed storage length, depending on column definition|
Limited to ROUND_DOWN |
(or truncation) for the most part
|Choice of five rounding modes that you can set by using the decflt_rounding database configuration parameter|
Table 4 offers a comparison between the DECFLOAT and DOUBLE data types.
Table 4. DECFLOAT and DOUBLE comparison
|Accuracy for business math||DOUBLE is a binary floating-point data type that approximates decimal numbers; ideal for scientific applications that can tolerate approximations||DECFLOAT is ideal for business-math applications that cannot tolerate approximations|
|Magnitude||DOUBLE data type cannot store numbers larger than 1.7e308 (approximately)||DECFLOAT(34) can store very large numbers — up to 9.99e6144 (approximately), which is important for some classes of scientific applications|
|Performance||Faster, because all math operations are done in binary (base 2)||Even with hardware support DECFLOAT is slower, because all math operations are done in decimal (base 10)|
|Rounding support||DOUBLE data-type implementation in DB2 does not support an interface to change the rounding mode and, instead, defaults to the ROUND_HALF_EVEN (hardware default)||Choice of five rounding modes that you can set by the decflt_rounding database configuration parameter|
DECFLOAT is a new data type in DB2 for Linux, UNIX, and Windows that is compliant with the industry standard IEEE754r specification. By leveraging hardware support (currently on POWER6), DECFLOAT provides applications with a performance advantage over DECIMAL. In addition, DECFLOAT is the largest number type, both in magnitude and precision. DECFLOAT supports five rounding modes:
You can set the rounding mode by using the database configuration parameter decflt_rounding.
Language support for decimal floating point is evolving in C, C++, and many other languages. Given that the language specifications are based on the same hardware specification, going forward, most application development for business applications will use decimal floating point to make it the data type of the future.
DB2 for Linux
UNIX and Windows Information Center: Find information describing how to use the DB2
family of products and features.
IBM System p Information
Center: Get technical information about IBM System p and AIX®.
- "General Decimal Arithmetic": Find decimal floating-point information, including the status of various specifications, products, languages, and a downloadable software implementation for operations on the IEEE754r decimal floating-point types.
System p on IBM PartnerWorld®
Discover benefits, resources, and support for IBM Business Partners.
on IBM PartnerWorld: Find out about IBM AIX, the premier UNIX operating system from IBM that runs IBM POWER processor-based servers.
Publications Center: View, download, or order publications, many are free of charge.
: Locate IBM Redbooks as well as additional helpful material.
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
Stay current with developerWorks
technical events and webcasts.
Get products and technologies
Now you can use DB2 for free. Download DB2 Express-C, a free version of
DB2 Express Edition for the community that offers the same core data
features as DB2 Express Edition and provides a solid base to build and
Build your next development project with
trial software, available for download directly from developerWorks.
Participate in developerWorks blogs and get involved in the developerWorks community.
Naresh Chainani is a software developer who has worked on DB2 for Linux, UNIX and, Windows development in IBM Beaverton for the last six years. He started working in DB2 compiler development and, more recently, has been working in the DB2 runtime-engine development area. He has a good understanding of the SQL language and realizes the importance and benefits of complying with industry standards. He was the technical lead for the DECFLOAT project in DB2.