Will I See You In Philadelphia for IDUG NA 2023?

Mother's Day is right around the corner and if you are a Db2 professional you now that can only mean one thing: the North American IDUG Db2 Tech Conference is nigh! It seems like every year our favorite conference competes with our Mom's for our attention. But this year the folks at IDUG have helped us out by starting the conference on Tuesday instead of the Monday after Mother's Day. So go ahead, take Mom out to dinner and stay out as late as you want. You will still have a day to recover for your trip to Philadelphia.

Yes, IDUG is back in Philadelphia this year (2023). And I am looking forward to an exciting week of learning more about my favorite DBMS and mingling with others who think likewise. There is always a world of phenomenal educational opportunities delivered at IDUG each year by IBMer, vendor experts, users, and consultants from all over the world. There will be numerous informative technical sessions on all the latest and greatest Db2 technologies, features, and related products. 

If you’ve ever attended an IDUG conference in the past then you know why I’m excited. If you haven't attended before, I'm sure you'll find a lot to occupy your interest!

And be sure to seek me out and say hello. I'll be delivering three presentations this year!

First up, on Tuesday at 2:00 pm I'll be taking a look at the trends impacting data and database systems in my session titled Database Trends 2023: Things Are Changing and You Better Keep Up!

Then on Wednesday I will be co-presenting a vendor-sponsored presentation for the fine folks at InfoTel on Intelligent Automation of Db2 Administration at 10:15 AM. I will also be spending a bit of time in the InfoTel booth so you can always stop by there to say hello or ask me a question!

And finally, on Thursday, I will be delivering a session titled Code Your Db2 Applications for Performance From the Start! This one starts at 2:00 PM and is targeted at application developers but it can be useful for DBAs and performance analysts, too! The presentation is based on my latest book, A Guide to Db2 Performance for Application Developers.

So I hope to see you in Philadelphia for a great week of education and camaraderie at this year's premier event for Db2 professionals, the IDUG Db2 Tech Conference. 

Harnessing the Power of zIIP Processors for Improved Db2 Performance and Lower Cost

As a Db2 DBA, you're constantly looking for ways to improve performance and efficiency while minimizing costs. One technology that can help achieve these goals is the zIIP (IBM System z Integrated Information Processor) processor. By offloading eligible Db2 workloads to zIIP processors, you can free up capacity on general-purpose processors and reduce costs, while improving performance.

So, what workloads are eligible for offloading to zIIP processors? XML processing, and portions of the Db2 LOAD, REORG, RUNSTATS and REBUILD utilities are among the most common. If you have third-party utilities (BMC, Broadcom, InfoTel) it is likely that they, too, will be zIIP-eligible, at least for some of their functionality.

Shifting workload to distributed/DDF is another good way to exploit zIIPs because SQL statements executed through DDF are zIIP-eligible. But most of the time DBAs have little influence on moving workload to distributed processing. This choice is typically driven by application development plans instead of DBA tuning tactics. 

Nevertheless, by understanding what type of workload is zIIP-eligible and encouraging such usage, you can offload workload to zIIP processors. Moving workload from general-purpose processors to zIIPs can possibly improve system performance and reduce costs.

You might also want to take a look at converting some of your COBOL workload to Java, if at all possible because Java programs are zIIP-eligible. Of course, this requires application developers to get involved, as well as (possibly) a conversion tool.

To fully harness the power of zIIP processors, it's important to identify eligible workloads and configure the system accordingly. Here are some tips to help you get started:

• Analyze workload usage: Use Db2 performance monitoring tools to identify workloads that are good candidates for offloading to zIIP processors. This can help you prioritize workload optimization efforts and maximize the benefits of zIIP processors. Be careful, as what is zIIP-eligible can change from version to version of Db2. As of Db2 13 for z/OS, here is the IBM documented list of zIIP-eligible Db2 "stuff."

• Configure Db2 for zIIP offload: Configure Db2 to take advantage of zIIP processors by setting the appropriate parameters and options. Consult the IBM Db2 documentation for specific guidance on configuring zIIP offload.
  
  
• Monitor and analyze performance: Use Db2 performance monitoring tools to track the performance of zIIP offloaded workloads and identify areas for further optimization. This can help you continually improve performance and efficiency over time.

By effectively utilizing zIIP processors for Db2 workloads, you can achieve significant cost savings and performance improvements on IBM Z mainframe systems. Don't let this powerful technology go to waste – start exploring the benefits of zIIP processors today!

A Db2 for z/OS DBA Poem

I'm a hip-hopping monster of the CRT,

All the programmers here, they be envying me,

I'm a DBA, a master of data,

Work with Db2 and I diss all the haters!

I strive every day to give smooth operation,

And to keep the data in perfect formation.

My eyes are trained to spot any trouble,

To fix it before the data turns to rubble.

My hands deftly skip over the keyboard, 

Tuning and tweaking all the inefficient code.

I know all the ins and outs of Db2,

Every command, every option, every ZPARM, I do.

My skills are honed by years of practice,

Through all the challenges, I've gained my status.

My expertise and skills are sought by many,

But few can match all my skills, or any.

I'm a Db2 DBA, indeed I am...

A guardian of data, and a z/OS fan!

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.