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
|
CHAR_LENGTH
|
CHARACTER_LENGTH, which returns the length of its argument in the number of string units that are specified
|
COVAR_POP
|
COVARIANCE or COVAR, which return the population covariance of a set of number pairs
|
HASH
|
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)
1 - HASH_SHA1
2 - HASH_SHA256
The HASH function returns a varying length (VARBINARY) value, unlike the existing functions, which return fixed length (BINARY) values.
|
POW
|
POWER, which returns the value of one argument raised to the power of a second argument
|
RANDOM
|
RAND, which returns a double precision floating-point random number
|
STRLEFT
|
LEFT, which returns a string that consists of the specified number of leftmost bytes or the specified string units
|
STRPOS
|
POSSTR, which returns the position of the first occurrence of an argument within another argument
|
STRRIGHT
|
RIGHT, which returns a string that consists of the specified number of rightmost bytes or specified string units
|
TO_CLOB
|
CLOB, which returns a CLOB representation of the first argument
|
TO_TIMESTAMP
|
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.
#Db2forz/OS#db2z/os#Db2Znews