DB2 10.5 for Linux, UNIX, and Windows

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
See Effects of changing the value of decflt_rounding.
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:

Table 1. Decimal floating point 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.