There’s a New Db2 12 for z/OS Function Level (505)

In late June 2019, IBM delivered more great new capabilities with the latest new function level for Db2 12 for z/OS, Function Level 505 (or FL505).

If you do not know what a function level is, then you probably aren’t yet on Version 12, because function levels are how new capabilities are being rolled out for Db2 12 and beyond. It is how IBM has enabled a continuous delivery model for Db2 functionality. You can learn more about function levels here.

Although the first link above goes into all of the gory details of the new functionality, I will take a bit of time to summarize the highlights of this new function level.

The first thing that will appeal to most Db2 users is improved performance. And FL505 delivers improved performance in two areas: HTAP and RUNSTATS.

• For HTAP, FL505 improves the latency between Db2 and the IBM Analytics Accelerator (sometimes called IDAA). Nobody likes latency and these improvements can enable transactional and analytical applications to see the same data.
• For RUNSTATS, FL505 makes the default RUNSTATS behavior to use page sampling for universal table spaces (unless the RUNSTATS specification explicitly states TABLESAMPLE SYSTEM with a value other than AUTO). This will boost RUNSTATS performance. (A nice description of this is provided by Peter Hartmann here.)

FL505 also delivers REBIND phase-in for executing packages. Waiting for a package to be idle (not running) has long been a deterrent to rebinding. Now, you can REBIND a package while it is running. Db2 makes this happen by creating a new copy of the package. When the REBIND completes, new executions of the package will use the newly rebound package and the threads already running with the old package continue to do so successfully until completion.

We also get some new built-in functions (BIFs) in FL505, for encrypting and decrypting data using key labels. You may be aware that Db2 already had functions for encryption and decryption but these functions, introduced back in V9 were not very capable because they required you to provide and manage a password to decrypt the data. The new functions work with key labels: encrypting plain text using ENCRYPT_DATAKEY to a block of encrypted text using a specified algorithm and key label; and decrypting with DECRYPT_DATAKEY to return the block of data decrypted to the specified data type.

And with FL505 we finally get additional functionality for DECFLOAT data type. The DECFLOAT data type was introduced in DB2 9 for z/OS, but it is not widely used because of some shortcoming. But first, what is DECFLOAT? Well, DECFLOAT is basically a combination of DECIMAL and floating-point data types, that is a decimal floating-point or DECFLOAT data type. Specified as DECXFLOAT(n), where the value of n can be either 16 or or 34, representing the number of significant digits that can be stored. A decimal floating-point value is an IEEE 754r number with a decimal point and it can be useful to store and manage very large numbers.

So what is the improvement? Quite simply, it is now possible to specify columns defined as DECFLOAT in an index and as a key in a primary key or a unique key. Unfortunately, there is still no support for DECFLOAT usage in COBOL programs, which will likely continue to hinder its uptake in many shops.

And finally, FL505 improves temporal support for triggers. It delivers the capability to reference system temporal tables and archive-enabled tables in the WHEN clause of your triggers.  

Summary

IBM is using function levels to deliver significant new capabilities for Db2 12 for z/OS. It is important for you and your organization to keep up-to-date on this new functionality and to determine where and when it makes sense to introduce it into your Db2 databases and applications.

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

The Db2 12 for z/OS Blog Series - Part 22: Function Levels 501 and 502 (Continuous Delivery)

If you have heard anything about Db2 Version 12 chances are that you have heard about continuous delivery. Instead of waiting 2 to 3 years for a new version of Db2 to be released, new functionality will be continuously delivered on a regular basis. The idea is to bring Db2 into the modern age of development practices where releases are small and quick, instead of large and slow.

So instead of waiting for the next version, Db2 professionals now wait on new Function Levels, where a Function Level identifies a set of new enhancements that can be enabled in Db2 for z/OS.

Of course, this means that a lot of internal practices and procedures had to be re-engineered and established at IBM, so there have not been many new Function Levels since Db2 12 was first released back in October 2016. There was Function Level 501 in early 2017, which basically added a simple new built-in function, LISTAGG.

The LISTAGG built-in function produces a list of all values in a group. An optional separator argument can delimit items in the result list. For example, specifying a comma as the separator produces a comma-separated list. An optional ordering can also be specified for the items within the group. So for example:

SELECT   WORKDEPT,
         LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
             AS EMPLOYEES
FROM     EMP
GROUP BY WORKDEPT;

This will return a comma-separated list of employee last names by department number.

Unless you needed the capability of LISTAGG in your applications there was no reason to migrate to Function Level 501. Except, of course, to test out moving to a new Function Level, which is the primary reason that IBM released LISTAGG as a Function Level. And that was it until recently…

Function Level 502 (FL502) was made available by IBM in late April 2018. This is the first “real” Function Level with multiple new capabilities that may entice your shop to implement it. 

Here are the capabilities introduced in FL502:

The first new feature bolsters DFSMS data set encryption (which is part of the Pervasive Encryption for IBM Z solution introduced with the z14). With FL502 we get KEYLABEL management capability for z/OS DFSMS data set encryption. You can manage the key labels for z/OS DFSMS data set encryption to transparently encrypt Db2 data sets. 

DFSMS can be used to encrypt various types of Db2 data sets including Db2-managed table space and index space data sets, data sets that are used by Db2 utilities, and sequential input and output data sets. 

After moving to FL502 an administrator (DBA, security admin, system admin or storage admin depending on your shop) can enable z/OS DFSMS data set encryption for your Db2 data sets.

Additionally, IBM offers a free tool, IBM z Systems Batch Network Analyzer (zBNA), which can be used to help estimate the costs of DFSMS data set encryption for your Db2 data sets. Additionally, the Db2 Statitistics Trace has been enhanced to report CPU time, which you can look at to help determine which data sets to encrypt.

The second enhancement enabled with FL502 is the ability to cast an explicit numeric value to a graphic string value. All of the numeric data types are supported. So you can use the GRAPHIC or VARGRAPHIC built-in functions and/or the CAST specification to cast numeric values to graphic string values. Regardless of whether CAST or GRAPHIC/VARGAPHIC functions are used, the result is Unicode (UTF-16), and the context must support Unicode data.

Implementing Function Level 502

You can activate Function Level 502 from Function Level 501, 500, 100, or as part of migration from Db2 11 (with z/OSMF only). Function level 502 requires catalog level 502, and tailoring the catalog for level 502 requires function level 500 or 501. Take care before activating any new Function level by making sure that you understand what Function Levels are, how they are delivered, and the current state of your Db2 subsystems.

You can easily view the current state of your Db2 subsystems by using the -DISPLAY GROUP command. It will show you the current Function Level, the high Function Level ever activated (which might be higher than current if you fell back), and the highest possible Function Level (based on the APARs that have been applied to your Db2 system).

What's Next?

Things are likely to speed up in terms of new Function Levels for Db2. Now that IBM has had time to implement new internal development proceudres and get them all test out appropriately, we should start seeing new capabilities more frequently than once a year... perhaps as frequently as quarterly. So make sure that you are ready to review every new Function Level as it is made available and make plans to activate the ones that deliver functionality that you need.

Another thing to keep in mind is that Function Levels are cumulative. So you cannot implment say, Function Level 502, without also getting the capabiltiies of all previous Function Levels (in this case, just 501). So be prepared and understand what activating a new Function Level means!

Welcome to the new world of continuous delivery in Db2 for z/OS… and take a look at how the new capabilities in Function Levels 501 and 502 might be useful at your shop and to your applications.

Encryption [DB2 9 for z/OS]

DB2 V9 offers some encryption news, but we need to go back a version to start the story. You see, DB2 supports encryption in Version 8 through encryption functions that need to be explicitly coded in order to encrypt and decrypt data.

These functions (ENCRYPT and DECRYPT) allow you to encrypt and decrypt data at the column level. Because you can specify a different password for every row that you insert, you are encrypting data at the “cell” level in your tables. If you use these functions to encrypt your data, be sure to put some mechanism in place to manage the passwords that are used to encrypt the data. Without the password, there is absolutely no way to decrypt the data.

To assist you in remembering the password, you have an option to specify a hint (for the password) at the time you encrypt the data. The following SQL example shows an INSERT that encrypts the SSN ( social security number ) using a password and a hint:

INSERT INTO EMP (SSN)
VALUES(ENCRYPT('289-46-8832','TARZAN','? AND JANE'));

The password is “TARZAN” and the hint we’ve chosen to provide is “? AND JANE”… so the hint will prompt us to think of Tarzan as the companion of Jane.

In order to retrieve the encrypted data you will need to use the DECRYPT function supplying the correct password. This is shown in the following SELECT statement:

SELECT DECRYPT_BIT(SSN,'TARZAN') AS SSN
FROM EMP;

If we fail to supply a password, or the wrong password, the data is returned in an encrypted format that is unreadable.

The result of encrypting data using the ENCRYPT function is VARCHAR FOR BIT DATA. The encryption algorithm is an internal algorithm. For those who care to know, it uses Triple DES cipher block chaining (CBC) with padding and the 128-bit secret key is derived from the password using an MD5 hash.

When defining columns to contain encrypted data the DBA must be involved because the data storage required is significantly different. The length of the column has to include the length of the non-encrypted data + 24 bytes + the number of bytes to the next 8 byte boundary + 32 bytes for the hint.

OK, that is all V8 stuff and this series of blog postings is supposed to be about V9 functionality, right? So what about version 9? Well, DB2 9 for z/OS offers some nice improvements to encryption support. Firstly, DB2 can take advantage of encryption hardware advances.

CP Assist for Cryptographic Function, aka CPACF, is available on z990 hardware. CPACF can run on all the CPUs, but remember, this feature is available only on z990 and later machines, not the older z900. The z990 also introduces a PCIXCC card which is needed for the IBM Data Encryption Tool, but not for the DB2 encryption functions.

Note: The IBM Data Encryption Tool (available from IBM at an additional price) offers encryption for DB2 tables at the table level, whereas the encryption functions (free with DB2) offer encryption at the column level.

The CP Assist for Cryptographic Function delivers cryptographic support on every CP with Data Encryption Standard (DES), Triple DES (TDES), and Advanced Encryption Standard (AES)-128 bit data encryption/decryption, as well as Secure Hash Algorithm (SHA-1) and SHA-256 hashing. For a more detailed discussion of CPACF, associated technology and functionality, check out the following IBM redbook: IBM eServer zSeries 990 (z990) Cryptography Implementation (SG24-7070).

Basically, the net result is that the cost of encrypting DB2 data under V9 is reduced on the z990 hardware.

Additionally, IBM has added encryption support in the controllers of its storage devices.

Both the IBM TS1120 tape drive and IBM Ultrium 4 tape drives include data encryption capabilities within the drives. This support can allow you to avoid the need for host-based encryption of data or the use of specialized encryption appliances. In addition, IBM claims that the encryption does not significantly impact the performance of the drives so there should be minimal to no impact on the batch processing window when encrypting in this manner.

So far, we’ve been talking about encryption for data at rest. But DB2 9 for z/OS also improves support for encryption of data in transit. DB2 9 supports the Secure Socket Layer (SSL) protocol by implementing the z/OS Communications Server IP Application Transparent Transport Layer Security (AT-TLS) function. The z/OS V1R7 Communications Server for TCP/IP introduces the AT-TLS function in the TCP/IP stack for applications that require secure TCP/IP connections. AT-TLS performs transport layer security on behalf of the application, in this case DB2 for z/OS, by invoking the z/OS system SSL in the TCP layer of the TCP/IP stack. The z/OS system SSL provides support for TLS V1.0, SSL V3.0, and SSL V2.0 protocols.

So encryption of data over the wire is improved in z/OS 1.7. The Communications Server supports AT-TLS, which uses SSL data encryption. Now SSL encryption has been available on z/OS for a long time, but now DB2 9 for z/OS makes use of this facility and offers SSL encryption using a new secure port.

When acting as a requester, DB2 for z/OS can request a connection using the secure port of another DB2 subsystem. When acting as a server, and from within a trusted context (I’ll discuss trusted context in a later DB2portal blog entry), SSL encryption can be required for the connection.

So, little by little, better encryption support is being made available within the world of DB2 for z/OS.