Concurrency vs. Throughput in Db2

As Db2 DBAs we often find ourselves chasing two elusive targets: speed and capacity. We want our systems to handle everything all at once, and we want it done yesterday. But as database architectures evolve and workloads grow increasingly complex, DBAs must constantly manage the delicate, often misunderstood relationship between two fundamental metrics: Concurrency and Throughput.

It’s easy to mistake one for the other, or to assume that maximizing one automatically boosts the other. But the two are not the same. Let’s break down what these terms really mean for your Db2 for z/OS subsystems and why balancing them is the key to a healthy production environment.

Defining the Duo

Before we look at how they interact, let’s establish a clear baseline for both concepts.

• Concurrency is the database's ability to handle multiple interactive sessions, transactions, or users at the exact same time. It’s a measure of simultaneous access. Think of it as the number of lanes on a highway. More lanes typically mean that more cars can be on the road simultaneously.

• Throughput is the actual amount of work successfully completed by the database per unit of time (e.g., transactions per second, rows processed per minute). If concurrency is the number of lanes on the highway, throughput is the number of cars that actually pass through the toll booth every hour.

In an ideal world, as concurrency increases, throughput rises right along with it. But database systems don't operate in a vacuum. They are bound by physical limitations: CPU, memory, I/O bandwidth, and, most importantly, locking and latching mechanisms.

The Concurrency Curve: When More Becomes Less

If you increase the number of concurrent threads entering your Db2 subsystem, throughput will generally scale linearly, at least up to a point. But eventually you hit a tipping point that is referred to as the "knee of the curve."

Beyond this point, adding more concurrent users doesn't get more work done. Instead, it breeds contention.

When too many transactions fight for the same resources, Db2 spends more time managing the queue than doing actual work. You’ll start to see symptoms like:

• High internal latch contention.

• Increased lock wait times and, worst-case scenario, spikes in deadlocks and timeouts.

• Elongated class 3 suspension times in your accounting reports.

At this stage, you haven't plateaued your throughput... you’ve actually degraded it. You are burning CPU just to manage the traffic jam.

Db2 Mechanisms to Balance the Scale

Maximizing throughput while maintaining healthy concurrency requires an understanding of Db2’s internal mechanics. Here are the core areas where DBAs can tip the scales back in their favor:

1. Lock Avoidance and Isolation Levels

Locks are the primary gatekeepers of concurrency. The best way to improve concurrency is to avoid locking altogether when safe. Ensure your application packages are bound with CURRENTDATA(NO) to allow Db2 to use lock avoidance techniques. Furthermore, when appropriate, consider dirty reads. If an application can tolerate reading uncommitted data, UNCOMMITTED READ (UR) blows the doors wide open for concurrency because it doesn't acquire read locks.

Note: it has been my experience that far too many programs and SQL statements use UR. Although removing locks with UR improves concurrency, it can damage data quality if used inappropriately.

2. Thread Management (MAX REMOTE ACTIVE / MAX ONLINE)

It is not an uncommon misconception that letting every single request hit the engine at once improves throughput. It really doesn’t. Leveraging Db2’s thread pooling and setting intelligent limits on MAX REMOTE ACTIVE (for distributed workloads via DDF) ensures that work is queued efficiently before it can thrash the engine.

3. Page-Level vs. Row-Level Locking

Row-level locking (LOCKSIZE ROW) sounds like a silver bullet for concurrency because it minimizes the footprint of a lock. However, it comes with a steep price tag: you might experience considerable CPU overhead for lock acquisition and management. If your throughput is CPU-bound, using LOCKSIZE PAGE (combined with smart page splitting and small row sizes) might actually increase your overall throughput by freeing up CPU cycles.

The trade-off between row and page locks depends on understanding the nature of the application accessing the data and its current execution profile. If there are contention problems when accessing a table space that is currently set to LOCKSIZE PAGE you might consider altering it to LOCKSIZE ROW and then monitoring the impact on performance, resource consumption, and concurrency.

The Bottom Line

Mainframes are designed to process massive, mind-boggling volumes of concurrent data better than almost anything else on earth. But the laws of database physics still apply.

To achieve maximum throughput, you cannot simply crank the concurrency dial to eleven and hope for the best. You must monitor your buffer pools, optimize your indexing to minimize table scans, design your applications for short commit scopes, and precisely configure your subsystem parameters.

Remember: True database performance isn't about how many transactions you let through the door at once; it's about how fast you can successfully get them out the exit.

The Mullins 5‑Step Performance Diagnosis Framework

In the complex ecosystem of enterprise mainframes and hybrid cloud environments, performance tuning often feels like chasing ghosts in the machine. However, achieving peak efficiency in systems like Db2 doesn't require magic. No, it requires a repeatable, disciplined methodology.

Whether you are managing massive digital transactions or high-concurrency analytics, the following 5-Step Performance Diagnosis Framework provides a blueprint for resolving the vast majority of enterprise performance bottlenecks. This framework consists of the following 5 steps...

1. Analyze workload patterns
2. Review SQL and access paths
3. Inspect locking and contention
4. Evaluate buffer pool efficiency
5. Check for recent changes

...and it can help to resolve the majority of enterprise database/application performance issues.

Let's examine each step in a little more detail.

1. Analyze Workload Patterns

Before diving into tweaking and turning technical knobs, you first have to understand the nature of the demand. Is the performance degradation systemic or isolated? 

• Systemic issues are environment-wide or application-wide degradations. They affect a broad range of users and processes simultaneously, regardless of the specific task being performed.
  
  
• Isolated issues are localized to a specific transaction, query, or user group. While one part of the system is failing to meet service level agreements (SLAs), the rest of the environment may be operating at peak efficiency.

By differentiating between these two types of problems, you can avoid the "shotgun approach" to tuning, such as changing global settings (e.g. DSNZPARMs) to fix a local problem. This often results in unintended consequences for the rest of the enterprise.

When you analyze workload patterns, you can differentiate between a runaway batch job and a steady-state increase in transaction volume. Understanding the "shape" of your data traffic is the first step in identifying if the issue is a sudden spike or a gradual trend toward resource exhaustion.

2. Review SQL and Access Paths

The most significant performance gains are almost always found in the SQL. A single poorly written query or a sub-optimal access path chosen by the optimizer can cripple an application. Reviewing the EXPLAIN output to verify index usage, join methods, and sort requirements is critical. In a world of evolving database engines, ensuring your SQL remains efficient is a primary responsibility of the modern DBA.

Using tools that create a visual diagram and "explanation" of your EXPLAIN output can greatly improve the efficiency of your SQL tuning efforts. The primary tool used by Db2 for z/OS sites these days is IBM Db2 Administration Foundation for z/OS, which is the modern, browser-based replacement for IBM Data Studio. It is built on the Zowe framework (open source) and provides a streamlined interface for mainframe DBAs. It displays the SQL execution plans as a graphical tree, showing table scans, index lookups, and join methods.

Consider reviewing and using my recently published blog containing my SQL Review Workflow.

3. Inspect Locking and Contention

In a high-availability Parallel Sysplex or clustered environment, concurrency is king. However, where there is concurrency, there is the potential for contention. Inspecting lock wait times, deadlocks, and timeout statistics can reveal hidden bottlenecks where processes are fighting over the same resources. Tuning your commit frequency and isolation levels can often alleviate these "soft" performance hits that don't show up in CPU metrics alone.

Indeed, one of the most common problems I find in my consulting engagements is what I call Bachelor Programming Syndrome or "Fear of COMMITing."

Unless you plan for, and issue regular COMMITs in your programs that access Db2 data, you will be causing locking problems. It is important for every programmer to issue COMMIT statements in all application programs where data is modified (INSERT, UPDATE, and DELETE). 

Failing to code COMMITs in a data modification program
is what I like to call Bachelor Programming Syndrome...
in other words, Fear of Committing.

The COMMIT externalizes the modifications that occurred in the program since the beginning of the program or the last COMMIT. A COMMIT ensures that all modifications have been physically applied to the database, thereby ensuring data integrity and recoverability. Without COMMITs, data remains locked making it inaccessible to others, eventually causing timeout failures. 

 4. Evaluate Buffer Pool Efficiency

Data that stays in memory is data that moves fast. Evaluating buffer pool hit ratios and asynchronous I/O rates is essential for ensuring that your most frequently accessed "hot" data isn't being constantly paged out to disk. Efficient memory management reduces the I/O overhead that often serves as the hard ceiling for database performance.

When allocating Db2 buffer pools, keep some basic rules of thumb in mind:

• Don't allocate everything to a single buffer pool (e.g., BP0); use a multiple buffer pool strategy.
• Explicitly specify a buffer pool for every table space and index.
• Isolate the Db2 Catalog in BP0 (and BP8K0 as of V8); put user and application Db2 objects into other buffer pools.
• It is a best practice to separate indexes from table spaces with each in their own dedicated buffer pools.
• Consider isolating heavily hit data into its own buffer pool to better control performance.
• Consider isolating sorts into a single buffer pool and tuning it for mostly sequential access (e.g. BP7).
• Consider separating Db2 objects into separate buffer pools that have been configured for sequential vs random access.
• Consider pinning small, high usage tables in memory by dedicating a buffer pool to each and using the PGSTEAL(NONE) option. 

5. Check for Recent Changes

The most common cause of a performance "event" is change. Whether it’s a recent REORG, a change in Db2 function level (or even maintenance being applied), or a modified application module, you must look at the delta between "yesterday" and "today". Always audit the most recent modifications to the environment to see if a configuration shift has triggered an unintended consequence in the execution plan.

Summary: While AI-driven tools are beginning to assist in database administration, the foundational logic of performance tuning remains rooted in these core pillars. By systematically moving through workload, SQL, locking, memory, and change management, you can stabilize and optimize even the most demanding enterprise environments.

 

How has your team integrated these diagnostic steps into your current hybrid cloud monitoring workflow?