Troubleshooting
Problem
leading zeros in character string returned by CHAR(decimal-expresion) scalar function and the CAST specification from decimal to character.
Symptom
In DB2 LUW V9.5, decimal to character string conversion using CHAR(decimal-expression) scalar function would result in a string with leading zeros. However, from V9.7, this behavior was changed at server to be compatible with SQL standard casting rules and hence there will not be any more leading zeros that one would see for the cast results.
Cause
Server behavior changed in db2 V9.7 and later versions for decimal to char conversion when using char(decimal-expression) scalar function
Diagnosing The Problem
After decimal to char conversion, one can observe the string retruned bychar(decimal-expression) for leading zeros
Resolving The Problem
To support backward compatibility to the applications, a new db cfg parameter 'dec_to_char_fmt' is introduced which is set to value 'V95' during database upgrade from V9.5 to V9.7 so that the char(decimal-expression) scalar function returns the same character string format as in previous releases.
For new databases created in DB2 Version 9.7 or later versions, dec_to_char_fmt is set to 'NEW' by default. If dec_to_char_fmt is set to NEW, the CHAR(decimal-expression) scalar function returns a fixed-length character string representation of a decimal number without leading zeros and without a decimal separator when the decimal part is zero.
example:
db2 "create table USER_TB(USER_NAME varchar(8), USER_ID varchar(10), DEPARTMENT varchar(2))";
db2 "CREATE PROCEDURE TEST_PROC(IN IN_USER_ID VARCHAR(10), IN IN_DEPARTMENT VARCHAR(2))
BEGIN
-- Declare variables
DECLARE vUserName decimal(8);
set vUserName = 430;
insert into USER_TB(USER_NAME,USER_ID,DEPARTMENT) values (cast(char(vUserName) as varchar(8)),IN_USER_ID,IN_DEPARTMENT);
END;
db2 "call TEST_PROC('430','db')";
on V9.5:
---------------------------------------
db2 "select * from USER_TB"
USER_NAME USER_ID DEPARTMENT
---------- -------- ----------
00000430 430 db
on V9.7 post database upgrade:
---------------------------------------
dec_to_char_fmt is set to 'V95'(by default)
db2 "select * from USER_TB"
USER_NAME USER_ID DEPARTMENT
---------- -------- ----------
00000430 430 db
For a new db created on v9.7, dec_to_char_fmt is set to 'NEW' by default and the result of cast is as below(note that leading zeros are not part of the converted result):
db2 "select * from bgw_test.USER_TB"
USER_NAME USER_ID DEPARTMENT
---------- ---------- ----------
430 430 db
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21963298