min_dec_div_3 - Decimal division scale to 3 configuration parameter

This parameter is deprecated starting with Db2® Version 11.1.3.3 and replaced by the dec_arithmetic configuration parameter. Starting with Version 11.1.3.3, the min_dec_div_3 configuration parameter is ignored if the dec_arithmetic configuration parameter is set to a non-default value. Setting dec_arithmetic to "DEC.3" results in the same behavior as enabling the min_dec_div_3 configuration parameter.
Note: The following information applies only to data servers and clients Version 11.1.2.2 and earlier.

This parameter is provided as a quick way to enable a change to computation of the scale for decimal division in SQL.

Configuration type
Database
Parameter type
Configurable
Default [range]
No [Yes, No ]

The min_dec_div_3 database configuration parameter changes the resulting scale of a decimal arithmetic operation involving division. It can be set to "Yes" or "No". The default value for min_dec_div_3 is "No". If the value is "No", the scale is calculated as 31-p+s-s'. If set to "Yes", the scale is calculated as MAX(3, 31-p+s-s'). This causes the result of decimal division to always have a scale of at least 3. Precision is always 31.

Effects of changing the value of min_dec_div_3

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 min_dec_div_3 configuration parameter on a database server with existing databases.
  • A static package will not change behavior until the package is rebound, either implicitly or explicitly. For example, after changing the value from NO to YES, the additional scale digits might not be included in the results until rebind occurs. After changing the value of min_dec_div_3, recompile all static SQL packages whose results are effected by the change to force a rebind. 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 min_dec_div_3 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 min_dec_div_3 value has no effect on data that has already been 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
    To force checking of such a constraint, follow these steps:
    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 min_dec_div_3, recompile all static SQL packages that depend on the value of a generated column whose results are effected by the change in the min_dec_div_3 value. To find out which static SQL packages are effected, you must compile, rebind all the packages by running the db2rbind command.
  • An index with expression-based keys whose calculation is dependent on min_dec_div_3 might be different for identical rows. The difference in values occurs if one row was inserted before the change to min_dec_div_3 and the other was inserted after. Drop and recreate all potentially impacted expression-based indexes after you change the value of the min_dec_div_3 configuration parameter. If you are unsure that a particular expression-based index is impacted, drop and recreate the index to avoid incorrect values in the index.
Note: min_dec_div_3 also has the following limitations:
  1. The command GET DB CFG FOR DBNAME will not display the min_dec_div_3 setting. The best way to determine the current setting is to observe the side-effect of a decimal division result. For example, consider the following statement: 
    VALUES (DEC(1,31,0)/DEC(1,31,5))
    If this statement returns sqlcode SQL0419N, the database does not have min_dec_div_3 support, or it is set to "No". If the statement returns 1.000, min_dec_div_3 is set to "Yes".
  2. min_dec_div_3 does not appear in the list of configuration keywords when you run the following command: ? UPDATE DB CFG