Changes to scalar functions in Db2 11.5

Db2 11.5 contains a number of new and changed scalar functions.

Review the changes to learn which ones might affect your existing applications and to determine whether you have to adjust them.

Changes

Bad locale name may lead to non-deterministic results.

For example, the statement:

VALUES TRUNC(TIMESTAMP('2400-12-23-01.55.55.003456789012', 10), 'DAY',  'asdf 1.5:  en_US');

returns:

'asdf 1.5:  en_US'

which is an incorrect locale level. Depending on the ICU version in use, results may vary. The first day of the week could be Sunday or Monday.

For more information, see: http://userguide.icu-project.org/locale#TOC-Locales-and-Services

German (de_DE) short month has an extra "."

For example, the statement:

SELECT VARCHAR_FORMAT('2015-10-10-04.05.19.934567','MON','de_DE') from sysibm.sysdummy1;

returns:

OKT.

Prior to this change, this statement would return:

OKT

Extra space in Meridian value for es_ES locale

There is now an extra space in the middle of the a. m. and

p.
m.

values. For example:

values to_char(timestamp('2008-1-01-23.59.59.000000'),'Mon dd, YYYY hh12:mi:ss PM','es_ES')

returns:

Ene. 01, 2008 11:59:59 p. m.

Prior to this change, this statement would return:

Ene. 01, 2008 11:59:59 p.m.

Default format string VARCHAR_FORMAT function changes depending on locale

For example, the statement:

values VARCHAR_FORMAT(timestamp_format('2008-06-20 01:23:45 AM','YYYY-MM-DD HH:MI:SS PM'))

For the locale zh_TW returns:
```
2008年6月20日 上午1:23:45
```
Prior to this change, this statement would return:
```
2008/6/20 上午1:23:45
```
For the locale en_US returns:
```
Jun 20, 2008, 1:23:45 AM
```
Prior to this change, this statement would return:
```
'Jun 20, 2008 1:23:45 AM'
```
For the locale de_DE returns:
```
20.06.2008, 01:23:45
```
Prior to this change, this statement would return:
```
20.06.2008 01:23:45
```

Incorrect use of "$" in regexp_replace returns an error

"$" in a replacement string of a regular expression processing refers to a capture group. It should be followed by an integer. Use "$" and it shall be escaped.

For example, the statements:

regexp_replace("aba", "b", "\\$" )




db2 "SELECT REGEXP_REPLACE ('aba', 'b', '\\$' ) FROM sysibm.sysdummy1"

1

a$a        

  1 record(s) selected.

db2  "SELECT REGEXP_REPLACE ('aba', 'b', '$' ) FROM sysibm.sysdummy1"  

1

SQL0171N The statement was not processed because the data type, length or value of the argument for the parameter in position "3" of routine. "SYSIBM.REGEXP_REPLACE" is incorrect. Parameter name: "". SQLSTATE=22546