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:- Run the ALTER TABLE statement to drop the check constraint
- 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:
- 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".
- min_dec_div_3 does not appear in the list
of configuration keywords when you run the following command: ?
UPDATE DB CFG