Late last month, October 2019, IBM introduced a new function level, FL506,
for Db2 12 for z/OS. There are two significant impacts of this new function
level:
- Alternative
function names support
- Support for implicitly dropping
explicitly created table spaces
The first impact, support for additional,
alternative names for some existing Db2 built-in functions, was added is to
improve compatibility across the Db2 product line. It is basically just a
new way to refer to existing functionality, an alternative syntax, if you will.
The following chart outlines the existing and new FL506 alternative syntax.
Table 1. Alternative Syntax for Function Names in
FL506
Existing
Function Name
|
New
Alternative Syntax Name
|
CHARACTER_LENGTH
|
CHAR_LENGTH
|
COVARIANCE
or COVAR
|
COVAR_POP
|
HASH_MD5
or HASH-SHA1 or HASH_SHA256
|
HASH
|
POWER
|
POW
|
RAND
|
RANDOM
|
LEFT
|
STRLEFT
|
POSSTR
|
STRPOS
|
RIGHT
|
STRRIGHT
|
CLOB
|
TO_CLOB
|
TIMESTAMP_FORMAT
|
TO_TIMESTAMP
|
Support for these alternative spelling of built-in
function names should make it easier to support applications across multiple
members of the Db2 family where support already exists for these spellings. Of
course, you may run into issues if you used any of the new spellings in your existing
applications, for example as variable names.
The other significant feature of FL506 is support for implicitly dropping
explicitly created universal table spaces when a DROP TABLE statement is
executed. Prior to FL506 dropping a table that resides in an explicitly created
table space does not drop the table space.
If you use vendor tools
that manage and generate scripts for DDL changes, they need to be modified to
support FL506. If not, they could produce -204 SQL codes when the generated DDL
is executed if the DDL contains a DROP TABLESPACE statement. The table space
will have already been implicitly dropped and trying to drop a table space that
does not exist will throw an error. Be sure to discuss this with your tools
vendor before migrating to FL506 to understand the cendor’s support timeline or
if they have a workaround.
Summary
IBM is using function
levels to deliver significant new capabilities for Db2 12 for z/OS. It is
important for you and your organization to keep up-to-date on this new
functionality and to determine where and when it makes sense to introduce it
into your Db2 databases and applications.
