Are You Actually Using the Db2 13 You Installed?

Congratulations. By now you should have migrated to Db2 13 for z/OS bringing you up-to-date with Db2 versions. But now what?

For too many organizations, the answer is: pretty much the same thing we were doing before.

The migration project gets completed, Db2 13 goes into production, everybody celebrates, and then the environment continues operating largely as it did under Db2 12. Applications continue using the same SQL, packages remain at older application compatibility levels, operational procedures change very little, and many of the capabilities delivered with Db2 13 remain unused.

There is nothing inherently wrong with being cautious. In fact, caution is usually a good characteristic for anyone responsible for mission-critical Db2 systems. But there is a difference between controlled adoption and simply ignoring new capabilities.

Installing Db2 13 and exploiting Db2 13 are two very different things.

Db2 Is No Longer a Once-Every-Few-Years Event

Long-time Db2 professionals remember when a new version meant a large, discrete migration project. You installed the new release, migrated the catalog, tested applications, and then operated on that version until IBM delivered the next one.

Continuous delivery changed that model.

With Db2 13, IBM delivers many new capabilities through the maintenance stream as they become ready. Function levels and application compatibility levels provide mechanisms for controlling when those capabilities become available to the subsystem and applications.

As of September 2026, Db2 13 has progressed through Function Level 509 (V13R1M509), which IBM delivered in April 2026. FL509 introduced capabilities including catalog table-space conversion to UTS, subnet filtering for the Resource Limit Facility, online ownership transfer for certain application objects, additional utility-history information, I/O counts in real-time statistics, and improvements involving data-sharing page P-lock contention.

The important point is not any particular function delivered by FL509. It is that Db2 continues to evolve after you migrate to each new version!

That requires a corresponding change in how DBAs think about Db2 maintenance.

Know What Level You Are Actually Running

One of the complications of continuous delivery is that saying, "We're running Db2 13," doesn't tell us everything we need to know.

There are several related levels to understand: code level, catalog level, function level, and application compatibility level. They work together, but they are not the same thing.

The code level reflects the Db2 code installed through maintenance. The catalog level identifies the level of the Db2 catalog. The function level determines which continuously delivered capabilities have been activated. And APPLCOMPAT controls which new SQL capabilities and behaviors an application can use.

IBM provides the -DISPLAY GROUP command as one way to determine the code, catalog, and function levels of your Db2 environment.

This is important because you can have the code installed for a capability without actually having that capability available for use.

And even after activating a new function level, your applications do not automatically have to adopt every new SQL behavior. In most cases, new SQL syntax and behavior require the application to operate at the corresponding APPLCOMPAT level.

That separation is a feature, not a problem. It gives DBAs control.

APPLCOMPAT Is Your Friend

I've encountered organizations that view APPLCOMPAT primarily as another complication associated with Db2 maintenance. I prefer to look at it as a risk-management mechanism.

Suppose your subsystem is operating at a newer Db2 13 function level, but you have an important application that has not yet been thoroughly tested against the corresponding SQL behavior. You do not necessarily have to move that application forward immediately.

IBM specifically designed APPLCOMPAT so applications can adopt new capabilities when the organization is ready. Applications can continue operating with behavior associated with earlier compatibility levels even when the subsystem has moved ahead.

That is extremely useful in large enterprises where hundreds or thousands of packages may support applications with very different development schedules and business priorities.

Indeed, IBM recommends that customers not immediately raise the subsystem's default APPLCOMPAT level after migration or function-level activation. Applications should first be verified at the higher level and incompatibilities addressed.

In other words, continuous delivery does not mean reckless delivery... it means controlled delivery.

Don't Forget About Performance

New function is not the only reason to examine what Db2 13 offers. Performance improvements can be just as important. And sometimes more important.

Db2 13 has delivered improvements involving sort processing, INSERT processing, index look-aside optimization, Fast Traversal Blocks, external security processing, and other areas. Function Level 506, for example, introduced an optimization for certain queries containing large residual IN-list predicates that IBM says can substantially reduce CPU consumption for applicable SQL.

But there is an important lesson here for DBAs: a performance enhancement sitting in the product documentation isn't improving your workload merely because you migrated to Db2 13. Some enhancements become effective when maintenance is applied. Others depend on function levels. Optimization improvements may require a full prepare of the SQL before a new access path can take advantage of them. For static SQL, that generally means a bind or rebind.

And that means you need to understand how new Db2 capabilities intersect with your existing package management, access-path management, statistics, testing, and REBIND strategies.

Simply installing Db2 isn't performance tuning.

Make Exploitation an Ongoing DBA Discipline

So what should you be doing?

Periodically review what IBM has delivered since your last Db2 capability assessment. Determine your current code, catalog, function, and application compatibility levels. Review the new-function APARs and function levels that have become available. Identify enhancements relevant to your workloads, and determine what must happen before you can safely exploit them.

Then prioritize.

You do not need to implement every new feature just because IBM delivers it. That would be just as misguided as ignoring everything new. A high-volume transaction processing environment may benefit from different enhancements than a mixed analytics environment. Security improvements may be urgent for one organization while SQL or utility improvements provide greater value for another.

The objective should be deliberate exploitation.

Ask what business, performance, availability, security, or operational problem a capability solves. Test it appropriately. Understand its prerequisites. Determine its effect on applications and access paths. Then introduce it under controlled conditions.

That sounds like traditional DBA discipline because it is.

Migration Is the Beginning, Not the End

There is a tendency to treat a Db2 migration as a destination. We get to Db2 13, declare success, and move on to the next project. Continuous delivery makes that mindset obsolete.

Db2 13 is not a static piece of software that looks exactly the same several years after installation. IBM continues to add capabilities through function levels and new-function APARs. Indeed, Db2 13 has moved from its initial 2022 function levels through FL509 in 2026.

That means Db2 professionals need an ongoing process for evaluating what is available, what is activated, what applications can exploit, and what provides meaningful value.

So take a look at your environment. You know you're running Db2 13, but how much of Db2 13 are you actually using?

That is a much more interesting question.

The Most Overlooked Db2 Performance Metric

 If I had to identify a single most overlooked Db2 performance metric, it would be GETPAGEs.

Many organizations focus on CPU utilization, elapsed time, or buffer pool hit ratios because those metrics are easy to understand and frequently appear on dashboards. But getpages often reveal performance problems long before those other metrics become alarming.

In Db2 for z/OS, a GETPAGE represents a request by Db2 to access a page of data or an index page. If the page is already in the buffer pool, no physical I/O is required. If not, Db2 must read it from disk. Either way, every GETPAGE consumes CPU resources.

Why GETPAGEs Matter

Because every SQL statement generates GETPAGE requests, the number of GETPAGEs is a fundamental measure of how much work Db2 performs to execute a workload. The more pages Db2 must examine to satisfy a query, the more work it performs. Reducing unnecessary GETPAGEs often leads directly to lower CPU consumption and better application performance.

High GETPAGE counts usually indicate one or more of the following:

• Inefficient index usage
• Poor clustering
• Table scans
• Low filter factors
• Redundant index probes
• Accessing more columns or rows than necessary

Even when all pages are found in the buffer pool, millions of unnecessary GETPAGEs still consume CPU.

The Hidden CPU Consumer

I've worked with organizations that upgraded processors because CPU costs continued to rise. After analyzing the workload, we discovered that many critical SQL statements were generating ten or even one hundred times more GETPAGEs than necessary.

A simple index redesign or SQL rewrite reduced GETPAGE counts dramatically. And CPU usage dropped without any hardware changes.

That's why I often tell clients: "Every unnecessary GETPAGE results in costly unneeded work."

Don't Just Count GETPAGEs

The raw number of GETPAGEs by itself does not tell the whole story. Instead, monitor:

• GETPAGEs per transaction
• GETPAGEs per SQL statement
• GETPAGEs per row returned
• Trends over time
• The highest GETPAGE-consuming applications

A workload processing twice as many transactions should naturally generate more GETPAGEs. The real warning sign is when GETPAGEs per transaction steadily increase. That usually means SQL or access paths have degraded.

GETPAGEs Point to Root Causes

Unlike CPU utilization, which tells you that work is occurring, GETPAGEs often explain why.

For example:

Symptom	What High GETPAGEs May Indicate
High CPU	Excessive index or table page accesses
Long elapsed time	Inefficient access path
Buffer pool pressure	Poor locality of reference
Lock contention	Long-running scans
Increased zIIP usage	More work being offloaded, but still excessive overall processing

My Rule of Thumb 👍

After more than four decades tuning Db2 systems, one principle has remained remarkably consistent:

Watch the work, not just the time.

Elapsed time can fluctuate because of concurrency. CPU depends on hardware generation. I/O depends on storage technology.

But GETPAGEs measure the amount of work Db2 is performing. If you reduce the work, you almost always improve performance.

Other Frequently Overlooked Metrics

Although GETPAGEs are my top choice, several other metrics deserve more attention:

• Synchronous read percentage — Indicates how often Db2 must wait for I/O instead of benefiting from prefetch.
• Pages read per GETPAGE — Helps evaluate buffer pool effectiveness.
• Lock suspension time — Often more meaningful than simply counting lock waits.
• Class 3 suspension time — Shows where Db2 is waiting (I/O, locks, logging, etc.).
• RID pool failures — Can reveal access path problems that are otherwise difficult to diagnose.
• Sort overflows — Indicate insufficient sort memory or inefficient SQL.
• Index leaf page split rates — A useful indicator of index maintenance issues and clustering degradation.

If I were teaching a new Db2 performance analyst, I'd spend less time looking at CPU graphs and more time asking, "Why is Db2 doing so much work?" In many cases, the answer begins with GETPAGEs.

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.

 

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?

A High-Level Guide for Db2 Database Health Checks

 Here’s a practical, field-tested 10-point Db2 database health check you can use to quickly assess the state of your environment and identify risk areas.

1. Buffer Pool Efficiency

• Check hit ratios (GETPAGE vs. I/O)
• Look for excessive synchronous reads
• Ensure critical objects reside in appropriately sized buffer pools

---

2. SQL Performance & Access Paths

• Identify top CPU-consuming and elapsed time SQL
• Look for table scans, non-matching index scans, and sort activity
• Validate access paths haven’t regressed (especially after RUNSTATS or REBIND)

---

3. Index Effectiveness

• Check for unused or duplicate indexes
• Identify missing indexes for high-frequency queries
• Review clustering ratios and index levels

---

4. RUNSTATS Currency & Quality

• Ensure statistics are up to date on critical tables and indexes
• Verify distribution stats (FREQVAL, HISTOGRAM) where needed
• Watch for stale or missing stats causing poor optimizer choices

---

5. Locking & Concurrency

• Monitor lock waits, timeouts, and deadlocks
• Identify hot objects or pages
• Evaluate isolation levels and commit frequency

---

6. I/O Performance

• Review I/O response times by dataset and volume
• Check for hotspots or imbalance across storage groups
• Ensure proper striping and dataset placement

---

7. Logging & Recovery Health

• Monitor active log utilization and archive activity
• Check for long-running units of work delaying log reuse
• Validate backup and recovery procedures (image copies, PIT recovery readiness)

---

8. Utilities & Maintenance

• Ensure REORGs are run where needed (based on RTS, not just schedule)
• Validate COPY, RUNSTATS, and REORG cadence
• Look for objects in advisory REORG or COPY pending states

---

9. System Resource Utilization

• Track CPU consumption (general purpose vs. zIIP if applicable)
• Monitor memory usage and EDM pool (for Db2 for z/OS)
• Watch for thread reuse and connection pooling efficiency

---

10. Configuration & ZPARM Settings

• Review key ZPARMs (or Db2 LUW configuration parameters)
• Validate they align with workload characteristics
• Check for outdated defaults that may no longer be optimal

---

Bonus Tip: Trend, Don’t Snapshot

A single point-in-time check is useful—but trending over time is where the real insight lives. Look for gradual degradation, not just obvious breakage.