Db2 for z/OS and its ecosystem

This Db2 for z/OS News from the Lab blog entry was originally published on 2019-03-06.

By Chris Crone and Paul McWilliams.

Db2 12 function level 504 (activation enabled by Db2 12 APAR PH07672) introduces a step in the evolution of existing Db2 for z/OS infrastructure—the capability to prevent, if you want to, the creation of the following deprecated object types in your Db2 12 for z/OS environments:

• Synonyms
• Non-UTS table spaces, including "classic" segmented and "classic" partitioned
• Hash-organized tables and the associated hash table spaces

To help our clients better understand what this means for their Db2 environments, we asked IBM Distinguished Engineer Chris Crone about this new capability.

Why are we doing this?

The success of Db2 for more than 35 years, can be attributed to many things: loyal clients, dedicated developers, high availability… Of the many reasons, one that stands out to me is a willingness to embrace change, while retaining compatibility. The ability to transition the old to the new, while the system is running, is a hallmark of Db2."

"Many changes to Db2, like JSON, SQL procedures, and XML, to name a few, are entirely new capabilities, but other change involves the evolution of existing functionality. There have been many such evolutionary changes to Db2 for z/OS such as type-2 indexes, longer identifiers, Unicode catalog tables, 64-bit support, universal table spaces (UTS), reordered row format (RRF), and the extended 10-byte RBA, to name a few examples."

"Preventing the creation of these deprecated objects is another such step in the evolution of Db2. In fact, in the Idea forum for IBM Data & AI clients, we have more than 50 votes for several ideas, where clients indicated that they need help preventing the creation of these items."

What do we mean by "deprecated"?

Merriam Webster includes the following definition for deprecate:

"to withdraw official support for or discourage the use of (something, such as a software product) in favor of a newer or better alternative."

The key phrase there is, "discourage from use." Deprecated capabilities have often remained supported in Db2 for z/OS for compatibility with previous releases and to give clients time to convert their environments to use the newer alternatives.

Deprecated also means that many newer Db2 capabilities are not supported for the deprecated objects. For example, function level 504 also introduces support for IBM z14 hardware-based Huffman compression for Db2 data, but it is supported only for data in (non-hash organized) universal table spaces (UTS).

For a full list of such deprecated capabilities that remain supported in Db2 12, see Deprecated function in Db2 12 in IBM Knowledge Center.

When I activate function level 504, will I still be able create the deprecated objects?

The short answer is, “Yes." Simply activating FL 504 has no effect on your ability to create a synonym, a segmented table space, or any other deprecated object.

If activating function level 504 doesn’t prevent creating the deprecated objects, what does?

Binding a package with APPLCOMPAT(V12R1M504) or higher prevents SQL statements in that package, and only in that package, from creating the deprecated objects. That means that you can control the restrictions for specific applications, at the package (or SQL routine) level.

I have bound an application with APPLCOMPAT(V12R1M504), can I select from a table in a segmented table space?

Yes, all of your applications will continue to have access to deprecated objects that have previously been created.

If I bind a package with APPLCOMPAT(V12R1M504), do I have to REBIND with APPLCOMPAT(V12R1M503) to create a deprecated object?

Not necessarily. Most DDL is run as dynamic SQL. For example, if you use DSNTEP2 to run your DDL, you can issue " SET CURRENT APPLICATION COMPATIBILITY = ‘V12R1M503’;" first, and then you would be able to create a deprecated object.

Why is it so easy to get around the restrictions?

Legitimate reasons remain for clients to create deprecated objects. We're trying to “nudge” our clients to make the decision to avoid using these deprecated objects. There are many real-world examples of “Nudge Theory” working in real life, for some examples, see 10 examples of Nudge Theory. With this enhancement, we hope to reduce the new creation of these deprecated objects in many shops, while giving DBAs the capability to create these objects when necessary.

Why use APPLCOMPAT to control creation of the deprecated objects instead of new zPARMs?

Last year we published the results of a survey of client's zPARM settings. We found that two existing zPARMs that prevent the creation of other deprecated objects are not widely used. Only about about 20% use PREVENT_NEW_IXCTRL_PART (UTS tables always use table-controlled partitioning), and less than 10% use PREVENT_ALTERTB_LIMITKEY.

Db2 development recommends setting both of these zPARMs to YES. Where is the disconnect?

When we asked customers, the most common response was they would not use these zPARMs because of change-control requirements (in many customer environments, changing a zPARM, even in an online fashion, requires going through a change control process). Given this requirement, the zPARM-based solution wasn’t working for many clients."

Why are synonyms deprecated?

Synonyms, and the CREATE SYNONYM statement were announced as deprecated with the release of Db2 10. The recommended alternative is to use aliases. Although there are differences, aliases are similar to synonyms in Db2 for z/OS, and the Db2 family supports aliases with compatible behavior on all platforms.

What about the non-UTS table spaces?

The non-UTS table spaces, including "classic" segmented and "classic" partitioned, were also announced as deprecated with the release of Db2 10. The recommended alternative is to create all new tables in UTS partition-by-range or partition-by-growth table spaces. The creation of new simple table spaces was already prevented beginning in Db2 9."

"Now, when an application is bound with APPLCOMPAT(V12R1M504) or higher, the result of any CREATE TABLESPACE statement is either a partition-by-range or partition by growth universal table space, and any CREATE TABLE statement returns an error if it attempts to create a new table in an existing "classic" segmented or simple table space.

Hash-organized tables are deprecated too? Wait, really?

Yes, with the release of function level 504, we are also announcing the deprecation of hash-organized tables. Db2 10 for z/OS introduced hash-organized tables to provide improved performance for a very specific set of use cases. Hash table spaces were not widely adopted by clients. At the same time, Db2 10 also introduced index-processing enhancements, and newer capabilities such as fast index traversal in Db2 12 provide additional benefits. With these improvements, the niche where hash-organized tables outperform standard UTS tables is extremely small.

"Additionally, the use of hash-organized tables requires DBAs and application developers to make a decision to use hash access. However, we recognize that our clients just want good performance—not more decisions to make. With fast index traversal, Db2 monitors the indexes and decides whether to use it autonomically.

Chris Crone is an IBM Distinguished Engineer for Db2 for z/OS development and Paul McWilliams is an information developer for Db2 for z/OS documentation.