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.

The Silent Performance Killers in Db2 for z/OS: What You’re Probably Not Monitoring

 If you’ve been working with Db2 for z/OS long enough, you’ve likely experienced this scenario.

Everything looks fine. CPU is within expected ranges. Buffer pools aren’t thrashing. Locking isn’t out of control. The dashboards are green. Nothing is obviously broken.

And yet, users start to complain. Batch jobs run a little longer. Online response times stretch just enough to be noticeable. Not a crisis—but not quite right, either.

So you dig in. At first, nothing stands out. But as you peel back the layers, you discover the truth: performance didn’t fall off a cliff. It eroded. Quietly. Gradually. Almost invisibly.

These are the silent performance killers. And in many Db2 for z/OS environments, they’re not being monitored closely enough.

The Illusion of Stability

One of the strengths of Db2 for z/OS is its stability. With features like plan management, access path stability, and mature instrumentation, it is entirely possible to run a system that appears steady for long periods of time.

But stability can be deceptive.

When access paths remain “stable,” it is easy to assume they remain “optimal.” When performance metrics stay within historical ranges, it is tempting to believe everything is under control.

In reality, small inefficiencies can accumulate. A slightly suboptimal access path here. A bit of extra I/O there. A few more GETPAGEs than last quarter. Individually insignificant. Collectively impactful.

And because the degradation is gradual, it often escapes notice... until it becomes difficult to ignore.

Predicate Processing: Stage 1 vs. Stage 2 Still Matters

It is fashionable in some circles to assume that the old distinctions no longer matter. But predicate processing still plays a critical role in performance.

Stage 2 predicates, non-indexable expressions, and function-wrapped columns can quietly force Db2 to do more work than necessary. Even when an index is present, it may not be used as efficiently as expected.

What makes this particularly insidious is that the SQL hasn’t necessarily changed. But data distribution might have. Or perhaps a new release introduced subtle optimizer behavior differences. Or a developer added a seemingly harmless function to a predicate.

The query still runs. It just runs a little slower. Multiply that across hundreds or thousands of query executions, and the cost can become significant.

Statistics Drift: RUNSTATS Is Not a “Set It and Forget It”

Most Db2 shops run RUNSTATS. That’s not the problem. The problem is assuming that any RUNSTATS is sufficient.

Over time, data changes. Skew increases. New values appear. Old assumptions become invalid. Yet many organizations rely on static RUNSTATS profiles that no longer reflect reality. When statistics drift, the optimizer’s decisions drift with them.

Access paths that were once ideal become merely acceptable. Then marginal. Then problematic. But because RUNSTATS is still running, there is a false sense of confidence.

The real question is not whether RUNSTATS is being executed. It is whether the right statistics are being collected at the right time with the right level of detail.

zIIP Offload: Are You Getting What You Expect?

zIIP utilization is often viewed as a win when the numbers look high. But high utilization does not necessarily mean optimal utilization.

Some workloads that should be offloaded are not. Others are only partially eligible. In some cases, system or application changes inadvertently reduce zIIP eligibility without anyone noticing.

The result is subtle but important: more work shifts back to general-purpose CPUs.

You may not see a dramatic spike. But you may see a steady increase over time—one that is difficult to explain if you are not explicitly tracking zIIP efficiency at a granular level.

Buffer Pool Health: Beyond Hit Ratios

Buffer pool hit ratios have long been a staple metric. But by themselves, they can be misleading.

A high hit ratio does not guarantee efficient memory usage. It is entirely possible to have acceptable hit ratios while still experiencing excessive page churn, suboptimal page residency, or inefficient object placement.

As workloads evolve, the way data is accessed changes. Tables grow. Index usage shifts. New applications introduce different access patterns.

If buffer pools are not periodically re-evaluated and tuned in light of these changes, inefficiencies can creep in unnoticed.

Again, nothing breaks. Things just get a little slower.

The Application Factor

Perhaps the most significant, and most likely least visible, source of silent degradation is the application layer. Modern development practices introduce new challenges. ORMs generate SQL that is technically correct but not always efficient. Microservices increase the number of distinct access patterns. Dynamic SQL proliferates. And often, these changes occur outside the traditional DBA change control process.

The DBA sees the symptoms—more executions, different access paths, increased resource consumption... but not always the cause. Without tighter collaboration between development and database teams, these inefficiencies can persist indefinitely.

Plan Stability: A Double-Edged Sword

Plan stability features are invaluable. They protect against sudden access path regressions and provide a safety net during change. But they can also create complacency.

When a plan is “locked in,” it is easy to stop questioning whether it is still the best plan. Over time, as data and workloads evolve, a once-optimal access path may no longer be ideal.

Yet it persists. Not because it is the best choice, but because it is thought to be the safest.

Periodic review is essential. Stability should not mean stagnation.

What Should You Be Doing?

The answer is not to monitor more metrics indiscriminately. It is to monitor the right things with the right perspective. Look for trends, not just thresholds. Compare performance over time, not just against static baselines. Question assumptions that have gone unchallenged for years.

Examine access paths periodically—even for stable workloads. Revisit RUNSTATS strategies. Validate zIIP expectations. Reassess buffer pool configurations.

And perhaps most importantly, engage with application teams. Understand how the workload is changing, not just how it is performing.

Final Thoughts

The most dangerous performance problems in Db2 for z/OS are not the ones that cause immediate failures. They are the ones that quietly erode efficiency over time.

They do not trigger alarms. They do not demand urgent attention. They simply persist. Until one day, “everything looks fine” is no longer true. And by then, the fix is far more involved than it would have been if the problem had been caught earlier.

The challenge... and the opportunity... for today’s DBA is to look beyond the obvious. To question the comfortable. And to recognize that in a mature, stable system, the biggest risks are often the ones you are not actively watching.

Db2 Performance Myths That Refuse to Die

After decades of working with IBM Db2 for z/OS, I’m continually surprised that some performance myths simply refuse to go away. Despite new releases, new features, and new hardware generations, the same half-truths and oversimplifications keep resurfacing in design meetings and tuning discussions.

Performance tuning is nuanced. It requires understanding tradeoffs, workload patterns, and business requirements. When we rely on myths instead of measurement, we risk making things worse—not better.

Let’s examine a few Db2 performance myths that deserve to be retired once and for all.

Myth #1: “More Indexes Always Improve Performance”

This may be the most persistent myth of them all. Yes, indexes can dramatically improve read performance. But every index carries overhead:

• INSERTs must update every index.

• DELETEs must remove entries from every index.

• UPDATEs may require index maintenance.

• REORG and RUNSTATS processing increases.

• Log volume grows.

• Buffer pool pressure increases.

In high-volume OLTP systems, excessive indexing can slow down transactional throughput far more than it speeds up queries. I’ve seen tables with 20, 30, even 50 indexes many of them rarely used.

Indexes should be created deliberately:

• To support specific, critical access paths.

• To enforce uniqueness or constraints.

• To support clustering or partitioning strategies.

If you’re not actively verifying index usage with performance traces or accounting data, you’re guessing. And guessing is not tuning.

All of the above notwithstanding, there is a corrollary here that needs to be mentioned: There is no magic number of indexes! Sometimes people think the exact opposite of this myth, namely that there should be no mroe than 3 or 5 (or insert your favorite number here) indexes per table. And this can be just as dangerouse as believing that there is no impact to adding too many indexes!

Myth #2: “Buffer Pools Fix Everything”

Throwing memory at a performance problem is tempting. Increase the buffer pool. Expand real storage. Let caching solve it. Sometimes that works. Often it doesn’t.

Buffer pools help when:

• You’re experiencing high synchronous I/O.

• There’s clear evidence of poor hit ratios.

• The workload has locality of reference.

But if your problem is:

• Poor access paths

• Missing or inaccurate statistics

• Lock contention

• Log bottlenecks

• CPU-bound processing

Then increasing buffer pool size won’t solve the underlying issue.

I’ve seen shops double buffer pools and see zero improvement because the real issue was an inefficient access path causing millions of unnecessary GETPAGE operations.

Memory is powerful. But it’s not magic.

Myth #3: “RUNSTATS Solves Access Path Problems”

RUNSTATS is essential. But it is not a cure-all. Statistics allow the optimizer to make informed decisions, however be sure to heed the following:

• If distribution statistics are missing, the optimizer may still misestimate.

• If predicates are stage 2, statistics won’t fix the inefficiency.

• If indexes are poorly designed, RUNSTATS won’t invent a better one.

• If dynamic SQL varies significantly, access paths may still fluctuate.

More importantly, blindly running RUNSTATS can change access paths unexpectedly. I’ve seen stable systems destabilized because statistics were refreshed without understanding the impact.

Statistics should be:

• Purposeful.

• Consistent.

• Aligned with workload patterns.

RUNSTATS informs the optimizer. It doesn’t override bad design.

Myth #4: “Static SQL Is Always Better Than Dynamic”

There was a time when this was mostly true. Today, it is clearly not the case.

Modern dynamic SQL in Db2 for z/OS benefits from:

• Dynamic statement caching

• Improved reoptimization options

• Plan management capabilities

• Statement concentration

• Better access path stability controls

Dynamic SQL can be highly efficient, especially in distributed and API-driven environments where flexibility is essential. The real issue isn’t static versus dynamic, instead it is fitting the design to the use case and understanding options like:

• Access path stability.

• Parameter marker usage.

• Proper bind options.

• Monitoring and governance.

Poorly written static SQL can perform worse than well-managed dynamic SQL. The myth persists because it’s easier to rely on history than to evaluate modern capabilities.

Myth #5: “The Optimizer Always Knows Best”

The optimizer is incredibly sophisticated. But it is not omniscient. It depends making sure that you have a well-designed system with an understanding and implementation of:

• Correct statistics options that are up-to-date and accurate

• UInderstanding of data distribution 

• Available indexes

• Cost model estimates

There are cases where:

• Business knowledge reveals data skew that the optimizer cannot detect.

• A carefully crafted index improves performance dramatically.

• Query rewrites reduce CPU by orders of magnitude.

Blind faith in the optimizer can lead to complacency. The best DBAs understand how the optimizer thinks (at least for the most part) and understand the best ways to guide it when that is necessary.

Myth #6: “If It’s Slow, It Must Be Db2”

This one might be my favorite. Or perhaps I should say least favorite!

When performance degrades, Db2 is often blamed first. But many problems originate elsewhere:

• Application design issues

• Network traffic

• Inefficient commit frequency (or no commit strategy at all)

• Lock escalation caused by poor transaction boundaries

• Middleware configuration

• Distributed request patterns

Db2 is frequently the messenger, not the culprit. Effective tuning requires end-to-end analysis. If you only look at Db2 metrics, you’re only seeing part of the picture.

The Real Lesson: Measure, Don’t Assume

Performance tuning in Db2 is not about rules of thumb. It’s about evidence.

Before:

• Adding an index,

• Increasing a buffer pool,

• Running RUNSTATS,

• Changing bind options, or

• Rewriting SQL,

You need data. Data that can be found in:

• Accounting (and other) traces,

• Performance monitors,

• Workload statistics, and

• Access path analysis.

Myths persist because they contain a kernel of truth. But performance is contextual. What helps one workload can hurt another.

The best Db2 professionals:

• Understand tradeoffs.

• Measure impact.

• Test changes.

• And avoid one-size-fits-all thinking.

As Db2 environments continue to evolve adopting hybrid workloads, AI-driven applications, and distributed architectures, the need for disciplined performance analysis becomes even more critical.

Myths are comfortable. Measurement is harder.

But if you want predictable, scalable performance from Db2 for z/OS, evidence... not folklore... must drive your decisions.

10 Reasons for the Success of Db2 for z/OS

Db2 for z/OS has proven successful for decades not because of any single feature, but because it consistently delivers on the things that matter most for mission-critical enterprise computing. The biggest reasons fall into a few clear and convincing categories.

1. Unmatched Reliability and Availability

At the top of the list is availability. Db2 for z/OS is engineered to run continuously, often measured in years of uptime rather than days or months.

Key contributors include:

• Robust logging and recovery mechanisms

• Online maintenance (schema changes, reorgs, index builds)

• Data sharing across multiple Db2 members in a Parallel Sysplex

• Automatic restart and failure isolation

For businesses where downtime directly translates to lost revenue, regulatory exposure, or reputational damage, this reliability is non-negotiable... and Db2 has consistently delivered it.

2. Exceptional Performance at Massive Scale

Performance is a hallmark of Db2 systems and applications. Db2 for z/OS excels at high-volume, high-concurrency transaction processing. It routinely handles:

• Tens of thousands of transactions per second

• Millions of SQL statements per hour

• Thousands of concurrent users

This performance advantage is not accidental. Db2 is tightly integrated with IBM Z hardware features such as:

• Specialty processors (zIIP, and previously zAAP which has been rolled into the zIIP funtionality)

• Large memory footprints with sophisticated buffer management

• Hardware-assisted compression and encryption

The result is predictable, repeatable performance even under extreme workloads.

3. Deep Integration with the z/OS Platform

Unlike databases that are merely hosted on an operating system, Db2 for z/OS is co-engineered with z/OS and IBM Z hardware.

This integration enables:

• Advanced workload management (WLM)

• Superior I/O handling

• System-level security and auditing

• Fine-grained resource governance

Because the database, OS, and hardware evolve together, Db2 can exploit platform innovations faster and more effectively than loosely coupled systems.

4. Rock-Solid Data Integrity and Consistency

Db2 for z/OS has earned a reputation as the system of record because it protects data integrity above all else.

This includes:

• Full transactional integrity (ACID compliance)

• Enforced referential integrity and constraints

• Proven locking and concurrency control

• Bulletproof recovery from failures

Enterprises trust Db2 with their most valuable data including financial records, customer accounts, order entry details, healthcare information, flight tracking and more. When correctness is not optional, Db2 for z/OS is the answer!

5. Security Built In, Not Bolted On

Security has always been foundational to Db2 for z/OS, not an afterthought.

Its strengths include:

• Tight integration with RACF and z/OS security services

• Granular authorization at table, column, and row levels

• Native encryption for data at rest and in flight

• Comprehensive auditing and compliance capabilities

For highly regulated industries, Db2 simplifies compliance while reducing risk exposure.

6. Backward Compatibility and Investment Protection

Few platforms can match Db2’s commitment to backward compatibility. Applications written decades ago often continue to run unchanged today.

This provides:

• Long-term investment protection

• Lower modernization risk

• Predictable upgrade paths

Organizations can adopt new Db2 features incrementally without rewriting core applications which is a critical factor in long-term platform success.

7. Continuous Evolution Without Disruption

Db2 for z/OS has evolved continuously while maintaining stability. Over the years it has added:

• Support for new SQL standards

• XML and JSON capabilities

• Temporal tables

• Advanced analytics functions

• RESTful access and modern connectivity

Importantly, these enhancements arrived without forcing disruptive migrations, a balance few platforms achieve.

8. Alignment with Business-Critical Workloads

Db2 for z/OS was designed from the start to support workloads that:

• Cannot fail

• Cannot lose data

• Cannot tolerate unpredictable performance

Industries such as banking, insurance, government, retail, and transportation still depend on these characteristics. As long as these workloads exist, Db2’s value remains clear.

9. A Mature Ecosystem and Skilled Community

Db2 benefits from:

• Decades of operational best practices

• A rich ecosystem of tools (monitoring, tuning, recovery, automation)

• A global community of experienced professionals

This maturity reduces risk and accelerates problem resolution which is another quiet, but powerful, contributor to its success.

10. Trust Earned Over Time

Perhaps the most important reason for Db2 for z/OS’s success is trust. Enterprises have seen it perform reliably through:

• Hardware generations

• Economic cycles

• Technology shifts

• Organizational change

That trust is hard to win... and even harder to replace.

In Summary

Db2 for z/OS has endured not because it resists change, but because it embraces change without compromising stability. Its success rests on a rare combination of reliability, performance, security, and evolution. And these qualities remain just as relevant today as when the platform was first introduced.

Don't Forget About Histogram Statistics in Db2 for z/OS

There are a lot of options and parameters to consider when putting together your RUNSTATS jobs to collect statistics on your Db2 data. For those who do not know, RUNSTATS is a Db2 for z/OS utility program that is used to collect information about characteristics of tables and indexes, and storing that data in the Db2 Catalog where it can be used by the Db2 Optimizer to formulate SQL access paths. 

Without reasonable statistics, as collected by RUNSTATS, you will likely experience SQL performance problems because Db2 will be accessing the data based on incorrect or outdated information about your data. As I have discussed before, RUNSTATS is one of The 5 R's of Db2 Performance.

OK, with that introductory bit out of the way, one capability of RUNSTATS, first introduced in Db2 9 for z/OS is somewhat underutilized. As you might guess from the title of this blog post, I am talking about histogram statistics.

But what is a histogram? Well, in mathematical terms, a histogram is a representation of data that shows the distribution of data in a data set. Often represented using a bar graph, each bar represents the frequency of data points within a specific range. However, unlike a typical bar chart (displaying categorical data), a histogram groups numbers into ranges, making it useful for identifying patterns, such as underlying frequency distribution, outliers, and skew.

So, a histogram is a way of summarizing data that’s measured on an interval scale. A histogram is particularly helpful when you want:

• to highlight how data is distributed, 
• to determine if data is symmetrical or skewed, or 
• to indicate whether or not outliers exist.

The histogram is appropriate only for variables whose values are numerical and measured on an interval scale. To be complete, let’s define interval, which is a set of real numbers between two numbers either including or excluding one or both of them.  

Histograms are generally most useful when dealing with large data sets.

Why Use Histogram Statistics?

Db2 historgram statistics can be quite useful for certain types of data. Instead of the frequency statistics, which are collected for only a subset of the data, sometimes Db2 can improve access path selection by estimating predicate selectivity from histogram statistics, which are collected over all values in a table space.

Histogram statistics can help the Db2 Optimizer estimate filtering when certain data ranges are heavily populated and others are sparsely populated. Think about data that represents customers of a diner. Typically, there will be periods of high activity (breakfast, lunch, dinner) and low activity (other periods). Or perhaps on a grander scale, consider data that represents customer activity at a big box store. During big sales (such a Black Friday) and over holidays (Christmas, for example), there will be more sales than during other periods.

Depending on the type and makeup of the data, it can make sense to collect histogram statistics to improve access paths for troublesome queries used with the following predicates: 

• RANGE, 
• LIKE, and 
• BETWEEN. 

Histogram statistics also can help in some cases for equality (=), IS NULL, IN list, and COL op COL predicates.

 

How to Collect Histogram Statistics

 

OK, so how can you go about collecting histogram statistics? Well, the IBM RUNSTATS utility can collect statistics by quantiles. A quantile is the name for a bucket representing a range of data. For instance, from our diner example discussed earlier, you might want to have quantiles representing pre-breakfast, breakfast, pre-lunch, lunch, pre-dinner, dinner, and late night.

Db2 allows up to 100 quantiles. You can specify how many quantiles Db2 is to use: from one up to 100. Of course, specifying one isn’t wise because it’s the same as collecting for everything and so it will not be very helpful.

You do not explicitly define the make-up of quantiles, though. Db2 does that. Db2 creates equal-depth histogram statistics, dividing the whole range of values into quantiles that each contain about the same percentage of the total number of rows. 

You indicate that RUNSTATS should collect histogram statistics by coding the HISTOGRAM keyword in conjunction with the COLGROUP option. This way you can collect histogram statistics for a group of columns. You also must tell Db2 the number of quantiles to collect by specifying the NUMQUANTILES parameter. NUMQUANTILES also can be specified with the INDEX parameter, in which case it indicates that histogram statistics are to be collected for the columns of the index.

 

A single value can never be broken into more than one interval. This means the maximum number of intervals is equal to the number of distinct column values. For example, if you have 40 values, you can have no more than 40 quantiles, each consisting of a single value. In other words, be sure that you don’t specify a value for NUMQUANTILES that is greater than the total number of distinct values for the column (or column group) specified. Also, keep in mind that any NULLs will occupy a single interval.

 

So, then, how do you decide on the number of quantiles to collect? If you don’t specify NUMQUANTILES, the default value of 100 will be used; then based on the number of records in the table, Db2 will adjust the number of quantiles to an optimal number. Therefore, unless you have a good understanding of the application or a viable reason to deviate, a reasonable rule of thumb is to simply let NUMQUANTILES default and let Db2 work it out.

 

RUNSTATS will attempt to produce an equal-depth histogram. This means each interval will have about the same number of rows. Please note that this doesn’t mean the same number of values—it’s the same number of rows. In some cases, a highly frequent single value could potentially occupy an interval all by itself.

 

Where Are the Histogram Statistics Stored?

 

The following columns in a histogram statistics table define the interval: 

• QUANTILENO - a sequence number that identifies the interval
• HIGHVALUE - a value specifying the upper bound for the interval. 
• LOWVALUE - a value specifying the lower bound for the interval

These columns can be found in the following six Db2 Catalog tables: 

• SYSCOLDIST, 
• SYSKEYTGTDIST, 
• SYSCOLDIST_HIST, 
• SYSCOLDISTSTATS, 
• SYSKEYTGTDIST_HIST, and 
• SYSKEYTGTDISTSTATS.

 

Here’s a sample RUNSTATS specification that collects histogram statistics for the key columns of the indexes on tables in the CSMTS02 table space:

 

RUNSTATS TABLESPACE DB.CSMTS02

  INDEX ALL

  HISTOGRAM NUMCOLS 2 NUMQUANTILES 10

  SHRLEVEL(CHANGE)

  UPDATE ALL

  REPORT YES

 

Summary

 

If you have troublesome queries on data that is skewed or disproportionately distributed, you should consider gathering histogram statistics. Keep in mind that running RUNSTATS specifying HISTOGRAM will probably consume more CPU than just gathering frequency distribution statistics, the performance gains from improved access paths for your queries could more than offset that cost.

The Many Types of Mainframe Pricing

Whenever somebody starts talking about software pricing on the mainframe, or tuning to save on their monthly mainframe bill, I always wonder just exactly "what" they are talking about. Are they aware of all their company’s licensing agreements and all the intricacies they contain?

Before we move on, let me just state that the mainframe still makes good economic sense. The total cost of ownership (TCO) for the mainframe, when you add up all of the cost components (such as hardware, software licenses, storage, network, labor, power and cooling), is similar to or lower than comparable platforms.

So, don’t get me wrong, the mainframe is a great place to run your enterprise applications. And I am all in favor of tuning your code, infrastructure, and environment. But there are many reasons for doing so. Of course, the first reason is to improve the experience of your end users. The more efficiently tuned your software and the system it runs on, the more optimal and pleasant the experience of your end users will be. So, improving your customer experience is always a valid reason for tuning and optimizing your software.

That said, sometimes the intention of tuning efforts gets stated as to reduce costs. And that is a noble goal. But quite frequently, it seems, people undertaking the assignment to tune for cost optimization do not have enough information on achieving that goal!

What must be understood if you are looking to reduce mainframe cost by tuning? Well, I think the first thing you need to understand is what comprises your mainframe cost.

At a high level there is the cost of the IBM Z hardware that has to be taken into account. Of course, there is little that you can do to reduce hardware cost once you have purchased your mainframe. That said, by tuning your workloads to avoid reaching the peak utilization capacity of your mainframe you can avoid the need to upgrade to a new machine (a costly endeavor, indeed). This can be achieved by tuning activity to reduce CPU requirements or by moving workload around to smooth out the peaks. This is something that capacity planners should always be looking at and taking measures to achieve.

The next thing you need to understand is the mainframe software licenses that you have in place. This is not as easy as it might sound. Let’s start with the pricing metrics offered by IBM for Monthly License Charge (MLC) products. The IBM MLC products include operating systems, middleware, compilers, and other system software offerings. Examples of MLC products include: z/OS, z/TPF, CICS, Db2, IMS, COBOL, and so on. These products have a recurring charge that is applied each month for the right to use the product and also access IBM product support. Fair enough, and so far, not overly confusing.

But you also must know the specific MLC pricing metric used by your organization. And there are multiple, including:

           Advanced Workload License Charges (AWLC)

           Country Multiples License Charges (CMLC)

           Variable Workload License Charges (VWLC)

           Flat Workload License Charges (FWLC)

           Advanced Entry Workload License Charges (AEWLC)

            Entry Workload License Charges (EWLC)

            Tiered Workload License Charges (TWLC)

            System z New Application License Charges (zNALC)

            Parallel Sysplex License Charges (PSLC)

            Midrange Workload License Charges (MWLC)

            zSeries Entry License Charges (zELC)

            Growth Opportunity License Charges (GOLC)

Each of these metrics has specific requirements and specific conditions for how MSU usage is charged. Some of these metrics, such as AWLC and VWLC, offer sub-capacity licensing. That means that the software charges are based on the utilization capacity of the logical partitions (LPARs) on which the product runs. There are many nuances to how this actually works, but the bottom line is that if you can tune workloads that run during a peak rolling four-hour average (R4HA) period for the month, you can likely reduce your monthly bill.

Of course, there are also full-capacity metrics, where all software charges are determined by the full IBM-rated capacity (MSUs) of the CPC in which a product runs. Examples of full capacity based pricing metrics are PSLC and zELC. 

With the sub-capacity pricing metrics above your monthly bill can vary, sometimes substantially, from month-to-month. IBM introduced Tailored-Fit Pricing (TFP) for organizations looking for a predictable monthly bill. The general approach of TFP is to provide a predictable cloud-like pricing option for IBM z software. At a high-level, your overall usage for the last year is reviewed and an estimated increase is factored into the usage. Your monthly bill for the upcoming year is then 1/12 of last year’s total usage (plus the increase).

Then we have to consider International Program License Agreement (IPLA) products, which have an up-front license fee and an optional annual maintenance charge. IPLA products include tools for managing Db2, CICS, IMS, and others. To further complicate matters, at least when trying to tune for cost reduction, some mainframe IPLA products can be licensed at a sub-capacity level. 

And let’s not forget Container Pricing for IBM Z, where certain defined workloads can scale without impacting other workloads, either within an existing LPAR, separate LPARs, or multiple LPARs.

The Bottom Line

Given this general information, you can see how the choice of pricing metric will impact the efficacy of your tuning to reduce costs. Before undertaking any mainframe cost optimization effort be sure that you understand the different licenses for IBM Z software that are in effect at your organization. Furthermore, it is essential that you have effective tools for monitoring your CPU utilization, including identify monthly peaks. Armed with sufficient information and proper tools (including tuning and modernization tools), your chances of achieving cost reduction will increase greatly!

--- 

For additional information on mainframe pricing metrics and how zIIPs can help, check out my book on the topic: IBM Mainframe Specialty Processors: Understanding zIIPs, Licensing, and Cost Savings on the IBM Sytem z.

The Role of Db2 DBAs in Promoting zIIP Usage

 As most z/OS practitioners know, zIIP processors can provide significant benefits in terms of cost savings and improved performance. And one of the most significant areas for taking advantage of the benefits that zIIPs can provide is within Db2 for z/OS. As such, the role of Db2 DBAs in promoting zIIP usage can be critical.

It is important to note that not all features of Db2 for z/OS can run on zIIP processors, and that the extent to which a particular workload can be offloaded to zIIP processors depends on several factors, including the workload characteristics, system configuration, and the Db2 for z/OS version and licensing.

Of course, the first thing you need to be sure of is that the system is configured to utilize zIIPs. This means you must procure a license from IBM to use zIIPs. But you also need to ensure that you have configured your system appropriately for zIIPs, which is usually done by the system programming team. Configuration issues include:

• Ensuring that sufficient zIIP weight is defined for LPARs where Db2 for z/OS workloads run, and
  
  
• Using simultaneous multi-threading in the z/OS LPAR on z13 or later processors to increase zIIP capacity (when only one zIIP engine can be dedicated to the LPAR)

Once the system is properly configured, Db2 DBAs need to immerse themselves in understanding what type of workloads are zIIP-eligible. IBM documents the authorized zIIP uses for Db2 processing for each version of Db2 for z/OS. DBAs should bookmark this page (for Db2 13 for z/OS) and return to it for clarification as needed.

As a high-level guide, the following Db2 processing can run on zIIPs:

• Up to 100% of Db2 system agents processing running under enclave SRBs that execute in the MSTR, DBM1, and DDF address spaces are zIIP-eligible (except for P-lock negotiation). This includes things like buffer pool processing, log reading and writing, index pseudo-deletes and so on. In other words, things that Db2 will be doing as part of its general operation. These are not generally things that DBAs can influence or encourage much one way or the other, but can deliver benefits by offloading work from the general purpose CP to zIIPs.
  
  
• Up to 60% of distributed SQL that uses DRDA to access Db2 over TCP/IP and native REST calls over HTTP are zIIP-eligible. This can be a significant source for offloading work to zIIPs. DBAs can work with development teams to encourage the use of distributed SQL to further their organization’s usage of zIIPs.
  
  
• Up to 100% of parallel query child processes can be run on zIIPs, after you have reached a preset CPU threshold (which is defined by IBM for each specific model of IBM Z). DBAs can help to encourage parallelism, where appropriate, to further zIIP usage. This can be done by binding packages using DEGREE(ANY) or by setting CURRENT DEGREE to ANY. Furthermore, since parallelism can only be used by read-only queries, encourage developers to identify appropriate cursors as FOR READ ONLY.
  
  
• Up to 100% of XML processing for XML schema validation and non-validation parsing, as well as for the deletion of unneeded versions of XML documents. So, if you are using XML in your Db2 databases and applications, certain processing-intensive XML operations can be run on zIIPs.
  
  
• Many IBM Db2 utility processes are also zIIP-eligible. Up to 100% of the index maintenance tasks for LOAD, REORG, and REBUILD INDEX are zIIP-eligible. And up to 100% of the statistics-gathering portion of RUNSTATS is also zIIP-eligible. Planning and executing IBM Db2 utilities is something else that DBAs can do to encourage zIIPs usage. For example, encourage developers to use the LOAD utility instead of writing programs to load or bulk insert a lot of data whenever possible.
  
  
• And up to 100% of the SQL AI functions (SQL Data Insights) in Db2 13 for z/OS that is eligible to be run as a parallel query child process are zIIP-eligible. It is a bit more complex than that, because a portion of SQL statements that reference AI functions but are ineligible to be run as a parallel query child process may still be eligible if the SQL request is made through DRDA. Therefore, utilizing, where appropriate, the SQL Data Insights AI functions (AI_ANALOGY, AI_COMMONALITY, AI_SEMANTIC_CLUSTER, and AI_SIMILARITY) can help bolster the usage of zIIP processors.

I would be remiss if I did not mention that other ISVs offer Db2 utilities with varying degrees of zIIP eligibility; for example, BMC Software, Broadcom, and InfoTel. So, if you have Db2 utilities from vendors other than IBM, be sure to consult their documentation for details on their zIIP exploitation and proceed accordingly.

Furthermore, components of other types of system software may be zIIP-eligible, so be sure to investigate and document which products that you regularly use may be able to utilize zIIPs. For example, if you have heavy sorting requirements Precisely’s Syncsort MFSort can offload a good percentage of sort workload to zIIPs.

And do not forget about Java! Applications written in Java can be redirected to run on zIIPs. So, instead of writing a new application in COBOL (or another language that is not zIIP-eligible) consider using Java to create more zIIP eligible workloads. You might even take a look at where and when it makes sense to convert some existing workloads to run on a JVM to expand you zIIP usage.

On-going DBA zIIP Responsibilities

The DBA’s involvement with zIIPs does not end after workload has been made eligible. On-going activity is required to ensure effective zIIP usage. Db2 DBA must: 

• Monitor performance: Db2 DBAs should monitor system performance to ensure that zIIP processors are being used effectively. This involves tracking zIIP processor utilization, general-purpose CPU utilization, and overall system performance.
  
  
• Optimize performance: Furthermore, Db2 DBAs can help optimize performance by making changes to the system configuration or adjusting workload placement to improve zIIP processor utilization and overall system performance.

And finally, Db2 DBAs should take it upon themselves to educate other team members about zIIPs, their benefits, and how Db2 workloads can take advantage of zIIPs to reduce cost.

The Bottom Line

In summary, the role of a Db2 DBA in promoting zIIP usage involves identifying eligible workloads, ensuring the system is configured appropriately, monitoring performance, optimizing performance, and educating other team members on the benefits of zIIP processors. It can also include encouraging zIIP usage by educating and training developers on what types of processes are zIIP eligible. By promoting zIIP usage, a Db2 DBA can help improve system performance, reduce costs, and improve overall efficiency on IBM Z mainframes.