This parameter is used to control the result of the CHAR
scalar function and the CAST specification for converting decimal
to character values.
- Configuration type
- Database
- Parameter type
- Configurable online
- Configurable by member in a DB2® pureScale® environment
See Effects of changing the value of dec_to_char_fmt.
- Default [range]
- NEW [NEW, V95]
The setting of the parameter determines whether
leading zeros and a trailing decimal characters are included in the
result of the CHAR function. If you set the parameter to NEW,
leading zeros and a trailing decimal characters are not included;
if you set the parameter to V95, leading zeros and
a trailing decimal characters are included.
Leading zeros and
a trailing decimal characters are also included in the result of the
CHAR_OLD scalar function, which has the same syntax as the CHAR function.
When
upgrading, for databases created before Version 9.7 and then upgraded
to Version 9.7 or higher, the parameter dec_to_char_fmt is
set to V95 by default.
Effects of changing the value of dec_to_char_fmt
- Materialized query tables (MQTs) that you created before Version
9.7 might contain results that differ from those MQTs that you created
by using the NEW setting. 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.
Setting the value of the parameter to NEW to change
the format has no effect on data that has already been written.
- Constraints that allowed data to be inserted into a table might,
if reevaluated, reject that same data. Similarly, constraints that
did not allow data to be inserted into a table might, if reevaluated,
accept that same data. Use the SET INTEGRITY statement to check for
and correct data in a table that might no longer satisfy a constraint.
- After changing the value of dec_to_char_fmt,
recompile all static SQL packages that depend on the value of a generated
column whose results are effected by the change in the dec_to_char_fmt value.
To find out which static SQL packages are effected, you must compile,
rebind all the packages using the db2rbind command.
- The
value of an index with expression-based keys whose calculation is
dependent on dec_to_char_fmt will be different
after changing the value of dec_to_char_fmt.
Drop and recreate all potentially impacted expression-based indexes
after changing the value of dec_to_char_fmt.
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.