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 Quiet Erosion of Db2 for z/OS Expertise

There’s a shift happening in Db2 for z/OS environments that isn’t getting nearly enough attention. It’s not a new feature. It’s not a new release. It’s not even about modernization.

It’s about knowledge. And more specifically, the steady erosion of institutional knowledge. I’ve been talking with Db2 professionals across organizations, and the pattern is remarkably consistent. Teams are smaller. The work hasn’t shrunk. And the depth of experience is thinning out in ways that are subtle, but significant.

Let’s start with staffing.

It’s not unusual to hear about Db2 teams that have gone from a dozen or more DBAs down to a handful. For example, a BMC blog post reports of one team that dropped from 15 DBAs to 5 while supporting essentially the same application portfolio. That’s not just a reduction in headcount. That’s a two-thirds reduction in accumulated experience.

And experience matters in ways that are hard to quantify. Because the most valuable Db2 knowledge is rarely documented. It lives in people’s heads. Things like:

• Why that index was created
• Why that query was stabilized
  Why those statistics were manually modified in the Db2 Catalog
• Why that subsystem parameter was set the way it was

When the people with that institutional knowledge leave, the system doesn’t stop running. But something important is lost. The understanding of why things work the way they do. And that’s where the real risk begins.

We’re also seeing a shift in how responsibilities are handled. Traditional DBA roles are being fragmented. Some responsibilities are automated. Others are pushed to developers. Some simply fall through the cracks. The result is that fewer people have a holistic understanding of the entire Db2 environment. And that kind of fragmentation creates blind spots.

At the same time, new talent is entering the workforce. That’s a good thing. But they’re coming in with a very different skill profile. They know "modern" languages. They understand distributed systems. They’re comfortable with cloud platforms.

But deep Db2 for z/OS internals? Buffer pool behavior? Access path stability? The nuances of statistics and optimization? Those skills aren’t widely taught. And they aren’t quickly learned. So while headcount may be replaced, expertise is not.

And then there’s knowledge transfer. Most organizations recognize the problem. There are plans. There are mentoring initiatives. There are documentation efforts. But in practice, knowledge transfer is often inconsistent at best. I've worked at shops where a long-tenured Db2 DBA or sysprog retires without fully sharing their expertise. This can happen because it is difficult to transfer decades of knowledge in a couple of weeks or months. It can happen because time is not allotted for the transfer. And sometimes it can happen because the retiree wants to feel needed so they don't share unless absolutely required.

The bottom line is that it takes time and focus. And it requires that experienced professionals are available long enough to pass along what they know. Too often, that doesn’t happen. And so the knowledge simply walks out the door.

Perhaps the most concerning aspect of all this is how quietly it happens. Systems continue to run. Applications continue to process transactions. SLAs are still met. On the surface, everything looks fine. But underneath, there’s a gradual shift.

Decisions become more reactive. Tuning becomes more superficial. Defaults replace judgment. Over time, “optimal” becomes “good enough.” And “good enough” has a way of becoming the standard. Until something breaks. Or performance degrades in ways that no one quite knows how to diagnose. Or a modernization effort uncovers layers of complexity that no one fully understands anymore.

This isn’t a call for alarm. But it is a call for awareness. If you’re running Db2 for z/OS, the question isn’t just whether your systems are stable today. It’s whether your organization still understands them deeply enough to keep them stable tomorrow.

Because institutional knowledge isn’t something you can rebuild overnight.

And once it’s gone, you don’t just lose the past.

You lose the ability to make better decisions in the future.

Some Under-the-Radar Db2 13 for z/OS Features to Consider

Db2 13 for z/OS has been available for some time now, and soon (December 2025) it will be the only version of Db2 supported by IBM. So, we all should be either using Db2 13 already, or well into the process of migrating to Db2 13.

With that in mind, here are a few a lesser-known but compelling new features in Db2 13 for z/OS. That is, these enhancements have not received as much attention as the more news-hogging AI and performance new features. 

Online Removal of Active Log Datasets 

It is now possible to remove active log data sets while Db2 is up and running. This new capability is available in Function Level 500 and above. This can be accomplished using 

–SET LOG REMOVELOG 

Using this new option of the -SET LOG allows you to safely remove an active log data set from the BSDS without requiring downtime—as long as the data set isn't currently in use.

• If the data set is in use, the data set will be placed in a “REMOVAL PENDING” state, making it unavailable moving forward, until explicitly handled.

• If it's the next log to be written, the command fails with “REMOVAL PROHIBITED.”

• You can monitor this using -DISPLAY LOG DETAIL and use D GRS,RES=(*,dsname,*) to check log usage.

This feature greatly reduces operational risk and complexity during log maintenance in active environments.

DDL Lock Management

Db2 13 adds several new controls that help improve availability and reduce contention when performing DDL (data definition) operations:

• CURRENT LOCK TIMEOUT special register: Lets you override the system-level lock timeout (IRLMRWT) on a per-statement basis (values from 1–32767 seconds), limiting how long transactions queue behind DDL.

• DEADLOCK_RESOLUTION_PRIORITY global variable: Assigns a numerical priority (0–255) to help determine which process is likely to win in a deadlock. Higher values make a DDL process less likely to be chosen as a deadlock victim.

• System monitor profiles can now be configured — for both local and remote applications — to automatically set these values and even adjust package release behavior between RELEASE(COMMIT) and RELEASE(DEALLOCATE).

These features provide more granular control over lock management and thereby should help us reduce disruptions, improve the responsiveness of DDL, and help maintain service levels across transactional workloads.

Why These Features Deserve More Spotlight

• Operational impact without fanfare: While AI functions and accelerator improvements grab headlines, these enhancements quietly deliver high-impact capabilities—especially in high-availability, non-stop environments.

• Prevents outages during routine tasks: The ability to remove log datasets live and better manage DDL locking improves reliability and uptime for critical systems.

• Real-world value for DBAs and sysprogs: These are features that seasoned Db2 for z/OS professionals will deeply appreciate—and can use to simplify otherwise risky operations.

---

Bonus: Other Less-Heralded, but Useful Enhancements

From the 2024 continuous delivery updates (without function-level control), these two new capabilities also seem to be flying under the radar:

• Database utilities running on zIIP: APAR PH63832 allows portions of the COPY utility to leverage zIIP processing, reducing CPU costs.

• Targeted statistics deletion: APAR PH63145 lets you delete catalog statistics for a specific partition—without touching the whole object.

Maybe I have missed your favorite under the rader Db2 13 enhancement? If so, please share it with the community in a comment below!

Mainframe Relevance in an AI-First Era: How Db2 Fits

For decades, the IBM Z mainframe has been the backbone of mission-critical computing. Db2 for z/OS sits at the center of this story, reliably managing the world’s most sensitive and high-value data. Yet in today’s IT landscape, dominated by discussions of artificial intelligence (AI), machine learning, and data-driven transformation, the question inevitably arises: 

Where does Db2 fit in an AI-first world?

The answer is clear: Db2 remains central. In fact, it is uniquely positioned to power and support enterprise AI initiatives.

The Foundation of Trustworthy Data

AI is only as good as the data that feeds it. Models trained on incomplete, inconsistent, or inaccurate data produce unreliable outcomes. This is where Db2 shines. With its proven capabilities for data integrity, security, and availability, Db2 for z/OS provides the foundation of trustworthy, enterprise-grade data that AI depends upon.

Organizations already store their most critical operational data in Db2. Leveraging this data directly—without needing complex ETL processes that move it into less secure environments—offers a significant advantage. AI workloads can run against reliable, current data with governance and compliance controls already in place.

Db2 and Embedded AI Capabilities

IBM has not stood still in bringing AI to Db2 for z/OS. For example, Db2 AI for z/OS (Db2ZAI) uses machine learning models to improve database performance. By analyzing workload patterns, Db2ZAI can recommend optimal buffer pool configurations, predict query performance, and even assist the optimizer in choosing the best access paths. This closes the loop: AI is being applied inside Db2 itself to make database management more intelligent and efficient.

Similarly, SQL Data Insights brings AI-powered analytics directly into Db2 for z/OS, enabling built--in SQL functions to use AI for anomaly detection and data pattern recognition without requiring external AI platforms. These capabilities allow organizations to unlock the hidden value in their Db2 data more quickly and intuitively.

Synergy with IBM Z and AI Acceleration

The hardware platform itself reinforces this story. The latest IBM z16 and z17 mainframes incorporate on-chip AI acceleration with the Telum processor and Spyre AI accelerator. This means that inferencing can be performed where the data resides, avoiding latency and risk associated with data movement. For financial institutions detecting fraud, retailers optimizing transactions, or insurers assessing claims, the ability to apply AI in real-time on operational data is transformative.

Db2, running on these systems, is directly positioned to take advantage of this capability—turning the mainframe into not just a system of record, but also a system of insight and decision.

The DBA’s Evolving Role in an AI-First Era

As AI integrates more deeply into Db2, the role of the DBA also evolves. No longer solely the guardian of performance tuning and availability, the modern DBA must understand how AI tools are being embedded in their environment. This includes evaluating AI-driven recommendations, integrating AI queries into business applications, and ensuring that AI workloads are governed and secure.

Rather than diminishing the DBA’s importance, AI amplifies it. Human expertise is needed to validate, interpret, and operationalize AI-driven insights in ways that align with business priorities and regulatory requirements.

Conclusion

The narrative that positions mainframes and Db2 as “legacy” is misguided. In reality, Db2 for z/OS sits at the heart of enterprise AI adoption. With its unmatched reliability, native AI capabilities, and synergy with IBM Z’s AI-accelerated hardware, Db2 is not only relevant but critical in an AI-first world.

For organizations pursuing AI, the best path forward often starts with the data they already trust most—residing in Db2. The mainframe is not being left behind by AI; it is, in fact, helping to lead the way.

Intelligent Automation of Db2 Administration and Management

It is vitally important to ensure that your Db2 databases and systems are running effectively and efficiently. And this requires the diligent application of administration and management tasks on a regular basis. Failure to keep up with the status of your Db2 databases can result in poorly performing applications, unavailable data and systems, and ultimately, lost revenue.

Automation can help. But what do we mean by automation? Sure, most of you inherently know what automation is, at least at a high level. But it is worthwhile to consider a brief definition. Automation involves reducing human involvement in your activities, turning things over to intelligent software. The goal is to reduce the amount of time, effort, and human error involved in maintaining efficient systems. That sounds good, right?

Nevertheless, many IT professionals have an aversion to automation. We are happy to automate everyone else’s job but not our own. As experts on technology, you’d think we’d be able to see the fallacy of this belief. By developing computerized applications to support business processes, we automate just about every job in our organizations. But try to tell a DBA to automate their utilities or to use advanced autonomics to direct their actions and you’d think you just insulted their mother. Many technology folks resist automation for fear of losing control or perhaps, losing their job. These fears are understandable, but not really justifiable.

There is an IT skills shortage and companies want to hire more IT professionals than are available. And we are over-worked – who among us really wants to work 12-hour days all the time? And in the mainframe world, the workforce is aging and we need to do something as the experienced folks retire and move on.

The truth is, most IT tasks and procedures can be streamlined and made more efficient using automation: automated systems management, database administration and tuning, and yes, even application development. Automation will not be able to completely replace IT professionals any time soon, but it is important as organizations struggle to cope with a shortage of skilled IT professionals. By turning some of the work over to the computer, IT can become more efficient, more effective, and provide a higher ROI to the business.

One of the biggest challenges IT professionals in general, and DBAs in particular, face is the growing complexity of technology and software. Contributing to this complexity is the growing number of devices, the increasing number of systems that interoperate with one another, and the growing number of parameters and options available in systems software and utilities.

 

A Day in the Life of a DBA

To help us understand the growing complexity and need for automation, let’s examine a typical day in the life of a Db2 DBA. It can be quite hectic. The DBA is required to maintain production and test environments while at the same time keeping an eye on active application development projects, attending strategy and design meetings, helping to select and evaluate new products, and connecting legacy systems to the web. And Joe in Accounting, he just submitted that “query from hell” again that is bringing the system to a halt, can you do something about that? All these things can occur within a single DBA workday.

Databases are at the center of modern applications. If Db2 fails, applications fail, and if applications fail the entire business can come to a halt. If databases and applications fail often enough, the entire business can fail. Database administration therefore is critical to the on-going success of modern business.

So, growing complexity and overwhelmed DBAs contribute to the need to automate. Automation can optimize management and reduce complexity by instrumenting and running tasks based on tried-and-true best practices.

Automating Db2 Utilities

Db2 utilities are a prime candidate for automation because they need to be run regularly and there are industry best practices and statistics that can be used to automate them. Not to mention the fact that the complexity of Db2 utilities is increasing all the time. Just compare the number of parameters and options available today for Db2 13 for z/OS versus what was available in the past.

For example, do you understand how to run your utilities online, while data is accessible to your applications? Do you use parallel index build with the LOAD, REORG and REBUILD INDEX utilities? Have you ever loaded partitions in parallel? How about using the LISTDEF, TEMPLATE, and OPTIONS statements for controlling utility operations? And these are only a few of the innovations made to IBM’s Db2 utilities over the years. Indeed, the list of improvements made to IBM utilities in Db2 13 for z/OS is challenging, and some DBAs have not really implemented any new utility functionality in years.

Truly, automating utilities is a best practice, but many shops do not adhere to this best practice. Instead, they just create utility jobs for every object and schedule them to run regularly. This set-it-and-forget-it mentality means that most utility jobs get built when the object is created and then rarely examined ever again. The decision on how frequently the jobs will be run is made up-front and never re-examined, unless there are performance, availability, or recovery problems.

If you fall into this category of utility scheduler, how frequently do you schedule your REORG, RUNSTATS, and COPY jobs to run. Weekly, monthly, quarterly? There is not usually a lot of thought put into the frequency of execution. And even if you meticulously analyze your scheduling decision when the object is created, are you sure that decision is still sound now, perhaps many years later? Few DBAs re-assess the situation over time to see if their initial scheduling decisions were accurate or still stand.

At any rate, automating utilities based on thresholds is a better practice. Automation can ensure that you are running your REORG, RUNSTATS, and COPY utilities when it makes sense, instead of on a rigid schedule. If you run these utilities too late, you risk recoverability and availability issues, as well as increased cost due to poor performance and increased CPU usage. And if you run the utilities too soon, you are consuming CPU and I/O that you do not need to run, and that, too, increases costs.

The goal is to run your Db2 utilities at just the right time. And that can be done using Db2 statistics. Of course, each utility needs to examine different real-time statistics to run them at the proper time. You should consider a tool, such as InfoTel Corp’s iDBA, which can help you to implement intelligent DBA automation. Such a solution can consider all the pertinent parameters and statistics and determine what the appropriate maintenance tasks and utilities that need to be run based on your environment and situation… and not a schedule from long ago.

Mixing Db2 Database Administration with DevOps - Summary

Recently, I posted a series of blog entries on DevOps and Db2 DBA here on the Db2 Portal blog site. The posts dealt with the many issues and considerations that need to be addressed as mature Db2 shops embrace DevOps practices and procedures.

This post is a short one that collects together links to all 7 of the posts in this series. You can use this post to review all of the posts in this series on DevOps Db2 DBA...

Part 1: Intro to DevOps

Part 2: The DevOps Toolchain

Part 3: Automating the DevOps Toolchain

Part 4: Database Schema Change and DevOps

Part 5: SQL Performance Testing

Part 6: Treat Dev and Ops as Equals

Part 7: Culture and The Bottom Line

I hope that you find the information in these posts to be informative and useful. And please, feel free to add your observations, thoughts, and experiences to the comments in this post (or any of the 7 DevOps posts linked above).

Mixing Db2 Database Administration with DevOps - Part 7: Culture and The Bottom Line

When DevOps is embraced by an organization it should mean that DBAs get aligned more closely with development and appli­cations than in the past. By deploying agile development, with DBAs participating in teams along with the developers, you get increased cooperation and communication between the folks coding the application (that’s Dev) and the folks developing and managing the database (that’s Ops, or the DBAs).

So an overarching change required to succeed with DevOps is that DBAs should be work­ing in teams with developers, instead of in teams of other DBAs... at least for periods of time when development projects are very active. For some applications, a permanent DBA, or team of DBAs, may be assigned. For others, the DBA may rotate back and forth between the development team and a centralized DBA team.

Regardless of the pattern, DBAs are becoming more appli­cation-savvy. That’s a good thing because with improved appli­cation knowledge the DBA will be better able to administer the database for an application’s needs as the app moves from devel­opment to test to production.

The Bottom Line

Db2 for z/OS administration and management techniques need to adapt to the modern practices of agile application development and DevOps. But this is easier said than done.

It includes adapting the behavior of both developers and operations to be more collaborative between developers and operations (DBA) personnel. It also requires automating as much of the software development lifecycle as possible into a DevOps toolchain to reduce development time and deliver a better return on investment to application development and support.

Mixing Db2 Database Administration with DevOps - Part 6: Treat Dev and Ops as Equals

Although DevOps is widely accepted in the industry, it is not without some problems. The biggest problem, as I see it, is that many DevOps shops, emphasize the “Dev” over the “Ops” causing important operational aspects to be overshadowed by development concerns. And the “Ops” part is where you will find database management and administration.

Far too often, control of the application delivery process is driven by development, sometimes without the traditional control and oversight of the DBA group. Without the expertise of the DBA, the delivery and integration process can fall apart because the oversight and administration required for database performance and change management cannot be accomplished in the same exact way as applications.

A particular problematic perception is that the DBA acts as a barrier to progress. The developers work on their code, test it, and are ready to move forward, only to be barred by the DBA from doing so. So, developers view the DBA as a bottleneck.

But it important to understand that DBAs are not just stopping things for the fun-of-it. There is important work that the DBA must perform to ensure that the application can be turned over to production without causing problems. This includes:

• Reviewing SQL code and access paths for performance under production volume and workload.
• Ensure that everything is production-ready: this includes statistics collection, index analysis and design, join method analysis, system parameter analysis, and more.
• Reviewing database structure changes required to support the application.
• Building scripts to ensure a successful migration.
• Coordination of database changes with application changes.
• Ensuring that all support jobs (backup, recovery, reorg, etc.) are in place for every database object.
• Determine and mitigate any impact of the new application (or changes) on any other existing applications and databases.

When there is little, or no, communication between development and database administration until the code is ready to be delivered to production, it will take time to allow the DBA to perform their portion of the application delivery.

Of course, if the DBA and application teams communicate and coordinate their workload the perceived bottleneck can be eliminated. This is at the core of DevOps and is sometimes referred to as “shifting left.”

The shift-left ideology arose out of the application testing discipline. Software development typically progresses from Requirements to Design to Coding to Testing to Deployment and then to Support, which can be viewed as a chain, starting from the right and moving to the left:

          Requirements – Design – Code – Testing – Deployment – Support

With a shift-left development mentality, processes on the right are moved to the left, so they are performed earlier in the development lifecycle. So, the goal with database administration should be to shift tasks to the left instead of performing them all right before Deployment. And to enable developers and DBAs to work together and communicate as team members with the same goal: servicing the customers and end-users.

But it should not, indeed cannot, be that DBA practices and procedures are shifted away. DBA functionality still needs to be performed to ensure the accuracy and effectiveness of corporate data!

Mixing Db2 Database Administration with DevOps - Part 5: SQL Performance Testing

Although implementing database schema changes is the most important component when incorporating database structures into your DevOps pipeline, it is not the only thing to consider. It is also important to be able to analyze and optimize SQL performance within your application code.

As anyone who has written SQL knows, it is a very flexible language. There are multiple ways to write SQL queries to achieve the same results. For example, you can combine multiple tables using a join or a subselect and achieve the same results. But each SQL formulation is likely to perform differently, one better than the other. And this is but one example of the various ways you can build SQL statements to perform the same function.

The development mindset is usually to write code that matches the requirements and delivers the expected results, not necessarily to assure the best performance. Therefore, SQL performance testing should be carried out on all programs before they are migrated to a production environment. Failure to do so will likely result in poorly performing applications.

In a DevOps environment, the best approach is to measure, analyze and improve SQL statements at all stages as your code progresses from development to testing to production. The more SQL performance testing that can be accomplished by developers the earlier performance problems will be found and corrected. And that means the cost of delivering high-quality Db2 applications will decline.

However, things are not as simple as just running your program and evaluating its performance metrics. The data that you use in your test environment will not be the same as your production data. Typically, you will have less test data than you do in production. So, if you run the RUNSTATS utility on your test data you will get different statistics than in production, which means you will also get different access paths and performance results.

Setting up the test environment with production statistics and modeling the environment to mimic production is an important aspect of performance testing during development.

With the proper setup and tooling, developers can examine the access paths of their SQL statements to judge their efficiency. Of course, tools that can simplify this process are needed to speed up SQL performance testing. Such tooling should be able to capture Explain information, display it graphically and combine it with pertinent catalog statistics, store a repository of access paths by statement, compare access paths, identify changes, and make recommendations. Ideally, the tool should be integrated into the DevOps toolchain so that information is automatically captured and analyzed each time the program is compiled and bound. 

Considerations should also be made for testing specific use cases for performance. For example, consider skewed data. Db2 assumes that data values are mostly uniformly distributed throughout the data. However, not all data is uniformly distributed. RUNSTATS can be used to capture information about non-uniformly distributed and skewed data.

Another performance testing consideration is to always try multiple SQL variations, especially for queries that access a lot of data or have complex access paths. Do not just find one SQL formulation that works and stick with it. Remember that you can code multiple variations of SQL statements that return the same data, but that perform quite differently.

Tools that can help set up testing for various use cases and SQL variations will be needed for integrating SQL performance testing into the DevOps toolchain. There are a wide variety of vendors and solutions for managing Db2 for z/OS SQL performance, but I am not aware of any that have been fully integrated into the DevOps toolchain.

Mixing Db2 Database Administration with DevOps - Part 4: Database Schema Change and DevOps

Traditionally, the DBA is the custodian of database changes. But the DBA typically is not the one requesting the change. Usually, a programmer does that. There are times when the DBA requests changes, such as to improve performance or to utilize new features, but this is not as common as development changes. Regardless of who requests the change, the DBA must be involved to ensure that each change is performed successfully and with no impact on the rest of the database. 

After moving to a DevOps approach, there is a shift that places more of the responsibility for database change on the developer. But to succeed, the DBA still must be involved to oversee, analyze, and approve any changes. As is common with DevOps practices, it is desirable to automate as much of the process as possible in order to remove manual, error-prone tasks and increase the speed of delivery. 

This requires a tool that automates complex database changes and integrates into the DevOps toolchain. Without such a tool incorporating database changes into application delivery and deployment remains a slow, mostly manual process. 

To effectively make database changes, the DBA needs to consider multiple issues, the most important of which are the appropriateness of the change in terms of the database design and the impact of the change on all other database objects and applications. Additionally, the DBA must determine if the change conforms to standards, how best to implement the change, and the timing of the change. 

The ideal arrangement is for database schema changes to be incorporated into the DevOps toolchain using a tool that allows developers to request changes. Those changes should be analyzed and compared against standards and rules for conformance. Non-compliant changes should automatically be referred back to the developer for modification and resubmission. Compliant changes should be accepted and cause a script to be generated using the most appropriate mechanisms to implement the change. This is a non-trivial activity which if done properly can eliminate a lot of manual downtime. The generated script should be presented to the DBA for review and upon acceptance, be implemented.

Db2 for z/OS, like all of today’s major DBMS products, do not support fast and efficient database structure changes for all types of change. A quick example: try to add a column to the middle of an existing row. This requires a complex series of metadata capture, data unloading, DROP, and CREATE statements. And don’t forget about all of the dependent objects and structures like indexes, referential integrity, authorizations, and so on.

Adding to the difficulty of making schema changes is the fact that most organizations have at least two, and sometime more, copies of each database. There may be copies of the database at different locations or for different divisions of the company. And at the very least, a test and a production version will exist. But there may be multiple testing environments—for example, to support simultaneous development, quality assurance, unit testing, and integration testing. Each database change will need to be made to each of these copies, as well as, eventually, to the production copy. So, you can see how database change can quickly monopolize a DBA’s time.

You can see how a robust, time-tested process that is designed to effect database changes is required. BMC, Liquibase, and IBM all offer DevOps-integrated database change management solutions for Db2 for z/OS.

As you review their capabilities, be sure that the tooling supports the type of changes your organization requires. For example, be sure that the tool is aware of all the different requirements for making any change you may need.

From my experience, vendors can focus on the development side of the DevOps experience and minimize the complexity of database change. All too often the tool demo shows a request to add a column... how boring is that? How about changing a data type from numeric to text? That would be a bit more challenging... or requiring a tablespace be converted to Universal from segmented as part of the change (perhaps to support larger sizes)?

Mixing Db2 Database Administration with DevOps - Part 3: Automating DevOps Toolchain

Understanding what is meant by DevOps and the many requisite pieces of the DevOps toolchain is just the first step in adopted DevOps.

The key to success with automating DevOps is a well-constructed toolchain for automating all of the afore-mentioned processes in an integrated and cooperative manner. If you have been an IT professional for any length of time most of the categories we just discussed will not be new to you. What is new, however, is likely the integration of the tools hooked up together to work in concert for the purpose of delivering application software quickly and accurately.

Additionally, the list of activities we just reviewed is not comprehensive. A glaring omission is the lack of integration with your database management systems. The orchestration of application changes in the DevOps toolchain is fairly well established, but this is not so much the case for database changes. And this has especially been the case for the mainframe world in terms of incorporating Db2 for z/OS into the DevOps toolchain.

As any good DBA or performance analyst knows, there are multiple management and operational activities required to assure functionality and optimal performance when applications rely on Db2 databases for persistent storage. Perhaps the single most difficult task is managing database schema changes. 

Mixing Db2 Database Administration with DevOps - Part 2: The DevOps Toolchain

Adopting a DevOps approach to application development is all about moving faster. Automating the multitude of processes required during software development and management is a core part of increasing speed and enabling faster development.

As mentioned in the first part of this series, a DevOps toolchain is used to automate the development process. A toolchain is a set of software tools used to perform a complex development task... or to create a software product. The software tools that comprise a toolchain typically are executed sequentially with the output or state of one tool becoming the input for the next, but this is not a hard-and-fast requirement.

A DevOps toolchain therefore is a set of tools that interoperate with one another to aid in the delivery, development, and management of software applications throughout the SDLC. The following components typically are involved in putting together a useful DevOps toolchain.

Orchestration enables the automation of coordinating and managing the SDLC. Whereas automation refers to a single task or capability, orchestration automates a complex, multi-step process. The orchestration tool drives the entire DevOps process. Jenkins is the most popular DevOps orchestration tool.

Continuous Integration (CI) and Continuous Delivery (CD) are important components of the DevOps toolchain. Typically, CI and CD are implemented into collaboration tools that provide a dashboard for integration and delivery of software. Bamboo and Jenkins offers CI and CD capabilities. 

Tools that deliver Configuration and Resource Management provide an automated method for maintaining your systems and software infrastructure in a consistent state. This can include servers, storage, networking, and software, with the goal of managing the environment in a known, desired state. Examples of configuration management tools include Chef, Ansible, and Puppet.

Source Control software is used to manage who can change code, who is changing code, and track those changes. It enables developers to work collectively on a complex software project without impacting code changes other developers are making. GitHub is the most popular modern source control tool for DevOps projects.

Collaboration tools aid in the scheduling and tracking of code sprints by delivering transparency to the process for all stakeholders. Many different types of tools can fall into this category, including communication, project management, and service management software. Tools that help developers to catalog and track issues help to speed delivery by improving the response taking corrective action. 

Whenever code is being written, it must also be tested. Automated Software Testing tools can improve the speed and quality of software by quickly identifying defects and validating the accuracy of code. 

Deployment tools automate the migration and implementation of application code throughout your environments. It facilitates rapid feedback and continuous delivery in agile development while providing the audit trails, versioning and approvals needed in production. A popular deployment tool IBM UrbanCode Deploy is an example of a popular deployment tool for DevOps.

A Container is a standard unit of software that packages up code and all its dependencies so the application runs quickly and reliably from one computing environment to another.  Using a container developers can incorporate everything required to run an application to enable it to be portable, without being impacted by differences across multiple environments. Docker is the most popular container software. Kubernetes is frequently used with Docker as a container orchestration system that automates application deployment, scaling, and management.  

Monitoring software that can oversee the performance and operation of applications and services is a crucial component of a DevOps pipeline. There are many types of monitoring software including IT infrastructure (operating system, network, and database); the end-user response and experience; and for the performance and functionality of the actual applications themselves. 

Mixing Db2 Database Administration with DevOps - Part 1: Intro to DevOps

Organizations of all types are being impacted by the transformation to the modern digital economy. This digital transformation, driven by the 24/7 expectations of users and customers to access data and apps at any time, from any device, requires the ability to rapidly change technology and software. This means that today’s businesses have to deliver and improve the services and software used by their customers faster than ever before.

To survive and thrive in the new digital economy requires that organizations adopt new and faster methods of developing, testing and delivering application software. The clear trend for modern application development is the use of agile development and DevOps techniques. Why have these techniques been adopted for software development over long-standing techniques like the waterfall approach? It all comes down to improved collaboration, resulting in faster development and therefore a quicker return on investment.

An agile development methodology involves the organization of the team and goals such that software development is iterative, incremental, and evolutionary. Using an agile approach, development and testing activities are undertaken concurrently, unlike the traditional Waterfall development model. Large software projects are broken into pieces such that immediate value can be delivered quickly, in smaller pieces, instead of waiting for a monolithic product to be completed. With continuous testing and integration, the smaller software pieces can be integrated into a larger, final deliverable.

Another key methodology used to speed up the delivery of software is the DevOps approach, which results in small and frequent code changes. Its name is an amalgamation of Development and Operations. As such, DevOps relies on agile development, coupled with a collaborative approach between development and operations personnel during all stages of the application development lifecycle. Such an approach can significantly reduce the lead time for changes, lower the rate of failure, and reduce the mean-time-to-recovery when errors are encountered. With such benefits that can accrue, it is no wonder that DevOps and continuous delivery are gaining in popularity.

Instead of long software development projects that may not deliver value for months, or perhaps even years (such as are common using the Waterfall development methodology), an agile DevOps approach delivers value quickly, and then incrementally over time. DevOps enables the continuous delivery of new functionality demanded by customers in the digital economy.

Succeeding with DevOps however, requires a cultural shift in which all groups within IT work in collaboration with one another, and where management endorses and cultivates this cultural change. Because DevOps relies upon incremental development and rapid software delivery, your IT department can only thrive if there is a culture of accountability, collaboration, and team responsibility for desired business outcomes. Creating such a culture is not easy. 

The goal of DevOps is a constantly repeating cycle of continuous development, continuous integration and continuous deployment. To achieve such a high level of continuous delivery also requires in-depth automation of the entire development lifecycle. This is often referred to as the DevOps toolchain.

A Db2 for z/OS DBA Poem

I'm a hip-hopping monster of the CRT,

All the programmers here, they be envying me,

I'm a DBA, a master of data,

Work with Db2 and I diss all the haters!

I strive every day to give smooth operation,

And to keep the data in perfect formation.

My eyes are trained to spot any trouble,

To fix it before the data turns to rubble.

My hands deftly skip over the keyboard, 

Tuning and tweaking all the inefficient code.

I know all the ins and outs of Db2,

Every command, every option, every ZPARM, I do.

My skills are honed by years of practice,

Through all the challenges, I've gained my status.

My expertise and skills are sought by many,

But few can match all my skills, or any.

I'm a Db2 DBA, indeed I am...

A guardian of data, and a z/OS fan!

Db2 Education is a Constant Requirement

Ensuring access to proper Db2 educational materials should be one of the first issues to be addressed after your organization decides to implement Db2. But education sometimes falls through the cracks... 

Does your organization understand what Db2 is? How it works? For what purposes it is needed at your shop? How it will be used? Without a sound understanding of Db2, its components, and features, it is unlikely that you will be able to use Db2 to its best advantage. A basic level of Db2 knowledge can be acquired through a short Db2 fundamentals class for the IT personnel charged with making Db2 a success at your ­organization. But long-term success with Db2 requires ongoing education.

After addressing the basics of Db2 education, you must support a curriculum for on-going Db2 education for your organization. This support falls into four categories: 

The first category of training is a standard regimen of SQL and Db2 programming training to be used by application developers. Every programmer should receive basic training on SQL, education on how to embed SQL in the programming languages they will be using, and possibly additional courses on using Db2 with specific infrastructure software like MQ, WebSphere, CICS, and IMS. Also, with the importance of distributed access to Db2 these days, a course should be made available on that topic, with specific portions that address the technology used by your shop to provide distributed Db2 data access. If this basic level of Db2 education is not required for every Db2 programmer, then Db2 application performance will surely suffer as untrained coders write inefficient and incorrect SQL. This type of training can be delivered as instructor-led training by in-house SQL coding experts, as computer-based training, or as external classes. It should also be augmented with reference material such as books, articles, and blog posts.

The second category of education support is external training for special needs. This support includes education for database administrators, technical support personnel, and performance analysts. Additionally, your organization needs to plan for ongoing education to keep appropriate personnel up-to-date on new versions and releases of DB2. Although IBM offers great courses for new Db2 releases, several third-party vendors such as ProTech, Interskill Learning and KBCE regularly offer in-depth training and release-specific Db2 courses and lectures.

The third category of education is in-house, interactive training in the form of videos, computer-based training, and instructor-led courses. These courses should be used to augment and refresh the formal training given to your Db2 professional staff. 

The fourth, and final category of support, is reference material—for example, IBM’s Db2 manuals, Db2 books (such as DB2 Developer's Guide and A Guide to Db2 Performance for Application Developers), vendor-supplied white papers, and industry publications and periodicals. The current IBM manuals for Db2 are always available on the web, as well:

IBM Db2 12 for z/OS documentation

 

IBM Db2 13 for z/OS documentation

Of course, you should consider augmenting the standard IBM Db2 manuals with IBM redbooks. IBM redbooks provide in-depth, detailed coverage of a specific technology topic. IBM publishes redbooks on multiple subjects, including Db2, IMS, CICS, z/OS, and many other topics. IBM redbooks can greatly assist Db2 technicians working to understand a feature or nuance of DB2. You can download IBM red books for free in Adobe Acrobat format over the Web at http://www.redbooks.ibm.com/

IBM also offers many other useful sources of DB2 information on the Web including white papers, articles, and book excerpts from IBM Press publications. As does the International Db2 User Group (IDUG).

Independent Software Vendors (ISVs) are another rich source of Db2 information. The major vendors provide in-depth technical papers on features of Db2 that would be difficult for most shops to research in the same detail. Be sure to watch for and request the great Db2 Catalog posters, reference guides, white papers and eBooks available from BMC Software and Broadcom. There are other great Db2 ISVs who offer educational information, too, such as the newsletters, white papers, and webinars; these include InfoTel, SEG, and UBS-Hainer, among others.

All of these educational components—in-house education, external education, and industry publications—are useful for learning how you can use Db2 effectively. You would be wise to have a mix of material that supports more than one of the categories outlined previously. In this way, you provide a varied learning environment that meets the needs of all students. This varied learning environment allows each student to learn in the most conducive way for him or her. Plan to provide an on-site library of educational material addressing the following subjects:

• Introduction to relational databases
• Introduction to DB2 and SQL
• Advanced SQL
• Programming DB2 using (your languages of choice)
• Programming DB2 in batch
• Programming DB2 using TSO, CICS, and IMS
• Programming DB2 and the Web
• Creating DB2 stored procedures, triggers, and UDFs
• Programming DB2 in a Distributed Environment
• Debugging and Problem Analysis

You also might want to have an introductory Db2 database administration course to train new DBAs. In addition to this basic education library, plan to provide advanced education for technical Db2 users, such as DBAs, technical support personnel, and technical programmers and analysts. Advanced DBA topics (such as Sysplex Data Sharing, performance management, and backup/recovery) should be left to instructor-led training courses because of the complex nature of Db2 database administration. Additional advanced topics to consider include system administration (for systems programmers) and disaster recovery. Many vendors, including the ones metntioned above, offer these classes. Searching for smaller consulting firms and local resources is also prudent; these firms sometimes can provide courses tailored to your installation-specific needs.

The advanced education program should include allocating time to attend area user groups meetings, such as the International Db2 User Group (IDUG) conferences held every year in North America, EMEA, and Canada. 

The bottom line is simple, though. Plan for ongoing Db2 education for your DBAs, programmers, and analysts... or plan on failing.

My Speaking Schedule at IDUG Db2 Tech Conference in Boston (2022)

 Just a quick note to let everybody who is coming to Boston in July for IDUG know what I will be speaking about and when my presentations are scheduled!

First of all, my regular IDUG session this year is titled "Things Your DBAs Hear... and how to stop making them crazy!" This session is based on my decades of experience as a DBA and as a consultant. This session walks you through interactions between developers and DBAs, in a light-hearted way. All of them are real-life examples of actual conversations I've been in (or observed).  Attend this session to learn what frustrates DBAs and how improving your communication can improve your relationship with your DBAs... and therefore improve your development  efforts!  This is session E11, and it will be delivered on Wednesday, July 13 at 11:30 AM.

I will also be presenting at two different VSP sessions, one on Tuesday and another on Wednesday (this is the 10:15 AM time slot on both days).

On Tuesday, I will be presenting with InfoTel on the topic "To Protect and Preserve: Treat Your Data Properly or Pay the Consquences." This session will discuss vital data management issues such as data archiving and data protection (my portion), as well as some products that can help you manager your data better (the InfoTel portion). 

On Wednesday, I will be presenting "How to Accelerate Db2 SQL Workloads... Without Db2!" for Log-On Software. This session takes a look at in-memory trends and issues, and shines a light on how QuickSelect can improve the performance of SQL queries.

I hope to see you at this year's IDUG North American conference the week of July 11, 2022. If you are there, come see one (or all) of my sessions... and be sure to say "Howdy!"