In this, obviously, never ending series of new features, I will roll up all the new ones since my “SOUNDEX and other „cool“ features – Part six All new for Db2 12” newsletter from 2018-08 where I first covered the new stuff in Db2 12.
What was new in FL100
At that level, a new table function called BLOCKING_THREADS was introduced, which is primarily used by the command DISPLAY BLOCKERS to help in looking for bad guys who are ready to BLOCK your change to FLxxx
FL500 Changed Scalars
Two scalar BiF got changed: TRANSLATE and UPPER can now both use UNI_60 as a locale.
FL500 New Scalars
In FL500, we got a nice bunch of new Scalar functions:
GENERATE_UNIQUE_BINARY (like GENERATE_UNIQUE except it returns a BINARY(16) value)
HASH_CRC32
HASH_MD5
HASH_SHA1
HASH_SHA256
PERCENTILE_CONT
PERCENTILE_DISC
WRAP
FL500 Changed Aggregates
One aggregate BiF got updated: ARRAY_AGG can now be used for associative arrays.
FL501?
It brought in just one new Aggregate BiF, which was the very famous LISTAGG. This is famous, as it was the “proof of concept” that Agile actually worked and could function in the wild.
FL502?
Two changes to Scalar BiFs in this release:
GRAPHIC The first argument now accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT.
VARGRAPHIC The first argument now accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT.
FL503?
Nothing new for us in that release!
FL504?
It brought in a brand new concept for Db2 on z/OS. The concept of “pass-through BiFs”. These are just checked for valid syntax before being passed on down to the Accelerator that would actually execute them. Obviously, you must be careful about their usage!
CUME_DIST
CUME_DIST (aggregate)
FIRST_VALUE
LAG
LAST_VALUE
LEAD
NTH_VALUE
NTILE
PERCENT_RANK
PERCENT_RANK (aggregate)
RATIO_TO_REPORT
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR
FL505?
Brought in a whole bunch of encryption and decryption BiFs:
ENCRYPT_DATAKEY converts a block of plain text to a block of encrypted text using a specified algorithm and key label.
DECRYPT_DATAKEY_datatype functions return the block of encrypted text from ENCRYPT_DATAKEY as a block of the datatype specified by the function used. List of datatypes: DECRYPT_DATAKEY_INTEGER, DECRYPT_DATAKEY_BIGINT, DECRYPT_DATAKEY_DECIMAL, DECRYPT_DATAKEY_VARCHAR, DECRYPT_DATAKEY_CLOB, DECRYPT_DATAKEY_VARGRAPHIC, DECRYPT_DATAKEY_DBCLOB, and DECRYPT_DATAKEY_BIT.
FL506?
Here IBM came up with a slew of “alternative names” for existing BiFs and a new method of calling HASH routines. The background here was to make porting to Db2 on z/OS that little bit easier!
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 (default) HASH_MD5 Returns VARBINARY(16) instead of BINARY(16)
1 HASH_SHA1 Returns VARBINARY(20) instead of BINARY(20)
2 HASH_SHA256 Returns VARBINARY(32) instead of BINARY(32)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
FL507?
Here IBM development added another slew of “pass through” BiFs:
ADD_DAYS
BTRIM
DAYS_BETWEEN
NEXT_MONTH
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT or REGR_ICPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
ROUND_TIMESTAMP if invoked with a date expression
FL508?
None that I have read of yet!
FL509?
None yet…
Naturally I will be keeping this newsletter up-to-date, as necessary.
Any questions or ideas, do not hesitate to drop me a line,
TTFN,
Roy Boxwell