The 2022 IDUG North American Db2 Tech Conference was held in Boston the week of July 11, 2022. For those who do not know, IDUG is the International Db2 User Group and it has been supporting Db2 users and conducting conferences across the world since 1988.
This year’s event was hybrid with both in-person and virtual participation. Over 600 Db2 users attended and chose from more than 125 technical sessions delivered by IBMers, vendors, consultants, and end users. I attended in-person, but many of the sessions were also broadcast live to virtual attendees, including my presentation “Things Your DBAs Hear.” The overall feel of the event was that people were glad to be participating live, person-to-person again.
From a content perspective, there was quite a lot to choose from. IBM made news with its announcement of Db2 pureScale on AWS, a new web page supporting Db2 Developers was announced, and there were opportunities for attendees to participate in hands-on testing and take free certification tests… But the highlight of the event for me was learning more about the latest and greatest version of mainframe Db2.
Db2 13 for z/OS was released by IBM for general availability on May 31 of this year, but if you were not paying close attention, you may have missed it. Db2 13 for z/OS was announced at the same time as the new mainframe (IBM z16), so it didn't get quite the same level of attention. But those of us who use Db2 for z/OS day-in and day-out will find a lot of great new stuff in this latest and greatest version of Db2.
Db2 13 for z/OS
What do you need to know about Db2 13 for z/OS? Well, let’s start at the beginning. The first thing most shops will need to do is activate Db2 12 function level 510 (FL510) before you can migrate to Db2 13. Db2 function levels deliver new capabilities and enhancements to Db2 using a continuous delivery model, and there have been 10 function levels for Db2 12.
Unlike all the other function levels, FL510 does not add any new features or capabilities, nor does it introduce any new changes to the Db2 Catalog. So, what does it do?
FL510 is basically there to prepare for the new release of Db2. You can think of it as housekeeping and tidying up to prep for Db2 13. When you activate FL510 it verifies and enforces pre-migration conditions that have to be met before you can migrate to Db2 13, such as ensuring that all Db2 12 function levels are activated and that all catalog updates for Db2 12 have been applied.
Additionally, FL510 will check to make sure that your application packages were rebound recently enough to ensure that they are supported by Db2 13. If any of the previous conditions are not met, then the activation of FL510 will fail. You will have to remediate your system and try to activate FL510 again before you can move forward to the new version.
The bottom line is that FL510 is a boring function level, in that it delivers no new functionality, but it is required before you can migrate to Db2 13. The interesting part will be watching how this impacts customer migration. Many organizations are lagging behind in terms of function level activation, so it may take some time before they make their way to FL510 before they can start their migration to Db2 13.
So far so good, but what about the new functionality in Db2 13?
The first thing to take note of is the ongoing synergy between Db2 and IBM Z. Many DBMS products are designed to be heterogeneous; that is, to function across multiple operating systems with little or no changes required. But Db2 for z/OS is engineered specifically for the IBM Z and can therefore take advantage of unique aspects of the hardware.
One example of this synergy is the exploitation of the IBM Integrated Accelerator for Z Sort, and its SORT LISTS (SORTL) machine instruction, available on IBM z15 and later models. This can greatly improve the efficiency of sorting Db2 data.
Another example of zSynergy is the ability to take advantage of more concurrent data sets and threads, as delivered by z/OS 2.5. The maximum number of open data sets in Db2 13, as controlled by the DSNZPARM DSMAX, has been doubled, from 200,000 to 400,000.
Additionally, the IBM z16 System Recovery Boost can minimize downtime by speeding up the performance of Db2 for z/OS restart. Furthermore, Db2 13 for z/OS takes advantage of the new IBM Z Telum processor for artificial intelligence (AI) workloads.
Perhaps the biggest new capability in Db2 13 for z/OS is SQL Data Insights. This refers to the delivery of new AI functions built into Db2. By combining deep learning in AI with the new IBM Z processor, SQL Data Insights enables users to write SQL-based semantic queries on their Db2 tables and views.
SQL Data Insights is a built-in capability of Db2 delivered with the new release at no additional cost. From a developer perspective, the basic functionality of SQL Data Insights is delivered via three new built-in functions:
- AI_SIMILARITY: Computes a score to enable comparing data for its similarity. For example, you could specify a customer and ask Db2 to return other customers that are most similar to it.
- AI_SEMANTIC_CLUSTER: Computes a semantic clustering score of a member argument against a set of clustering arguments. For example, you could specify a set of customers and ask Db2 to return other customers that best belong to that set.
- AI_ANALOGY: Computes an analogy score between two sets of values. This function works like an analogy: A:B as C:?.
Of course, this is a high-level overview of SQL Data Insights. Consult the documentation for the actual formulation of queries using these functions. And understand, before these functions can be used, you need to train the model for AI by collecting key statistics and building metric scores for the functions to use. As anybody who has built models for AI knows, the process can be lengthy and consume a lot of CPU resources. Fortunately, zIIPs can be used to build the models.
With SQL Data Insights, Db2 13 enables you to extend your queries into the realm of AI, which is exciting because it can help you to gather heretofore undiscovered insight into your data. But there are other AI capabilities delivered with Db2 13 in addition to SQL Data Insights.
AI is infused into Db2 13 delivering improved performance, reduced cost, and more proactive system monitoring. The Db2 engine code can intelligently adapt its behavior as it learns from processing your workload. Consider for example, the index lookaside improvements introduced in Db2 13. By storing additional details of index access and Db2 can enable more lookaside operations for more indexes and use cases. Db2 13 index lookaside will minimize root-to-leaf index for INSERT, UPDATE, and DELETE operations regardless of the index cluster ratio. This can reduce index GETPAGE requests, particularly for modification-heavy workloads.
Additionally, Db2 13 helps to simplify building models for AI and exploits the speed of the IBM z16 for training and querying data. Of course, AI is not the only new functionality delivered in Db2 13 for z/OS. These are the business-as-usual features that IBM continues to introduce to make Db2 more usable and efficient. These Db2 13 features can be broken down into the following categories:
- Availability, Resiliency, and Scalability
- Performance
- Security
- Simplification and Serviceability
- Utilities
Availability, Resiliency, and Scalability
Typically, each new version of Db2 delivers functionality that improves these core requirements of database and application processing. Reducing outages and delivering continuous, seamless 24/7 functionality is the holy grail for all DBMSes, and Db2 13 offers some nice features to help achieve this goal.
One of the most anticipated capabilities delivered by Db2 13 is the ability to convert tablespaces from partition-by-growth (PBG) to partition-by-range (PBR) without taking an outage. Until Db2 13 there has been no easy method of achieving this. Universal tablespaces were introduced way back in Db2 9 for z/OS, so over time, as data volume and requirements change, many organizations find themselves with tablespaces originally defined as PBR that would be easier to manage as PBG. Db2 13 introduced the ALTER PARTITIONING TO PARTITION BY clause that makes it simple to convert a table's partitioning scheme. Issuing the ALTER to change from PBR to PBG results in a pending change that requires a REORG to materialize. And the REORG can be performed online, meaning that no outage is required to make the change.
There are many other availability and scalability improvements in Db2 13 including: the afore-mentioned doubling of DSMAX, the ability to delete an active log data set from the bootstrap data set (BSDS) while Db2 is running, improvements to Real Time Stats maximum values, increased size of Db2 Directory tablespaces, and Extended Common Service Area (ECSA) constraint relief.
Better utilization of above-the-bar storage greatly improves the resiliency of Db2 13. Moving things like storage for Accounting and Statistics traces from constrained storage areas allows for more seamless Db2 operation because fewer insufficient storage conditions will be reached.
Performance
Of course, performance improvements are one of the first things that most DBAs look for in a new version of Db2. And Db2 13 offers up some nice, new performance gains.
Fast Traversal Blocks (FTBs) are improved in Db2 13. FTBs are used by Db2 to keep portions of indexes in memory for more rapid retrieval, which can significantly improve query performance. Initially in Db2 12, FTBs were only supported for unique indexes with a key no longer than 64 bytes. Db2 12 FL508 eliminated some restrictions, but Db2 13 delivers even greater functionality. Now, Db2 13 supports both unique and non-unique indexes; the key length can be up to 128 bytes for unique indexes and up to 120 bytes for non-unique.
And when it comes to better index performance in Db2 13, do not forget about the index lookaside improvement discussed earlier in the AI section!
Additional performance improvements delivered in Db2 13 include
- INSERT enhancements for partition-by-growth table spaces
- SORTL improvements, as mentioned earlier in the IBM Z synergy discussion
- CPU reduction with PBR RPN tablespaces in a Data Sharing environment
- Improved index reorganization
- Group buffer pool residency time enhancements
Security
Improving the security and protection of data is one of the most important goals for most organizations these days as the amount of data, and its importance to the organization, skyrockets. And Db2 13 for z/OS delivers several new capabilities for improving the security of your enterprise data.
A security improvement that spans the security and performance categories is a series of enhancements that reduce the contention for Security Manager resources when accessing Db2. Native Db2 security checking automatically uses an authorization cache, but Db2 13 enables this caching for external security users, as well. This should result in improved performance for organizations that enable external security for Db2 access.
Db2 13 also delivers more flexibility for managing plans, packages, and SQL routines. New owner type support is added in Db2 13 enabling the DBA to control the ownership of plans, packages, and SQL routine packages using the DBA role without depending on the security administrator. For stored procedures and functions, the CREATE and ALTER statements have been modified so that the owner type can be ROLE or USER. The same owner type support has been added to BIND and REBIND for packages and plans.
Db2 13 also improves support for the z/OS Integrated Cryptographic Service Facility (ICSF), which added the decrypt-only archived key enhancement in z/OS 2.5. Db2 13 delivers support for the decrypt-only archived key when a key label is specified by using the Db2 interfaces for data set encryption.
Furthermore, Db2 13 now supports the IBM Z Security and Compliance Center with automated collection of Db2 data for compliance processes. Db2 for z/OS listens for the appropriate signal generated by the z/OS Compliance Agent services and generates SMF 1154 trace records for the recommended system security parameter settings. This can improve the ease of integrating Db2 into your compliance and security management requirements.
Simplification and Serviceability
Another key focus for Db2 13 functionality is to improve the ease of use and serviceability of Db2. Db2 13 continues the improvements made with Db2 12 in terms of continuous delivery with function levels. Furthermore, the actual migration process continues to be streamlined and improved, making it easier to move from version to version of Db2.
Db2 13 delivers application management improvements as well, such as more granular control of locking with a new lock timeout special register and a global variable for weighting deadlocks. Additionally, the ability to dynamically control the package RELEASE option using application Profile tables should make it easier to introduce DDL changes while applications run.
Other serviceability improvements have been introduced in Db2 13 such as improved diagnostics (Index Manager and page reject information) and a new IFCID (306) for improved data replication with EDITPROCs.
IBM Utilities
There is a long list of new and improved capabilities in the IBM Db2 utilities that can improve your Db2 administration and maintenance tasks. I am not going to go over most of the enhancements in any detail. Some of the improvements include enhanced inline statistics collection (in REORG and LOAD) with page-level sampling, enhanced space-level recovery, and REPAIR WRITELOG dictionary support.
There is, however, one big improvement that I want to cover in a little more detail: utility history! Through Db2 12, the only place that stored any form of records regarding when Db2 utilities ran was the SYSIBM.SYSCOPY table. Db2 13 improves upon this significantly.
First things first: if your shop relies on processes that read SYSCOPY that data will remain as is and your processes will continue to work.
So, what’s new? Db2 13 delivers the ability to collect metadata about the execution of all IBM Db2 utilities. This information will be stored in the new Db2 Catalog table, SYSIBM.SYSUTILITIES, designed for this purpose. Furthermore, the announced intention is for the tables to be extensible for other vendors to populate with their utility execution details. But for now, the following IBM Db2 utilities are supported for history collection:
| BACKUP SYSTEM |
CATMAINT |
| CHECK DATA |
CHECK INDEX |
| CHECK LOB |
COPY |
| COPYTOCOPY |
LOAD |
| MERGECOPY |
MODIFY RECOVERY |
| MODIFY STATISTICS |
QUIESCE |
| REBUILD INDEX |
RECOVER |
| REORG |
REPAIR |
| REPORT RECOVERY |
REPORT TABLESPACESET |
| RUNSTATS |
STOSPACE |
| UNLOAD |
|
Of course, utility history will not start to be collected until you indicate to Db2 that it should. This is accomplished by setting a new DSNZPARM (UTILITY_HISTORY) to enable the collection of utility execution history.
The ability to review what Db2 utilities were run, when they were run, and with information about their success or failure (along with other pertinent metadata) has long been a requirement of in-the-trenches DBAs. In my opinion, this is likely to be one of the most popular new features of Db2 13 for z/OS.
Summary
The bottom line is that there is a new version of Db2 for z/OS that mainframe shops will need to learn and prepare for. There are many significant new features that will cause some organizations to move quickly to adopt Db2 13. As with any new Db2 version, it will be exciting to dig in and discover all of the new stuff that can help us do our jobs better... and improve our organization's efforts to use its data to improve business.