Consider Application-Level Lock Control in Db2 13 for z/OS

It has been close to a year since Db2 13 for z/OS has been generally available. It was announced in April 2022 and delivered for GA on May 31, 2022.

As you think about migrating from Db2 12 to Db2 13, it is inevitable that you will consider the new functionality and capabilities that comes with the new version. I've discussed the AI functionality of SQL Data Insights delivered in Db2 13, but haven't really dug into some of the other interested new features.

Today, I want to briefly discuss application-level lock control. This new feature enables applications to take more control over Db2 locking. If you have applications that could benefit from different lock parameters than the system-wide settings used by Db2, then this new capability could be useful for at least some of your applications and tasks.

The first thing to note is that you must be at Function Level 500 before you can use application-level lock control. Using application-level lock control then requires setting a special register using the SET CURRENT LOCK TIMEOUT statement. This statement can be included in application programs to control the lock wait duration in seconds. The data type is INTEGER with a range of acceptable values from -1 to 32,767. Setting the CURRENT LOCK TIMEOUT to -1 indicates an indefinite wait, setting it to 0 indicates no waiting. 

Most DBAs reading that last sentence will shudder at the possible implications of waiting forever! Fortunately, there is another new DSNZPARM called SPREG_LOCKTIMEOUT_MAX that can limit the upper bound that an application can use for CURRENT LOCK TIMEOUT.

Nevertheless, in order to implement application-level lock control you will need to modify your application code. So, if you want to wait for locks for up to 50 seconds, you would issue

    SET CURRENT LOCK TIMEOUT = 50

Before the SQL that should wait for that duration. 

Of course, any applications using application-level lock control should be monitored for lock contention. This can be done using Db2 monitoring tools, such as Omegamon or Mainview. The trace record IFCID 437 can also be monitored to discover the specific applications and authorization IDs that use this special register.

Deadlock Resolution Control

Similar to controlling the lock timeout duration, Db2 13 also introduced the ability to manage deadlock resolution control at the application level. This is accomplished using a new system built-in global variable. Instead of just relying on the system setting to control deadlock detection, applications can choose to set the new global variable: SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY 

Valid values range from 0 and 255. The higher the value, the less likely that locks requested by the application will deadlock when the application is involved in a deadlock situation. Applications and users require the WRITE privilege on this global variable in order to be able to issue it successfully.

So, if you want to set this to the max, you would issue

    SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = 200

And then issue the statement(s) you are concerned about deadlocking. 

Of course, using this global variable does not guarantee that the application won't experience a deadlock because there are other considerations involved that Db2 still must negotiate and consider. 

One final note, you can use Profile tables to set CURRENT LOCK TIMEOUT and SYSIBM.DEADLOCK_RESOLUTION_PRIORITY as this support has been added in Db2 13.

1. 
   

Time to Download the Db2 13 for z/OS Manuals!

Yesterday I posted a brief piece about the new version of Db2 for z/OS that is now generally available: Db2 13 for z/OS is Here! It is always an exciting time when a new version of Db2 for z/OS is unleashed on the world and one of the first things I recommend is to start reading the manuals to see what all is there!

I like having the PDF versions of the manuals on my hard drive, so with each new version, I start by downloading them! With that in mind, here is the link to the IBM page that hosts the PDF manuals for Db2 13 for z/OS. 

Of course, over time, this documentation will be modified. Therefore, it is a good idea to be aware that subsequent modifications may render the versions on your hard drive inaccurate. You can always replace them periodically if you wish... perhaps when you adopt a new function level make plans to downloads the latest iterations of the manuals. 

There is also a great, new redbook on Db2 13 for z/OS that you should download and read. It is titled IBM Db2 13 for z/OS and More, and it, along with the What's New manual, can serve as a nice introduction to this new version.

A new manual that I need to mention is the SQL Data Insights Users Guide. If you read my earlier post (Db2 13 for z/OS is Here!) then you know that SQL Data Insights is a new Db2 13 capability that combines AI/deep learning with Db2 for z/OS and IBM Z technologies to deliver SQL-based semantic queries on tables and views. It is probably one of the most exciting new things in this new version...

So if you work with Db2 for z/OS don't delay... download those new manuals and start learning what new wonders Db2 13 will offer up!

Db2 13 for z/OS is Here!

Here we are, in June 2022, about 5 years or so since Db2 12 for z/OS was released. And lo' and behold, IBM has given us a new version of Db2 for z/OS to learn and adopt: Db2 13 for z/OS. 

The new version is generally available (as of May 31, 2022). If you were not paying close attention though, you may have missed it. Db2 13 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.

I'm not going to go into great detail about the new features and functionality of Db2 13 for z/OS today, but I will offer a high-level overview. Look for future blog posts to dig into more of the nitty gritty tech details and capabilities.

The first thing to mention is that you will need to activate the last Db2 12 function level 510 (FL510) before you can migrate to Db2 13. As many organizations are lagging behind in terms of function level activation, it will be interesting to see how this requirement impacts migration to Db2 13.

AI

So what can users expect from this new version? Well, it seems that the most talked-about features are related to adopting AI. Functions that deliver AI capabilities into Db2 will make it easier for organizations on the AI journey to integrate Db2 into their processes.

Perhaps the most significant AI addition to Db2 13 is the SQL Data Insights feature. Provided as an extension to Db2, SQL Data Insights is delivered using built-in functions to deliver AI capabilities like uncovering heretofore unknown relationships in your data. Since it uses built-in functions you can use it anywhere that you use SQL!

Db2 13 offers additional AI help delivering the ability to simplify building models, Natural Language Processing (NLP), and exploiting the speed of the IBM z16 for training and querying data.

IBM z16 Synergy

The next thing that has been highly-touted is that Db2 13 takes advantage of new capabilities delivered in the IBM z16 hardware.  The new Telum chip used by the z16 mainframe provides powerful AI capabilities that Db2 uses to bolster its AI capabilities (such as SQL Data Insights). And we have already touched on that in terms of speeding up training and querying data for AI.

Db2 for z/OS is unique in that it is the only major DBMS that is designed specifically for a single operating system (z/OS) and hardware platform (IBM Z). This enables IBM (the provider of the DBMS, O/S, and hardware) to take advantage of capabilities unique to the platform, because there is no worry about supporting other platforms.

One example of this unique synergy is the ability to improve sort performance using the SORTL instruction of the IBM Z15 and z16. Additionally, the IBM z16 System Recovery Boost can minimize downtime by speeding up the performance of Db2 for z/OS restart. 

But What About BAU?

OK, so there is new AI stuff and great synergy with the IBM Z, but what about the features and functionality that make it easier to keep up with Business As Usual (BUA)? You know, things like easier administration, better performance, and so on?

Good news! There are a plethora of great new capabilities and improvements in Db2 13 for z/OS. While I cannot adequately cover them in detail today, some examples include:

• The ability to convert back-and-forth between partition-by-growth and partition-by-range Db2 table spaces. 
• Support for more concurrent threads and open data sets, as well as improved storage conditions. 
• DDF storage relief.
• Real Time Statistics (RTS) improvements.
• Many improvements to IBM Db2 utility functionality.
• Security and compliance improvements, including integration to the IBM Z Security and Compliance Center.

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. 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.

Keep checking back here as I will blog in more detail about the new capabilities of Db2 13 for z/OS over time...