IBM Releases Db2 12 for z/OS Function Level 504

Early in March 2019, IBM announced the latest and greatest new function level, 504 (FL504), for Db2 12 for z/OS. For those of you not paying attention, starting with Version 12, IBM has moved to a continuous delivery model and has been releasing new functionality regularly. This means that new capabilities are rolled out periodically in what IBM calls function levels.

The first few new function levels were released slowly since the GA of Version 12, but IBM has indicated that it will begin releasing function levels much more quickly now that it has tested and perfected its continuous delivery methodology.

So, what’s new in FL504? Well, several things. Let’s start with better control over deprecated objects. Those of us who work with Db2 know that, over time, IBM deprecates functionality. When functionality is deprecated it means that it is on the list of things that will be removed from Db2 in the future. That is, deprecated features are still supported, but IBM does not recommend that you continue to use the features. Basically, deprecation gives users time to migrate their usage to another feature or capability until such time as support is entirely removed.

OK, so IBM has been deprecating features for a long time now, what is new in FL504? We now get the ability to prevent the creation of new deprecated objects, which is a good idea, right? If the feature has been deprecated by IBM you really shouldn’t be building new systems with that deprecated capability; the idea behind deprecation is to allow existing functionality to work until you can remove or replace it with support functionality. Click here for a full list of deprecated function in Db2 12.

Once you have enabled function level 504 you can prevent the creation of certain deprecated objects in your Db2 subsystems. Starting in function level 504, SQL statement in packages that are bound with APPLCOMPAT(V12R1M504) or higher, or with comparable SQL options in effect, are prevented from creating the following types of deprecated objects:

• Synonyms
• Segmented (non-UTS) or partitioned (non-UTS) tables spaces
• Hash-organized tables

Hash-organized tables are an interesting new deprecation. They were introduced quite recently in Version 10. Evidently, they were not used by enough Db2 sites to justify their continued support.

Another new capability of function level 504 is support for Huffman compression of Db2 data. This uses the IBM Z hardware-based entropy encoding (Huffman) compression with the IBM z14 Compression Coprocessor (CMPSC). A new ZPARM, TS_COMPRESSION_TYPE, has been introduced (once you have implemented FL504) that controls the compression method for the entire Db2 subsystem. 

The TS_COMPRESSION_TYPE subsystem parameter specifies the type of compression algorithm to use when Db2 creates new compressed table spaces, loads into, and reorganizes existing compressed table spaces. Of course, the Db2 subsystem must be running on z14 hardware with Huffman compression enabled for this parameter to be used. Additionally, Huffman compression only applies to universal table spaces. All other table space types will use fixed-length compression regardless of TS_COMPRESSION_TYPE.

The third new capability of FL504 is support for the built-in functions provided by the IBM Db2 Analytics Accelerator  (IDAA). The support is pass-through only, meaning that you must have IDAA in order for these BIFs to work. Db2 will recognize that they are supported by IDAA and pass the work to the accelerator. Db2 for z/OS only verifies that the data types of the parameters are valid for the functions. The accelerator engine does all other function resolution processing and validation. Click here for more information on how Db2 determines whether to accelerate eligible queries or not. 

The IDAA BIFs that are supported in this way as of Db2 12 for z/OS FL504 are 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 and REGEXP_SUBSTR.

Finally, FL504 provides new SQL syntax alternatives to make it easier to port applications to Db2 from other platforms. This capability gives developers more options for specifying certain special registers and NULL predicates.

Table 1 below outlines the new syntax variations that are supported for several existing special registers:

Table 1. New Special Register Syntax Alternatives 

Existing Special Register	New Syntax Alternative
CURRENT CLIENT_ACCTNG	CLIENT ACCTNG
CURRENT CLIENT_APPLNAME	CLIENT APPLNAME
CURRENT CLIENT_USERID	CLIENT USERID
CURRENT CLIENT_WRKSTNNAME	CLIENT WRKSTNNAME
CURRENT SERVER	CURRENT_SERVER
CURRENT TIME ZONE or CURRENT TIMEZONE	CURRENT_TIMEZONE

Additionally, ISNULL and NOTNULL are also now supported as alternatives for the IS NULL and IS NOT NULL predicates. For additional details, see NULL predicates.

Summary

Take some time to investigate this new functionality to determine whether it makes sense to introduce it to your Db2 environment. If so, be suire to read through the incompatible changes (such as if your Db2 has a UDF that matches one of the new IDAA BIFs, or a variable that uses one of the new syntax alternatives).  And only then build your plan for activating the new function level.

Also, be aware that if you are not currently running at FL503, moving to FL504 activates all earlier function levels. You can find a list of all the current function levels here.