5 Big Concerns of Modern IT When Using Db2 for z/OS

Db2 for z/OS is an entrenched solution for managing data at the world's largest organizations. It is a strong, reliable DBMS and I wrote about its strength recently on the blog (here). You really cannot go wrong using Db2 for z/OS for mission-critical workloads.

That said, there are concerns and issues facing organizations using Db2 for z/OS. One of the biggest concerns with Db2 for z/OS today is managing the cost and complexity of maintaining mainframe environments while still delivering high availability and performance. 

As such, here are 5 specific concerns facing large organizations using Db2 for z/OS today:

1. Skill Shortages: Many mainframe experts, especially those with deep Db2 for z/OS knowledge, are approaching retirement, creating a significant skills gap. The lack of trained professionals has made it challenging to manage and maintain Db2 for z/OS systems effectively.

2. Cost of Licensing and Maintenance: Mainframe systems come with substantial licensing costs. Many organizations are looking for ways to optimize usage or even repatriate workloads to more cost-effective platforms, where feasible, to reduce operational expenses. Whether or not such changes result in "actual" cost reductions is unfortunately irrelevant as many executives believe it will regardless of reality and studies to the contrary.

3. Integration with Modern Architectures: As companies adopt cloud, big data, and other modern architectures, integrating Db2 for z/OS with these systems can be complex and costly. Many seek seamless data integration between Db2 on mainframes and newer platforms like data lakehouses, which involves architectural and technological challenges.

4. Automation and DevOps Compatibility: Modern IT environments emphasize agility, continuous integration, and deployment, but the mainframe environment traditionally doesn’t integrate well with DevOps practices. Nevertheless, many companies are pushing for Db2 automation tools and integration with DevOps workflows to streamline operations and reduce manual workloads... and DevOps is being successfully deployed by mainframe organizations today using Zowe and other traditional DevOps tooling.

5. Performance and Availability: High performance and availability are always top concerns, especially as organizations process more data and need to meet stringent SLAs. Handling lock contention, optimizing query performance, and scaling resources efficiently continue to be challenges. But, to be fair, these are challenges with many DBMS implementations, not just Db2 for z/OS.

Organizations are adopting several strategies to address the challenges with Db2 for z/OS and ensure their mainframe environments remain relevant and efficient:

1. Workforce Development and Knowledge Transfer: To counter skill shortages, organizations are investing in training and upskilling initiatives for new IT staff, partnering with universities, or using mentoring programs to transfer knowledge from retiring mainframe experts to newer employees. Additionally, some companies are leveraging consulting firms or managed services providers with mainframe expertise to fill gaps temporarily.

2. Cost Optimization with Usage Analytics: Companies are using detailed workload and resource monitoring tools to optimize Db2 for z/OS usage, identify inefficient processes, and reduce costs. This includes tuning queries, scheduling batch jobs during off-peak hours, and leveraging IBM’s Workload Manager (WLM) to prioritize workloads based on business needs.

3. Hybrid Cloud and Data Lakehouse Integrations: To manage integration with modern architectures, organizations are implementing hybrid cloud strategies and data lakehouses that can interface with Db2 for z/OS. Tools such as IBM Db2 Analytics Accelerator allow data stored on Db2 for z/OS to be offloaded to faster, scalable platforms, enabling integration with big data and analytics environments without entirely migrating off the mainframe.

4. Automation and DevOps Integrations: Organizations are investing in DevOps and automation tools compatible with Db2 for z/OS, such as IBM UrbanCode and mainframe DevOps solutions from other ISVs such as Broadcom and BMC Software. By automating routine tasks like provisioning, patching, and deploying schema changes, organizations can adopt more agile, efficient processes. Integrating Db2 for z/OS with CI/CD pipelines helps streamline development workflows, bridging mainframe operations with modern DevOps practices. For more details on integrating Db2 for z/OS into DevOps, consult this blog post that highlights several posts I wrote on the topic!

5. Mainframe Modernization with AI and Machine Learning: Using AI and machine learning to optimize Db2 for z/OS operations is becoming common. AI-based monitoring tools, such as IBM’s Watson AIOps, can predict system issues and detect anomalies to prevent downtime. Machine learning algorithms can also be used for capacity planning, workload optimization, and tuning Db2 performance parameters, helping reduce manual intervention.

6. Resilience and High Availability Improvements: For performance and availability, companies are implementing high-availability solutions like IBM Geographically Dispersed Parallel Sysplex (GDPS) to ensure continuous uptime. They’re also using backup automation and disaster recovery solutions tailored for Db2 to meet stringent SLAs and minimize downtime in case of failures.

By combining these strategies, organizations are better equipped to manage the costs, complexity, and skills required to maintain and modernize Db2 for z/OS environments in today’s rapidly evolving IT landscape.

Understanding Lock Escalation: Managing Resource Contention

Ensuring efficient data access while maintaining data integrity is critical to both performance and stability. One of the mechanisms Db2 employs to manage this balance is lock escalation. Though this feature is essential when managing large numbers of locks, improper handling can lead to performance bottlenecks. Understanding lock escalation and how it impacts your Db2 environment is crucial for database administrators (DBAs) seeking to optimize operations.

What Is Lock Escalation?

Lock escalation is Db2’s method of reducing the overhead associated with managing numerous individual row or page locks. Instead of holding thousands of fine-grained locks, Db2 “escalates” these to coarser-grained table or table space locks. This happens automatically when a session’s lock usage exceeds a predefined threshold.

The primary goal of lock escalation is to reduce the system resources spent on tracking and maintaining a large number of locks. Without escalation, too many locks could overwhelm system memory or negatively impact performance due to the lock management overhead. Escalating to a table (space) lock allows Db2 to control resource consumption and avoid these issues.

When Does Lock Escalation Occur?

There are two limits to be aware of. The first is NUMLKTS, which specifies the maximum nunber of locks a process can hold on a single table space. This is the default and it can be overridden in the DDL of a tablespace using the LOCKMAX clause. When NUMLKTS (or LOCKMAX) is exceeded, Db2 will perform lock escalation.

The second is NUMLKUS, which specifies the maximum number of locks a process can hold across all table spaces. When a single user exceeds the page lock limit set by the Db2 subsystem (as defined in DSNZPARMs), the program receives a -904 SQLCODE notification. The program can respond by issuing a ROLLBACK and generating a message suggesting that the program be altered to COMMIT more frequently (or use alternate approaches like executing a LOCK TABLE statement).

Lock escalation may also occur due to the lock list or lock table approaching its capacity. In such cases, Db2 may escalate locks to prevent the system from running out of resources.

Additionally, keep in mind that as of Db2 12 for z/OS FL507, there are two new built-in global variables that can be set by application programs to control the granularity of locking limits.

The first is SYSIBMADM.MAX_LOCKS_PER_TABLESPACE and it is similar to the NUMLKTS parameter. It can be set to an integer value for the maximum number of page, row, or LOB locks that the application can hold simultaneously in a table space. If the application exceeds the maximum number of locks in a single table space, lock escalation occurs.

The second is SYSIBMADM.MAX_LOCKS_PER_USER and it is similar to the NUMLKUS parameter. You can set it to an integer value that specifies the maximum number of page, row, or LOB locks that a single application can concurrently hold for all table spaces. The limit applies to all table spaces that are defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. 

These new FL507 options should be used sparingly and only under the review and control of the DBA team.

The Impact of Lock Escalation

While lock escalation conserves system resources, it can also lead to resource contention. By escalating locks from rows or pages to a table-level lock, Db2 potentially increases the chances of lock contention, where multiple transactions compete for the same locked resource. This can have a few side effects:

• Blocking: When an entire table is locked, other transactions that need access to that table must wait until the lock is released, even if they only need access to a small portion of the data.
• Deadlocks: With more coarse-grained locks, the likelihood of deadlocks can increase, especially if different applications are accessing overlapping resources.
• Performance degradation: While escalating locks reduces the overhead of managing many fine-grained locks, the side effect can be a performance hit due to increased contention. For systems with high concurrency, this can result in significant delays.

Managing Lock Escalation

A savvy DBA can take steps to minimize the negative impacts of lock escalation. Here are some strategies to consider:

1. Monitor Lock Usage: Db2 provides tools like DISPLAY DATABASE and EXPLAIN to track locking behavior. Regularly monitor your system to understand when lock escalation occurs and which applications or tables are most affected.

2. Adjust Lock Thresholds: If escalation is happening too frequently, consider adjusting your LOCKMAX parameter. A higher threshold might reduce the need for escalation, though be mindful of the system’s lock resource limits. Additionally, consider the FL507 built-in global variables for difficult to control situations. 

3. Optimize Application Design: Poorly optimized queries and transactions that scan large amounts of data are more prone to trigger lock escalation. Review your applications to ensure they are using indexes efficiently, and minimize the number of locks held by long-running transactions.

4. Partitioning: Partitioning larger tables can help mitigate the effects of lock escalation by distributing locks across partitions.

5. Use of Commit Statements: Frequent commits help release locks, lowering the risk of escalation. Ensure that programs are committing frequently enough to avoid building up large numbers of locks. A good tactic to employ is parameter-based commit processing, wherein a parameter is set and read by the program to control how frequently commits are issued. This way, you can change commit frequency without modifying the program code.

Conclusion

Lock escalation is a necessary mechanism in Db2, balancing the need for data integrity with resource efficiency. However, it can introduce performance issues if not properly managed. By understanding when and why escalation occurs, and taking proactive steps to optimize your environment, you can minimize its negative impact while maintaining a stable, efficient database system.

As with many aspects of Db2, the key lies in careful monitoring, tuning, and optimization. A well-managed lock escalation strategy ensures that your system remains responsive, even under heavy workloads, while preserving the data integrity that Db2 is known for.

Db2 for z/OS: The Performance and Management Champion!

Usually, posts I write for this blog focus on technical details, tips, and techniques for better using and optimizing your experience with Db2. Today, I want to do something a little different. You see, I am a big fan of Db2 for z/OS, and I do not see it getting the press, or the accolades that I think it is due. So I am going to use my platform to shout out the performance benefits of Db2 for z/OS.

When it comes to performance, nothing beats Db2 for z/OS. This mainframe database has been setting the standard for decades, delivering unmatched speed and efficiency for mission-critical applications. Let's explore some of the reasons why Db2 for z/OS is the performance champion.

Hardware Acceleration

• z/Architecture: Db2 for z/OS takes full advantage of the powerful z/Architecture, which includes specialized hardware for database operations. This hardware acceleration provides a significant performance boost for tasks like query processing and data loading.
• Storage Subsystem: The mainframe's storage subsystem is designed for high performance and reliability. With features like z/Hyperlink, data compression, and flash storage, Db2 for z/OS can access data quickly and efficiently.
• IDAA: IBM Db2 Analytics Accelerator is a high-performance, in-memory database appliance designed to accelerate analytic workloads. It's optimized for large-scale data analysis tasks, providing significant speedups compared to traditional disk-based databases. By leveraging solid-state drives (SSDs) and advanced hardware architecture, IDAA can handle complex queries and data manipulations with exceptional efficiency. This makes it ideal for applications requiring real-time analytics, data warehousing, and big data processing.

Database Optimization

• Query Optimization: Db2 for z/OS has a sophisticated query optimizer that can automatically select the most efficient execution plan for your queries. This ensures that your applications run as fast as possible.
• Data Compression: Db2 for z/OS supports data compression, which can reduce storage requirements and improve performance. By compressing data, Db2 can reduce the amount of data that needs to be read and processed.
• Parallel Processing: Db2 for z/OS can take advantage of multiple processors to perform tasks in parallel. This can significantly improve performance for large workloads.
• AI: IBM Db2 AI for z/OS integrates autonomics to simplify database management efforts. Using machine learning and AI, it can help improve operational performance and maintain Db2 for z/OS efficiency and health while enhancing Db2 for z/OS performance, reliability and cost effectiveness–even under the most demanding circumstances.

Workload Management

• Resource Allocation: Db2 for z/OS provides powerful tools for managing resources and ensuring that your database applications get the resources they need to perform optimally.
• Workload Balancing: Db2 can automatically balance workloads across multiple systems to ensure that resources are used efficiently.
• WLM: Workload Manager is an integrated, critical component of z/OS that is used for optimizing the performance and resource utilization of Db2 for z/OS. It provides a comprehensive framework for managing workloads across the mainframe environment, ensuring that Db2 applications receive the resources they need to perform optimally.

Data Sharing and Parallel Sysplex

Finally, Data Sharing using IBM Z Parallel Sysplex confers a significant advantage onto Db2 for z/OS in that it can enhanced availability by providing inherent redundancy, as multiple subsystems can access the same data. This helps to mitigate the impact of hardware failures or system outages. And in case of a disaster, data sharing can facilitate rapid recovery by allowing applications to access data from a different subsystem.

Furthermore, Data Sharing enhances scalability by enabling workloads to be distributed across multiple subsystems, improving scalability and preventing bottlenecks. It facilitates simpler growth: as data volumes and application demands increase, data sharing can help to accommodate growth without requiring significant hardware investments.

And Data Sharing can improve performance. By allowing multiple Db2 subsystems to access the same data without requiring individual copies, data sharing significantly reduces I/O operations, leading to improved performance. And with data readily available to multiple subsystems, queries can be executed more quickly, resulting in faster response times for applications.

So, IBM Z data sharing on Db2 offers a range of benefits, including improved performance, enhanced availability, increased scalability, reduced costs, and simplified management. These benefits make it a valuable feature for organizations that require high-performance, reliable, and scalable database solutions.

Real-World Results

Organizations around the world rely on Db2 for z/OS to power their most critical applications. From financial services to healthcare, Db2 has proven its ability to deliver the performance and reliability that businesses need to succeed.

So, if you're looking for a database that can handle your most demanding workloads and deliver exceptional performance, Db2 for z/OS is the way to go.

Highlights of the 2024 NA IDUG Db2 Tech Conference

Just a quick blog post today to let my readers know that I have written an overview of the 2024 IDUG Db2 Tech Conference in Chartlotte, NC this past June. 

The overview was written for the SHARE'd Intelligence blog, which is the official publication of SHARE. It offers news and education on enterprise solutions, and you would be wise to bookmark the site to keep up with the content shared there. 

The post I wrote is titled Riding the Waves of Knowledge at the IDUG Db2 Tech Conference. I hope you'll check it out, read my perspective, and share your thoughts on it here... and make plans to attend next year's IDUG event in Atlanta!

Coding Db2 Applications for Performance - Expert Videos Series

Today's blog post is to share with my readers that I have partnered with Interskill Learning and produced a series of videos in the Expert Video Series on how to code Db2 applications for performance.

My regular readers know that application performance is a passion of mine. You may also have read my recent book on the topic, A Guide to Db2 Performance for Application Developers. But if you are looking for videos to guide you through the process optimizing your application development for Db2, look no further than the six-part series I recorded for Interskill Learning, Coding Db2 Applications for Performance.

You do not need in-depth pre-existing knowledge of Db2 to gain insight from these video lessons. The outline of the six courses are as follows:

 Db2 Coding – Defining Database Performance

• Providing a Definition
• The Four Components
• Diving a Little Deeper

Db2 Coding – Coding Relationally

• What is Relational?
• Relational vs. Traditional Thinking
• What Does It Mean to Code Relationally?
• Unlearning Past Coding Practices

Db2 Coding – General SQL and Indexing Guidelines

• Types of SQL
• SQL Coding Best Practices
• Indexes and Performance
• Stages and Clustering

Db2 Coding – Coding for Concurrent Access

• Introduction to Concurrency
• Locking
• Locking Duration and Binding
• Locking Issues and Strategies
• Query Parallelism

Db2 Coding – Understanding and Reviewing Db2 Access Paths

• Single Table Access Paths
• Multi-table Access Paths
• Filter Factors
• Access Paths and EXPLAIN

Db2 Coding – SQL Coding Tips and Techniques

• Avoid Writing Code
• Reusable Db2 Code
• Dynamic and Static SQL
• SQL Guidelines
• Set Operations

So if you are looking for an introduction to Db2 performance or want to brush up on the fundamentals of coding for performance, look no further. Check out this series of videos on Coding Db2 Applications for Performance from Interskill Learning (featuring yours truly)!

Note that Interskill Learning also offers other categories of training in their Expert Video series including systems programming, quantum computing, and pervasive encryption.