Introduction
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 datatype semantics across languages, platforms, and vendors?
For help with all of these problems, DB2 for Linux, UNIX, and Windows offers DECFLOAT, a new decimal floatingpoint 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™ processorbased 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 processorbased 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)
 SQL
 XML (precisionDecimal type in XML schema 1.1 Part 2: Data types)
For more uptodate information on one of these languages, refer to the General Decimal Arithmetic page, which includes a great deal of decimal floatingpoint information, including the status of various specifications, products, languages, and a downloadable software implementation for operations on the IEEE754r decimal floatingpoint types.
DECFLOAT considerations
Whether you are an independent software vendor (ISV), bank, or an application developer, the DECFLOAT data type can benefit you in several ways.
DECFLOAT for the ISV
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 floatingpoint arithmetic standard so the application can seamlessly interact with different databases that implement the standard.
In addition, mathintensive workloads run faster on a hardwarebased decimal floatingpoint implementation.
DECFLOAT for the business application
There is a pain point for business applications — decimal math in software slows down mathintensive 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 hardwarebased 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.
DECFLOAT for the application developer
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 floatingpoint 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 floatingpoint data type to eliminate rounding differences and other specific datatype issues.
Alter existing columns
The most common ALTER operation involves business applications that need to be moved from DECIMAL to DECFLOAT, an industrystandard 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 storagespace 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
Precision
in digits 
Length
in bytes  Range 

16  8 
9.999999999999999 x 10^{384 }to 1.0 x 10^{383 }: 1.0 x 10^{383 }to 9.999999999999999 x 10^{384} 
34  16 
9.999999999999999999999999999999999 x 10^{6144 }to 1.0 x 10^{6143 }: 1.0 x 10^{6143 }to 9.999999999999999999999999999999999 x 10^{6144} 
Rounding modes
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
Rounding mode  12.341  12.345  12.349  12.355  12.345 

ROUND_CEILING  12.35  12.35  12.35  12.36  12.34 
ROUND_FLOOR  12.34  12.34  12.34  12.35  12.35 
ROUND_HALF_UP  12.34  12.35  12.35  12.36  12.35 
ROUND_HALF_EVEN  12.34  12.34  12.35  12.36  12.34 
ROUND_DOWN  12.34  12.34  12.34  12.35  12.34 
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;
Comparison semantics
Trailing zeros in decimal floatingpoint 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
semantics.
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() builtin 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 arithmeticallyequal 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.
Builtin functions
The following new builtin 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. Builtin 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
Client support for DECFLOAT
The common client's CLI, Microsoft .NET and JDBC, all support the DECFLOAT data type. In addition, commandline 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.
I wonder why?
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() builtin 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.
Comparison with DECIMAL and DOUBLE
Table 3 offers a comparison between the DECFLOAT and DECIMAL data types.
Table 3. DECFLOAT and DECIMAL comparison
Attribute  DECIMAL  DECFLOAT 

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 floatingpoint 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 
Rounding support 
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
Attribute  DOUBLE  DECFLOAT 

Accuracy for business math  DOUBLE is a binary floatingpoint data type that approximates decimal numbers; ideal for scientific applications that can tolerate approximations  DECFLOAT is ideal for businessmath 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 datatype 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 
Summary
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:
 ROUND_CEILING
 ROUND_FLOOR
 ROUND_HALF_UP
 ROUND_HALF_EVEN
 ROUND_DOWN
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.
Resources
Learn
 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 floatingpoint information, including the status of various specifications, products, languages, and a downloadable software implementation for operations on the IEEE754r decimal floatingpoint types.
 System p on IBM PartnerWorld®: Discover benefits, resources, and support for IBM Business Partners.
 AIX on IBM PartnerWorld: Find out about IBM AIX, the premier UNIX operating system from IBM that runs IBM POWER processorbased servers.
 IBM Publications Center: View, download, or order publications, many are free of charge.
 IBM Redbooks®: Locate IBM Redbooks as well as additional helpful material.
 developerWorks Information Management zone: Learn more about DB2. Find technical documentation, howto articles, education, downloads, product information, and more.
 Stay current with developerWorks technical events and webcasts.
Get products and technologies
 DB2 ExpressC: Now you can use DB2 for free. Download DB2 ExpressC, 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 deploy application.
 Build your next development project with IBM trial software, available for download directly from developerWorks.
Discuss
 Participate in developerWorks blogs and get involved in the developerWorks community.
Comments
Dig deeper into Information management on developerWorks
 Overview
 New to Information management
 Technical library (tutorials and more)
 Forums
 Community
 Downloads
 Products
 Events

Bluemix Developers Community
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.

developerWorks Weekly Newsletter
Keep up with the best and latest technical info to help you tackle your development challenges.

DevOps Services
Software development in the cloud. Register today to create a project.

IBM evaluation software
Evaluate IBM software and solutions, and transform challenges into opportunities.