dec_arithmetic - DECIMAL arithmetic mode configuration parameter

This parameter specifies the DECIMAL arithmetic mode. This mode affects the rules for result precision and scale of basic DECIMAL operators (+, -, *, /) and the AVG and SUM aggregate functions with a DECIMAL argument.

Configuration type
Database
Parameter type
Configurable
Default [range]
NULL [DEC.S, DEC15, 15, DEC31, 31, D15.S, D31.S where S is a digit in the range 1 - 9]

The dec_arithmetic database configuration parameter can be used to change the resulting scale of a decimal arithmetic operation that involves division. Imposing a minimum division scale can be used to avoid SQLSTATE 42911.

This parameter can also be used to enable Db2® for z/OS® DECIMAL arithmetic emulation. See Expressions, AVG aggregate function, and SUM aggregate function for details on the effects of Db2 for z/OS emulation modes.

NULL
Specifies the default Db2 rules with no minimum DECIMAL division scale.
DEC.S or DEC,S
Specifies the default Db2 rules with a minimum DECIMAL division scale, S must be in the range 1 - 9. The separator that is used can be either a period or a comma, regardless of the setting of the default decimal point.
DEC15 or 15
Db2 for z/OS compatibility mode, equivalent to Db2 for z/OS DECARTH DECP value DEC15 or 15. Specifies the rules that do not allow a precision that is greater than 15 digits.
DEC31 or 31
Db2 for z/OS compatibility mode, equivalent to Db2 for z/OS DECARTH DECP value DEC31 or 31.
D15.S or D15,S
Db2 for z/OS DEC15 compatibility mode with a minimum DECIMAL division scale, S must be in the range 1 - 9. The separator that is used can be either a period or a comma, regardless of the setting of the default decimal point.
D31.S or D31,S
Db2 for z/OS DEC31 compatibility mode with a minimum DECIMAL division scale, S must be in the range 1 - 9. The separator that is used can be either a period or a comma, regardless of the setting of the default decimal point.

Effects of changing the value of dec_arithmetic

Changing this database configuration parameter might cause changes to applications for existing databases. This behavior can occur when the resulting scale for decimal division would be impacted by changing this database configuration parameter. The following list shows some possible scenarios that might affect applications. Consider these scenarios before you change the dec_arithmetic configuration parameter on a database server with existing databases:
  • A static package does not change behavior until the package is rebound, either implicitly or explicitly. For example, after you change the value from DEC.3 to DEC.6, the additional scale digits might not be included in the results until rebind occurs. After you change the value of dec_arithmetic, recompile all static SQL packages whose results are effected by the change to force a rebind operation. You can force an explicit rebind by running the REBIND command or the db2rbind command.
  • Materialized query tables (MQTs) might contain different results after you alter the dec_arithmetic configuration parameter. To ensure that previously created MQTs contain only data that adheres to the new format, refresh these MQTs by using the REFRESH TABLE statement.
  • The results of a trigger might be affected by the changed format. Altering the dec_arithmetic value has no effect on data that is already written.
  • A check constraint that involves decimal division might restrict some values that were previously accepted. Such rows now violate the constraint but are not detected until one of the following events occurs:
    • One of the columns that are involved in the check constraint row is updated.
    • The SET INTEGRITY statement with the IMMEDIATE CHECKED option is processed.
    The following steps force the check of such constraints:
    1. Run the ALTER TABLE statement to drop the check constraint.
    2. Run the ALTER TABLE statement to add the constraint.
  • After you change the value of dec_arithmetic, recompile all static SQL packages that depend on the value of a generated column whose results are effected by the change in the dec_arithmetic value. You can rebind all the packages by running the db2rbind command.
  • An index with expression-based keys whose calculation depends on dec_arithmetic might be different for identical rows. The difference in values occurs if one row was inserted before the change to dec_arithmetic and the other was inserted after. Drop and re-create all potentially impacted expression-based indexes after you change the value of the dec_arithmetic configuration parameter. If you are unsure that a particular expression-based index is impacted, drop and re-create the index to avoid incorrect values in the index.