decflt_rounding - Decimal floating point rounding configuration parameter
This parameter specifies the rounding mode for decimal floating point (DECFLOAT) values. The rounding mode affects decimal floating-point operations in the server, and in LOAD command operations.
- Configuration type
- Database
- Parameter type
- Configurable
- Default [range]
- ROUND_HALF_EVEN [ROUND_CEILING, ROUND_FLOOR, ROUND_HALF_UP, ROUND_DOWN]
Db2® database
systems support five IEEE-compliant decimal floating point rounding modes. The rounding mode
specifies how to round the result of a calculation when the result exceeds the precision. The
definitions for all the rounding modes are as follows:
- ROUND_CEILING
- Round towards +infinity. If all of the discarded digits are zero or if the sign is negative the result is unchanged. Otherwise, the result coefficient is incremented by 1 (rounded up).
- ROUND_FLOOR
- Round towards -infinity. If all of the discarded digits are zero or if the sign is positive the result is unchanged. Otherwise, the sign is negative and the result coefficient should be incremented by 1.
- ROUND_HALF_UP
- Round to nearest digit or, if equidistant, round up by 1. If the discarded digits represent a value that is greater than or equal to 0.5 in the next left position, then the result coefficient is incremented by 1 (rounded up). Otherwise, the discarded digits that have a value that is less than 0.5 are ignored.
- ROUND_HALF_EVEN
- Round to nearest digit or, if equidistant, round so that the final digit is an even number. If the discarded digits represent a value greater than 0.5 in the next left position, then the resulting coefficient is increment by 1 (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 if its rightmost digit is even, or incremented by 1 (rounded up) if its rightmost digit is odd, to make an even digit. This rounding mode is the default rounding mode as stated in the IEEE decimal floating point specification and is the default rounding mode in Db2 database products.
- ROUND_DOWN
- Round towards 0 (truncation). The discarded digits are ignored.
Table 1 shows the result
of rounding of 12.341, 12.345, 12.349, 12.355, and -12.345, each to
4 digits, under different rounding modes:
| Rounding mode | 12.341 | 12.345 | 12.349 | 12.355 | -12.345 |
|---|---|---|---|---|---|
| ROUND_DOWN | 12.34 | 12.34 | 12.34 | 12.35 | -12.34 |
| 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_FLOOR | 12.34 | 12.34 | 12.34 | 12.35 | -12.35 |
| ROUND_CEILING | 12.35 | 12.35 | 12.35 | 12.36 | -12.34 |
Effects of changing the value of decflt_rounding
Changing the value of the parameter has the following consequences:
- Previously constructed materialized query tables (MQTs) could contain results that differ from what would be produced with the new rounding mode. To correct this problem, refresh potentially impacted MQTs.
- The results of a trigger might be affected by the new rounding mode. Changing it has no effect on data that has already been written.
- Constraints that allowed data to be inserted into a table, if reevaluated, might reject that same data. Similarly constraints that did not allow data to be inserted into a table, if reevaluated, might accept that same data. Use the SET INTEGRITY statement to check for and correct such problems.
- The value of a generated column or an index with expression-based keys whose calculation is dependent on decflt_rounding might be different for identical rows. The difference in values occurs if one row was inserted before the change to decflt_rounding and the other was inserted after. Drop and recreate all potentially impacted expression-based indexes after changing the value of the decflt_rounding configuration parameter. If you are not sure that a particular expression-based index is impacted, it is best to drop and recreate the index to avoid incorrect values in the index.
- The value of decflt_rounding is not compiled into sections. Therefore, you do not have to recompile static SQL statements after changing the value of the decflt_rounding configuration parameter.
The value of this configuration parameter is not changed dynamically. The changes become effective only after all applications disconnect from the database and the database is restarted.