DB2 10.5 for Linux, UNIX, and Windows

dec_to_char_fmt - Decimal to character function configuration parameter

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.