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.
Changing
this database configuration parameter might cause changes to applications
for existing databases. This can occur when the resulting scale for
decimal division would be impacted by changing this database configuration
parameter. The following lists some possible scenarios that might
impact applications. These scenarios should be considered before changing
the
min_dec_div_3 on a database server with existing databases.
- If the resulting scale of one of the view columns is changed,
a view that is defined in an environment with one setting could fail
with SQLCODE -344 when referenced after the database configuration
parameter is changed. The message SQL0344N refers to recursive common
table expressions, however, if the object name (first token) is a
view, then you will need to drop the view and create it again to avoid
this error.
- 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. For any changed static
packages, an explicit REBIND command can be used to force a rebind.
- A check constraint involving decimal division might restrict some
values that were previously accepted. Such rows now violate the constraint
but will not be detected until one of the columns involved in the
check constraint row is updated or the SET INTEGRITY statement with
the IMMEDIATE CHECKED option is processed. To force checking of such
a constraint, perform an ALTER TABLE statement in order to drop the
check constraint and then perform an ALTER TABLE statement to add
the constraint again.
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
DB2 Version 9.7 for Linux, UNIX, and Windows