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. 
   

Asking ChatGPT About the Advantages of Db2 for z/OS over Oracle

If you read my blog post from yesterday, you know that I have been playing around with ChatGPT, the latest AI technology that is sweeping the headlines.

Yesterday I was asking it about the mainframe, but today I thought I'd ask ChatGPT about Db2 and Oracle, specifically:

In what ways is IBM Db2 for z/OS better than Oracle?

Yes, I know that is a loaded question and that the results would likely be different if I swapped the order of Db2 for z/OS and Oracle. Nevertheless, I liked the results that ChatGPT generated, so I figured I'd share them with everybody here!

So here is ChatGPT's reply:

"Both IBM Db2 for z/OS and Oracle are mature and widely used relational database management systems, and each has its strengths and weaknesses. However, here are some ways that IBM Db2 for z/OS might be considered better than Oracle:

Transaction Processing: Db2 for z/OS is known for its high performance and ability to handle large transaction processing workloads. It is optimized for OLTP (Online Transaction Processing) applications and can handle high concurrency and high throughput.

Security: IBM has a long-standing reputation for security, and Db2 for z/OS is no exception. It has a robust set of security features, including encryption, role-based access control, and auditing capabilities that help ensure that sensitive data is protected.

Cost: Db2 for z/OS can be less expensive to operate than Oracle for some use cases. It is designed to run on IBM mainframes, which are known for their scalability and efficiency, allowing organizations to reduce hardware and software costs. Additionally, because IBM mainframes are known for their reliability, uptime, and availability, they can help reduce costs associated with system downtime or data loss.

Integration: Db2 for z/OS can integrate with other IBM products and services, such as CICS (Customer Information Control System) and IMS (Information Management System). This can be particularly beneficial for organizations that already have an IBM infrastructure in place.

Built-in Analytics: Db2 for z/OS includes built-in analytics features, such as IBM Db2 Analytics Accelerator, which can help organizations to run complex analytics queries on large datasets. This can be particularly useful for organizations that need to process large amounts of data quickly and efficiently. 

It is worth noting that Oracle also has its own strengths and advantages, such as its support for a wide range of platforms and its focus on database consolidation. Ultimately, the choice between Db2 for z/OS and Oracle will depend on the specific needs and requirements of an organization."

Again, a quite reasonable response! I think the thing that jumped out at me the most here is the "Cost" section. As a grizzeled, old mainframer I am always ready to fight back against unfounded claims that the mainframe option is always the more espensive one. But ChatGPT didn't make that mistake! Maybe because Oracle has a reputation of being expensive, too?

At any rate, I look forward to asking ChatGPT for help in my future endeavors. 

AI and ChatGPT Tell You Why You Should Use a Mainframe

ChatGPT has been getting a lot of attention for its ability to formulate reasonable responses to questions and inquiries. There have even been examples of ChatGPT being able to pass college exams and write up readable articles.

ChatGPT is a chatbot developed by OpenAI. It was launched in November pf 2022. It is built on top of OpenAI's GPT-3 family of large language models and it has been fine-tuned using both supervised and reinforcement learning techniques. 

So it is a somewhat practical example of AI being applied to a common proglem... communication.

Being the mainframe zealot that I am, I decided to ask ChatGPT why I should use a mainframe. I wasn't sure what to expect, but I am delighted with the response that I got. 

Me: Why should I still use a mainframe?

ChatGPT: There are several reasons why you might still want to use a mainframe for certain applications:

1. High Reliability: Mainframes are designed to handle massive workloads and to provide high levels of reliability and availability. They are built with redundant components and features that allow them to recover quickly from failures, making them ideal for mission-critical applications.
2. Security: Mainframes are known for their robust security features. They have been used in industries such as finance, healthcare, and government for decades because they offer features such as hardware encryption, advanced access controls, and secure connectivity options.
3. Scalability: Mainframes can scale vertically to handle large amounts of data and processing power. They can also scale horizontally by adding more processors or servers to a mainframe cluster, making them a good fit for organizations with rapidly growing or fluctuating workloads.
4. Cost-Effective: While mainframes are expensive to purchase, they can provide a cost-effective solution for certain applications. Mainframes are designed to handle large workloads with high efficiency, and they can consolidate multiple applications onto a single platform, reducing the need for additional hardware and software.
5. Legacy Systems: Many organizations have critical legacy applications that were designed to run on mainframes. Replacing these systems can be expensive and time-consuming, and in some cases, it may not be feasible to migrate these applications to modern platforms.
6. Integration with Modern Technologies: Many mainframe systems now offer modern connectivity options, such as APIs and web services, which make it easier to integrate them with newer technologies and applications.

In summary, while mainframes are no longer the primary platform for many organizations, they still offer unique benefits and advantages for certain applications. Mainframes provide high levels of reliability, security, scalability, and cost-effectiveness, making them an attractive option for organizations with specific needs and requirements.

This seems like a  reasonable response to me. What do you think? With technology this good, I can only imagine how much better it will get as the models are honed over time.

Escaping Single Quotes in Db2 SQL Statements

Every now and then I write about some things that may seem to be basic to old-timers, but can be very confusing to developers the first time they encounter them. Today's post is an example.

How do you handle a text string with a single quote in it? For example, something like this:

    Today is Craig's birthday (not really)

Well, the first thing to corroborate is that text in Db2 SQL is delimited by single quotes. So the first attempt people tend to make is something like this:

    'Today is Craig's birthday (not really)'

But they also quickly discover that this does not work. There are three single quotes here: one at the beginning, one between the "g" and the "s", and one at the end. So Db2 will choke on it and you will get an error.

The trick is knowing the escape character. An "escape" character is used to tell Db2 that the next character is actually part of the text string, not a delimiter. For Db2, the escape is to double up the single quote (note that other DBMSes may use a different technique). So, if we want to use our sample text phrase in Db2 SQL, we'd code it with the escape character, as follows:

    'Today is Craig''s birthday (not really)'

The single quote denoting the possessive for Craig's is doubled. This tells Db2 to treat the single quote as part of the text. So we could write SQL like this:

    SELECT 'Today is Craig''s birthday (not really)'
    FROM   SYSIBM.SYSDUMMY1;

This will return the text that we want:

    Today is Craig's birthday (not really)

This works even if there are multiple single quotes within the text. For example, consider if we want to use the following text in SQL:

    Today is not Craig's birthday but it is Beth's

We could write the SQL like this:

    SELECT 'Today is not Craig''s birthday but it is Beth''s'
    FROM   SYSIBM.SYSDUMMY1;

And it also works even at the end of the text string, even though it starts to look somewhat confusing. For example, what is we want to use the following text in SQL?

    I like the book 'A Confederacy of Dunces'

In that case, we still double up the single quotes, like so:

    SELECT 'I like ths book ''A Confederacy of Dunces'''
    FROM   SYSIBM.SYSDUMMY1;

And that will return our desired text.

I hope this short treatise on how to use the escape for single quotes in Db2 SQL has been helpful!

Seasons Greetings 2022!

Just a short blog entry today wishing all my regular readers a very happy holiday season! 

And to let everbody know that I will not be posting anything new between now and the end of the year...

But be sure to check back again next year (2023) as I continue to write about Db2, mainframes, and database issues that impact us all!