Db2 for z/OS Lock Escalation: When Fine-Grained Locking Becomes a Problem

One of the primary goals of Db2 for z/OS locking is to maximize concurrency while maintaining data integrity. In a perfect world, every application would acquire only the locks it needs, hold them for the shortest possible duration, and release them promptly. But the real world is not always perfect. Sometimes an application acquires so many locks that Db2 decides it is more efficient to replace those many locks with a single, larger lock. This process is known as lock escalation.

Lock escalation is one of those Db2 behaviors that every DBA should understand because when it occurs unexpectedly, it can have a significant impact on application performance and availability.

What Is Lock Escalation?

Lock escalation occurs when Db2 replaces numerous row, page, or LOB locks with a single table space or partition lock.

Imagine an application updating hundreds of thousands of rows. Instead of managing and tracking an enormous number of individual locks, Db2 may determine that maintaining all of those locks consumes too much storage and processing overhead. Rather than continue managing thousands of granular locks, Db2 escalates them to a larger lock.

For example:

• 50,000 row locks become one table space lock.
• Thousands of page locks become one partition lock.
• Many LOB locks become a higher-level lock.

From Db2's perspective, lock escalation can reduce lock management overhead. From the application's perspective, however, lock escalation reduces concurrency because other applications may now be blocked from accessing a much larger portion of the data.

Why Does Db2 Escalate Locks?

Db2 lock escalation is generally driven by one of two conditions:

LOCKMAX Threshold Reached

The most common cause is the LOCKMAX parameter. LOCKMAX is set at the tablespace level and it defines the maximum number of page, row, or LOB locks that can be held for a table space or partition before Db2 attempts escalation.

The value can be:

• A specific number (ranging from 0 to 2,147,483,647)
• 0 (means lock escalation is disabled)
• SYSTEM (use subsystem default NUMLKTS)

You can find the value of LOCKMAX in the Db2 Catalog by reviewing the MAXROWS column of SYSIBM.SYSTABLESPACE.

When the threshold is exceeded, Db2 attempts escalation. For example, if LOCKMAX is set to 10,000 and an application acquires its 10,001st lock, Db2 attempts to escalate.

Lock Storage Shortage

Db2 also monitors lock storage consumption.

Even if LOCKMAX is not reached, Db2 may escalate locks when lock storage resources become constrained. This protects the subsystem from excessive lock memory consumption.

In these cases, escalation is a defensive measure designed to preserve overall system stability.

What Happens During Escalation?

Suppose an application holds 25,000 row locks and that is also the value of LOCKMAX. When the next row lock is requested while updating a table Db2 attempts to replace all 25,000 + 1 locks with a higher-level lock, typically:

• Exclusive table space lock for updates
• Share table space lock for read activity

If Db2 successfully acquires the higher-level lock:

1. The individual locks are released.
2. The table space or partition lock is acquired.
3. Processing continues.

The problem is that other applications may now be blocked from accessing data that previously would have remained available through row-level concurrency. A single poorly designed batch job can suddenly become a bottleneck for dozens or hundreds of online transactions.

Why Lock Escalation Can Be Dangerous

Many DBAs think lock escalation is merely a locking event. In reality, it is often an application design warning signal.

Consider a CICS transaction that normally updates ten rows. No issue. Now consider a batch job (running concurrently with the transactions) that updates five million rows under one unit of work. Without frequent commits, the job accumulates massive numbers of locks. Eventually escalation occurs. The consequences may be dire, including:

• Increased lock contention
• Application timeouts
• Deadlocks
• Reduced concurrency
• Unexpected outages for online users

In production environments, lock escalation frequently becomes visible only after users begin reporting delays.

Common Causes

Over the years, I have found that lock escalation is usually symptomatic of one or more underlying issues. And it is usually an application design/coding issue.

Infrequent Commits

Not issuing sufficient (or any COMMITs) is probably the most common cause. Applications that process large volumes of data without committing work accumulate locks continuously. And the locks are not released until a COMMIT is issued (or the program ends).

A batch job committing every 100,000 rows will typically consume far more lock resources than one committing every 1,000 rows.

I have written about Bachelor Programming Syndrome before (check the link), which is just my way of saying don’t fear committing. In general, I recommend that you plan to issue COMMITs in every batch program. You can structure the logic so that the COMMIT processing is contingent on a parameter passed to the program. This approach enables an analyst to modify COMMIT frequency, or even turn off COMMIT processing, as the concurrency needs of the application varies.

Mass Updates and Deletes

Large-scale data modification operations naturally acquire large numbers of locks.

Examples include:

DELETE FROM CUSTOMER_HISTORY
WHERE CREATE_DATE < CURRENT DATE - 5 YEARS;

or

UPDATE ACCOUNT
SET STATUS = 'I'
WHERE LAST_ACTIVITY_DATE < CURRENT DATE - 3 YEARS;

These operations can quickly exceed escalation thresholds.

Poor Access Paths

Inefficient access paths may cause Db2 to examine and lock far more rows or pages than intended. An application expected to update 100 rows might actually scan millions due to a missing or ineffective index.

Excessively Large Units of Work

The larger the unit of work, the greater the lock accumulation. Applications that hold locks for extended periods are prime candidates for escalation. Again, parameterized control of COMMIT frequency makes it easier to manage and optimize concurrency without requiring a program change.

Detecting Lock Escalation

Fortunately, Db2 provides several ways to identify escalation activity. DBAs should monitor:

• IFCID traces
• Db2 statistics reports
• Accounting reports
• Performance monitor alerts
• System messages

A sudden increase in lock waits often points directly to escalation activity. When troubleshooting, examine:

• Which object escalated
• Which application triggered escalation
• COMMIT frequency
• Number of locks acquired
• Access path efficiency

The goal is not merely to identify that escalation occurred, but to determine why.

Preventing Lock Escalation

The best strategy is usually prevention rather than accommodation.

Improve Commit Frequency

Frequent commits reduce lock accumulation.

This is often the single most effective corrective action.

Tune SQL

Efficient SQL accesses fewer pages and rows, reducing lock requirements.

Better indexing and improved access paths often eliminate escalation problems entirely.

Adjust LOCKMAX

In some situations, increasing LOCKMAX may be appropriate.

However, simply raising thresholds without understanding the underlying workload can mask deeper application issues.

Use Partitioning

Partition-level locking can significantly reduce the scope of lock contention.

An escalated partition lock is generally less disruptive than a full table space lock.

Be Cautious with LOCKMAX 0

Setting LOCKMAX to 0 disables lock escalation. This prevents escalation, but it does not eliminate lock consumption.

If applications accumulate excessive locks, other resource constraints may emerge. Therefore, LOCKMAX 0 should be used only after careful analysis.

The DBA Perspective

One lesson I have learned repeatedly is that lock escalation is rarely the root problem. It is usually a symptom. When escalation occurs, Db2 is telling you something important. Specifically:

"This application is holding more locks than I am comfortable managing efficiently."

The correct response is usually not to disable escalation or simply raise thresholds. Instead, investigate the workload. Examine COMMIT frequency. Review SQL efficiency. Analyze access paths. Understand the business process generating the activity.

In many cases, the real solution lies in better application design rather than lock configuration.

Lock escalation exists to protect Db2. But when it appears regularly in your environment, it is often signaling an opportunity to improve performance, scalability, and concurrency. Wise DBAs treat lock escalation not as a nuisance, but as valuable diagnostic information about the health of their applications and workloads.

 

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?

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.

Know Your Isolation Levels to Develop Correct and Efficient Db2 Programs

Did you know that Db2 provides a method to change the way a program or SQL statement acquires locks? That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement. Standard SQL defines four isolation levels that can be set using the SET TRANSACTION ISOLATION LEVEL statement:

·        Serializable

·        Repeatable read

·        Read committed

·        Read uncommitted

The isolation level determines the mode of page or row locking implemented by the program as it runs.

Db2 supports a variation of the standard isolation levels. Db2 implements page and row locking at the program execution level, which means that all page or row locks are acquired as needed during the program run. Page and row locks are released as the program run depending on the isolation level.

In Db2 you can specify the following four isolation levels: cursor stability (CS), repeatable read (RR), read stability (RS), and uncommitted read (UR).

Using the ISOLATION parameter of the BIND command you can set the isolation level of a package or plan. You also can use the WITH parameter on a SELECT statement to set the isolation level of a single SQL statement.

Cursor stability is the Db2 implementation of the SQL standard read committed isolation level. CS is perhaps the most common DB2 isolation level in use in production applications because it offers a good tradeoff between data integrity and concurrency. When CS is specified the transaction will never read data that is not yet committed; only committed data can be read. Cursor stability is the current default isolation level if none is specified at BIND time.

A higher level of integrity is provided with repeatable read. Under an RR isolation level all page locks are held until they are released by a COMMIT (or ROLLBACK), whereas with CS read-only page locks are released as soon as another page is accessed.

An RR page locking strategy is useful when an application program requires consistency in rows that may be accessed twice in one execution of the program, or when an application program requires data integrity that cannot be achieved with CS.

For example, of a good reason to use RR page locking, consider a reporting program that scans a table to produce a detail report, and then scans it again to produce a summarized managerial report. If the program is bound using CS, the results of the first report might not match the results of the second.

Suppose that you are reporting the estimated completion dates for project activities. The first report lists every project and the estimated completion date. The second, managerial report lists only the projects with a completion date greater than one year.

The first report indicates that two activities are scheduled for more than one year. After the first report but before the second, however, an update occurs. A manager realizes that she underestimated the resources required for a project. She invokes a transaction to change the estimated completion date of one of her project's activities from 8 months to 14 months. The second report is produced by the same program, but reports 3 activities.

If the program used an RR isolation level rather than CS, an UPDATE that occurs after the production of the first report but before the second would not have been allowed. The program would have maintained the locks it held from the generation of the first report and the updater would be locked out until the locks were released.

How about another example? Consider a program that is looking for pertinent information about employees in the information center and software support departments who make more than $30,000 in base salary. In the DB2 sample tables department 'C01' is the information center and department 'E21' is software support.

The program opens a cursor based on the following SELECT statement:

    SELECT  EMPNO, FIRSTNME, LASTNAME,
            WORKDEPT, SALARY
    FROM    DSN8B10.EMP
    WHERE   WORKDEPT IN ('C01', 'E21')
    AND     SALARY > 30000;

The program then begins to FETCH employee rows. Assume further, as would probably be the case, that the statement uses the XEMP2 index on the WORKDEPT column. An update program that implements employee modifications is running concurrently. The program handles transfers by moving employees from one department to another, and implements raises by increasing the salary.

Assume that Sally Kwan, one of your employees, has just been transferred from the information center to software support. Assume further that another information center employee, Heather Nicholls, received a 10 percent raise. The update program running concurrently with the report program implements both of these modifications.

If the report program were bound with an isolation level of CS, the second program could move Sally from 'C01' to 'E21' after she was reported to be in department 'C01' but before the entire report was finished. Thus, she could be reported twice: once as an information center employee and again as a software support employee. Although this circumstance is rare, it can happen with programs that use cursor stability. If the program were bound instead with RR, this problem could not happen. The update program probably would not be allowed to run concurrently with a reporting program, however, because it would experience too many locking problems.

Now consider Heather's dilemma. The raise increases her salary 10 percent, from $28,420 to $31,262. Her salary now fits the parameters specified in the WHERE condition of the SQL statement. Will she be reported? It depends on whether the update occurs before or after the row has been retrieved by the index scan, which is clearly a tenuous situation. Once again, RR avoids this problem.

You might be wondering, "If CS has the potential to cause so many problems, why is it used so ubiquitously? Why not trade the performance and concurrency gain of CS for the integrity of RR?"

The answer is simple: the types of problems outlined are rare. The expense of using RR, however, can be substantial in terms of concurrency. So the tradeoff between the concurrency expense of RR and the efficiency of CS usually is not a sound one.

The third isolation level provided by DB2 is read stability (RS). Read stability is similar in functionality to the RR isolation level, but a little less. A retrieved row or page is locked until the end of the unit of work; no other program can modify the data until the unit of work is complete, but other processes can insert values that might be read by your application if it accesses the row a second time.

Consider using read stability over repeatable read only when your program can handle retrieving a different set of rows each time a cursor or singleton SELECT is issued. If using read stability, be sure your application is not dependent on having the same number of rows returned each time.

Finally, we come to the last, and most maligned isolation level, uncommitted read (UR). The UR isolation level provides read-through locks, also know as dirty read or read uncommitted. Using UR can help to overcome concurrency problems. When you're using an uncommitted read, an application program can read data that has been changed but is not yet committed.

UR can be a performance booster, too, because application programs bound using the UR isolation level will read data without taking locks. This way, the application program can read data contained in the table as it is being manipulated. Consider the following sequence of events:

1.   To change a specific value, at 9:00 a.m. a transaction containing the
      following SQL is executed:

UPDATE DSN8B10.EMP
   SET FIRSTNME = ˈMICHELLEˈ
WHERE  EMPNO = 10020;

The transaction is a long-running one and continues to execute without issuing a COMMIT.

2.   At 9:01 a.m., a second transaction attempts to SELECT the data that was
      changed, but not committed.

If the UR isolation level were used for the second transaction, it would read the changed data even though it had yet to be committed. Obviously, if the program doesn't need to wait to take a lock and merely reads the data in whatever state it happens to be at that moment, the program will execute faster than if it had to wait for locks to be taken and resources to be freed before processing.

The implications of reading uncommitted data, however, must be carefully examined before being implemented. Several types of problems can occur. Using the previous example, if the long-running transaction rolled back the UPDATE to EMPNO 10020, the program using dirty reads may have picked up the wrong name ("MICHELLE") because it was never committed to the database.

Inaccurate data values are not the only problems that can be caused by using UR. A dirty read can cause duplicate rows to be returned where none exist. Alternatively, a dirty read can cause no rows to be returned when one (or more) actually exists. Additionally, an ORDER BY clause does not guarantee that rows will be returned in order if the UR isolation level is used. Obviously, these problems must be taken into consideration before using the UR isolation level.

Keep in mind, too, that the UR isolation level applies to read-only operations: SELECT, SELECT INTO, and FETCH from a read-only result table. Any application plan or package bound with an isolation level of UR will use uncommitted read functionality for any read-only SQL. Operations contained in the same plan or package and are not read-only will use an isolation level of CS.

When is it appropriate to use UR isolation? The general rule of thumb is to avoid UR whenever the results must be 100 percent accurate. Following are examples of when this would be true:

• Calculations that must balance are being performed on the selected data
• Data is being retrieved from one source to insert to or update another
• Production, mission-critical work is being performed that cannot contain or cause data integrity problems

In general, most production Db2 applications are not serious candidates for dirty reads. In a few specific situations, however, the dirty read capability will be of major benefit. Consider the following cases in which the UR isolation level could prove to be useful:

• Access is required to a reference, code, or look-up table that basically is static in nature. Due to the non-volatile nature of the data, a dirty read would be no different than a normal read the majority of the time. In those cases when the code data is being modified, any application reading the data would incur minimum, if any, problems.
• Statistical processing must be performed on a large amount of data. Your company, for example, might want to determine the average age of female employees within a certain pay range. The impact of an uncommitted read on an average of multiple rows will be minimal because a single value changed will not greatly impact the result.
• Dirty reads can prove invaluable in a data warehousing environment that uses DB2 as the DBMS. A data warehouse is a time-sensitive, subject-oriented, store of business data that is used for online analytical processing. Other than periodic data propagation and/or replication, access to the data warehouse is read-only. Because the data is generally not changing, an uncommitted read is perfect in a read-only environment due to the fact that it can cause little damage. More data warehouse projects are being implemented in corporations worldwide and DB2 with dirty read capability is a very wise choice for data warehouse implementation.
• In those rare cases when a table, or set of tables, is used by a single user only, UR can make a lot of sense. If only one individual can be modifying the data, the application programs can be coded such that all (or most) reads are done using UR isolation level, and the data will still be accurate.
• Finally, if the data being accessed already is inconsistent, little harm can be done using a dirty read to access the information.

Although the dirty read capability can provide relief to concurrency problems and deliver faster performance in specific situations, it also can cause data integrity problems and inaccurate results. Be sure to understand the implications of the UR isolation level and the problems it can cause before diving headlong into implementing it in your production applications.

Summary

It is important for Db2 DBAs and application programmers to know the four isolation levels and their impact on SQL. Using the isolation levels is an effective way to control concurrency and locking for your Db2 applications.

Db2 12 for z/OS Function Level 507

This month, June 2020, IBM introduced a new function level, FL507, for Db2 12 for z/OS. This is the first new function level this year, and the first since October 2019. The Function Level process was designed to release Db2 functionality using Continuous Delivery (CD) in short, quick bursts. However, it seems that the global COVID-19 pandemic slowed things a bit… and that, of course, is understandable. But now we have some new Db2 for z/OS capabilities to talk about for this first time in a little bit! 

There are four significant impacts of this new function level:

• Application granularity for locking limits
• Deletion of old statistics from the Db2 Catalog when using profiles
• CREATE OR REPLACE capability for stored procedures
• Passthrough-only expressions with IBM Db2 Analytics Accelerator (IDAA)

Let’s take a quick look at each of these new things.

The first new capability is the addition of application granularity for locking limits. Up until now, the only way to control locking limits was with NUMLKUS and NUMLKTS subsystem parameters, and they applied to the entire subsystem. 

NUMLKTS defines the threshold for the number of page locks that can be concurrently held for any single table space by any single DB2 application (thread). When the threshold is reached, DB2 escalates all page locks for objects defined as LOCKSIZE ANY according to the following rules:

• All page locks held for data in segmented table spaces are escalated to table locks.
• All page locks held for data in partitioned table spaces are escalated to table space locks.

NUMLKUS defines the threshold for the total number of page locks across all table spaces that can be concurrently held by a single DB2 application. When any given application attempts to acquire a lock that would cause the application to surpass the NUMLKUS threshold, the application receives a resource unavailable message (SQLCODE of -904).

Well, now we have two new built-in global variables to support application granularity for 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. 

The next new capability is the deletion of old statistics when using profiles. When you specify the USE PROFILE option with RUNSTATS, Db2 collects only those statistics that are included in the specified profile. Once function level 507 is activated, Db2 will delete any existing statistics for the object(s) that are not part of the profile. This means that all frequency, key cardinality, and histogram statistics that are not included in the profile are deleted from the Db2 Catalog for the target object. 

This is a welcome new behavior because it makes it easier to remove old and stale distribution statistics. Keep in mind that this new behavior also applies when you use profiles to gather inline statistics with the REORG TABLESPACE and LOAD utilities.

Another great new capability that stored procedure users have been waiting for for some time now is the ability to specify CREATE OR REPLACE for procedures. This means that you do not have to first DROP a procedure if you want to modify it. You can simply specify CREATE OR REPLACE PROCEDURE and if it already exists, the procedure will be replaced, and if not, it will be created. This capability has been available in other DBMS products that support stored procedures for a while and it is good to see it come to Db2 for z/OS!

Additionally, for native SQL procedures, you can use the OR REPLACE clause on a CREATE PROCEDURE statement in combination with a VERSION clause to replace an existing version of the procedure, or to add a new version of the procedure. When you reuse a CREATE statement with the OR REPLACE clause to replace an existing version or to add a new version of a native SQL procedure, the result is similar to using an ALTER PROCEDURE statement with the REPLACE VERSION or ADD VERSION clause. If the OR REPLACE clause is specified on a CREATE statement and a procedure with the specified name does not yet exist, the clause is ignored and a new procedure is still created.

And finally, we have support for passthrough-only expressions to IDAA. This is needed because you may want to use an expression that exists on IDAA, but not on Db2 12 for z/OS. With a passthrough-only expression, Db2 for z/OS simply verifies that the data types of the parameters are valid for the functions. The expressions get passed over to IDAA, and the accelerator engine does all other function resolution processing and validation. 

What new expressions does FL507 support you may ask? Well all of the following built-in functions are now supported as passthrough-only expressions to IDAA:

• ADD_DAYS
• BTRIM
• DAYS_BETWEEN
• NEXT_MONTH
• Regression functions 
• REGR_AVGX
• REGR_AVGY
• REGR_COUNT
• REGR_INTERCEPT
• REGR_ICPT
• REGR_R2
• REGR_SLOPE
• REGR_SXX
• REGR_SXY
• REGR_SYY
• ROUND_TIMESTAMP (when invoked with a DATE expression)

You can find more details on the regression functions from IBM here. 

Summary

These new capabilities are all nice, new features that you should take a look at, especially if you have applications and use cases where they can help. 

The enabling APAR for FL507 is PH24371. There are no incompatible changes with FL 507. But be sure to read the instructions for activation details and Db2 Catalog impacts for DL 507.