Db2 for z/OS

Announcing Db2 12 function level 506!

By Paul McWilliams posted Mon December 09, 2019 05:31 PM


This Db2 News from the Lab blog entry was originally published on 2019-11-06.

By Paul McWilliams.

Db2 12 function level 506 is now available. It introduces new alternative spellings for existing SQL built-in functions to improve compatibility with competitive database platforms, the SQL standard, and the Db2 product family. It also introduces a new capability to implicitly drop explicitly created table spaces when dropping tables, which we'll cover in more detail in a future post. You can activate function level 506 after you apply the PTF for APAR PH16829.

The new alternative function names are supported for existing functions that Db2 for z/OS supports. However, adding support for the alternative names, increases the compatibility and portability of applications across the entire Db2 product family and with other database management systems too.

The following table lists the new syntax alternatives that function level 506 introduces for applications bound at application compatibility level V12R1M506 or higher.

Newly supported function name

Existing equivalent function


CHARACTER_LENGTH, which returns the length of its argument in the number of string units that are specified


COVARIANCE or COVAR, which return the population covariance of a set of number pairs


HASH_MD5, HASH_SHA1, or HASH_SHA256, which return the result of applying a hash algorithm to an input argument, depending on the value specified for the second argument for the HASH function:


0 - HASH_MD5 (default)


2 - HASH_SHA256

The HASH function returns a varying length (VARBINARY) value, unlike the existing functions, which return fixed length (BINARY) values.


POWER, which returns the value of one argument raised to the power of a second argument


RAND, which returns a double precision floating-point random number


LEFT, which returns a string that consists of the specified number of leftmost bytes or the specified string units


POSSTR, which returns the position of the first occurrence of an argument within another argument


RIGHT, which returns a string that consists of the specified number of rightmost bytes or specified string units


CLOB, which returns a CLOB representation of the first argument


TIMESTAMP_FORMAT, which returns a timestamp for a character string expression, using a specified format to interpret the string


Along with the newly supported syntax alternatives, we're also making it easier for readers to find the supported alternative function names in the documentation, in both IBM Knowledge Center and in the Db2 12 for z/OS SQL Reference.

If the spellings for alternative function names are not adjacent when organized alphabetically, such as with CLOB and TO_CLOB, we've added a small "landing page" in the correct alphabetical position for each syntax alternative. Each such landing page contains only a brief description of the function, the syntax diagram, and a link to the page for the corresponding "preferred" Db2 for z/OS syntax, which always contains the full detailed description of the function. For examples of the new landing pages, see the links in the preceding table.

For a complete list of the SQL built-in function supported by Db2 12 for z/OS, see List of supported built-in functions.

Paul McWilliams is an information developer for Db2 for z/OS Development.