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 seven All new for Db2 12” newsletter from 2021-05.
Starting with Db2 12 – PTFs first
APAR PH36071 added support for the SUBSTR_COMPATIBILITY ZPARM parameter with default PREVIOUS and other valid value CURRENT. In Db2 12 FL500 and above, with this APAR applied and the value set to CURRENT, then the SUBSTR Built-in Function (BiF) will return an error message for invalid input.
APAR PH42524 added MULTIPLY_ALT support to the IBM Db2 Analytics Accelerator (IDAA).
APAR PH47187 added support for UNI_90 locale in the LOWER, TRANSLATE and UPPER BiFs.
APAR PH48480 added LISTAGG and RAND to the IDAA offload support. Note: you must enter YES in ENABLE ACCELERATOR SPECIFIC RESULTS field on panel DSNTIPBA to get this boost.
Db2 12 FL 100
Yes, they introduced a new BiF for this level way after I wrote my last newsletter all about BiFs and Functions. The new BiF is the BLOCKING_THREADS table function. This is very handy when DBAs are about to start doing DDL work. Adding or ALTERing a Column or what have you! The output is a table about who is blocking access to the database in question and can really save a massive amount of work if you can check *before* you do all your ALTERs that you can indeed succeed in getting them all done!
Now Db2 13 – PTFs first
APAR PH47187 added support for UNI_90 locale in the LOWER, TRANSLATE and UPPER built-in functions (BiFs).
APAR PH48480 added LISTAGG and RAND to the IBM Db2 Analytics Accelerator (IDAA) offload support. Remember, you must enter YES in ENABLE ACCELERATOR SPECIFIC RESULTS field on panel DSNTIPBA.
APAR PH51892 introduced vector prefetch for SQL Data Insights and improves the BiF AI_SEMANTIC_CLUSTER. You must also go to Db2 13 FL504.
APAR PH55212 enhanced SQL Data Insights and added support of numeric data types to the BiF AI_ANALOGY.
Db2 13 FL500
This was the release that introduced the Db2 SQL Data Insights with the new BiFs AI_ANALOGY, AI_SEMANTIC_CLUSTER and AI_SIMILARITY.
Db2 13 FL504
A new AI BiF: AI_COMMONALITY was released and when you use LISTAGG you can now add an ORDER BY to the full select.
Db2 13 FL505
Another new BiF: INTERPRET which can change nearly any argument to nearly any other data type. The most useful thing you can do with it is something like:
INTERPRET(BX'0000000000B0370D' AS BIGINT) -- 11548429So this is taking a hex RID and interpreting it as a BIGINT. This is very useful when you get RID problems with the LOAD utility, for example. You can then simply plug in the BIGINT value into a query like:
SELECT * FROM TABLE1 A WHERE RID(A) = 11548429;And then you’ll find the bad guy(s) very easily!
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