Understanding Lock Escalation: Managing Resource Contention

Ensuring efficient data access while maintaining data integrity is critical to both performance and stability. One of the mechanisms Db2 employs to manage this balance is lock escalation. Though this feature is essential when managing large numbers of locks, improper handling can lead to performance bottlenecks. Understanding lock escalation and how it impacts your Db2 environment is crucial for database administrators (DBAs) seeking to optimize operations.

What Is Lock Escalation?

Lock escalation is Db2’s method of reducing the overhead associated with managing numerous individual row or page locks. Instead of holding thousands of fine-grained locks, Db2 “escalates” these to coarser-grained table or table space locks. This happens automatically when a session’s lock usage exceeds a predefined threshold.

The primary goal of lock escalation is to reduce the system resources spent on tracking and maintaining a large number of locks. Without escalation, too many locks could overwhelm system memory or negatively impact performance due to the lock management overhead. Escalating to a table (space) lock allows Db2 to control resource consumption and avoid these issues.

When Does Lock Escalation Occur?

There are two limits to be aware of. The first is NUMLKTS, which specifies the maximum nunber of locks a process can hold on a single table space. This is the default and it can be overridden in the DDL of a tablespace using the LOCKMAX clause. When NUMLKTS (or LOCKMAX) is exceeded, Db2 will perform lock escalation.

The second is NUMLKUS, which specifies the maximum number of locks a process can hold across all table spaces. When a single user exceeds the page lock limit set by the Db2 subsystem (as defined in DSNZPARMs), the program receives a -904 SQLCODE notification. The program can respond by issuing a ROLLBACK and generating a message suggesting that the program be altered to COMMIT more frequently (or use alternate approaches like executing a LOCK TABLE statement).

Lock escalation may also occur due to the lock list or lock table approaching its capacity. In such cases, Db2 may escalate locks to prevent the system from running out of resources.

Additionally, keep in mind that as of Db2 12 for z/OS FL507, there are two new built-in global variables that can be set by application programs to control the granularity of locking limits.

The first is SYSIBMADM.MAX_LOCKS_PER_TABLESPACE and it is similar to the NUMLKTS parameter. It can be set to an integer value for the maximum number of page, row, or LOB locks that the application can hold simultaneously in a table space. If the application exceeds the maximum number of locks in a single table space, lock escalation occurs.

The second is SYSIBMADM.MAX_LOCKS_PER_USER and it is similar to the NUMLKUS parameter. You can set it to an integer value that specifies the maximum number of page, row, or LOB locks that a single application can concurrently hold for all table spaces. The limit applies to all table spaces that are defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. 

These new FL507 options should be used sparingly and only under the review and control of the DBA team.

The Impact of Lock Escalation

While lock escalation conserves system resources, it can also lead to resource contention. By escalating locks from rows or pages to a table-level lock, Db2 potentially increases the chances of lock contention, where multiple transactions compete for the same locked resource. This can have a few side effects:

• Blocking: When an entire table is locked, other transactions that need access to that table must wait until the lock is released, even if they only need access to a small portion of the data.
• Deadlocks: With more coarse-grained locks, the likelihood of deadlocks can increase, especially if different applications are accessing overlapping resources.
• Performance degradation: While escalating locks reduces the overhead of managing many fine-grained locks, the side effect can be a performance hit due to increased contention. For systems with high concurrency, this can result in significant delays.

Managing Lock Escalation

A savvy DBA can take steps to minimize the negative impacts of lock escalation. Here are some strategies to consider:

1. Monitor Lock Usage: Db2 provides tools like DISPLAY DATABASE and EXPLAIN to track locking behavior. Regularly monitor your system to understand when lock escalation occurs and which applications or tables are most affected.

2. Adjust Lock Thresholds: If escalation is happening too frequently, consider adjusting your LOCKMAX parameter. A higher threshold might reduce the need for escalation, though be mindful of the system’s lock resource limits. Additionally, consider the FL507 built-in global variables for difficult to control situations. 

3. Optimize Application Design: Poorly optimized queries and transactions that scan large amounts of data are more prone to trigger lock escalation. Review your applications to ensure they are using indexes efficiently, and minimize the number of locks held by long-running transactions.

4. Partitioning: Partitioning larger tables can help mitigate the effects of lock escalation by distributing locks across partitions.

5. Use of Commit Statements: Frequent commits help release locks, lowering the risk of escalation. Ensure that programs are committing frequently enough to avoid building up large numbers of locks. A good tactic to employ is parameter-based commit processing, wherein a parameter is set and read by the program to control how frequently commits are issued. This way, you can change commit frequency without modifying the program code.

Conclusion

Lock escalation is a necessary mechanism in Db2, balancing the need for data integrity with resource efficiency. However, it can introduce performance issues if not properly managed. By understanding when and why escalation occurs, and taking proactive steps to optimize your environment, you can minimize its negative impact while maintaining a stable, efficient database system.

As with many aspects of Db2, the key lies in careful monitoring, tuning, and optimization. A well-managed lock escalation strategy ensures that your system remains responsive, even under heavy workloads, while preserving the data integrity that Db2 is known for.

Db2 for z/OS: The Performance and Management Champion!

Usually, posts I write for this blog focus on technical details, tips, and techniques for better using and optimizing your experience with Db2. Today, I want to do something a little different. You see, I am a big fan of Db2 for z/OS, and I do not see it getting the press, or the accolades that I think it is due. So I am going to use my platform to shout out the performance benefits of Db2 for z/OS.

When it comes to performance, nothing beats Db2 for z/OS. This mainframe database has been setting the standard for decades, delivering unmatched speed and efficiency for mission-critical applications. Let's explore some of the reasons why Db2 for z/OS is the performance champion.

Hardware Acceleration

• z/Architecture: Db2 for z/OS takes full advantage of the powerful z/Architecture, which includes specialized hardware for database operations. This hardware acceleration provides a significant performance boost for tasks like query processing and data loading.
• Storage Subsystem: The mainframe's storage subsystem is designed for high performance and reliability. With features like z/Hyperlink, data compression, and flash storage, Db2 for z/OS can access data quickly and efficiently.
• IDAA: IBM Db2 Analytics Accelerator is a high-performance, in-memory database appliance designed to accelerate analytic workloads. It's optimized for large-scale data analysis tasks, providing significant speedups compared to traditional disk-based databases. By leveraging solid-state drives (SSDs) and advanced hardware architecture, IDAA can handle complex queries and data manipulations with exceptional efficiency. This makes it ideal for applications requiring real-time analytics, data warehousing, and big data processing.

Database Optimization

• Query Optimization: Db2 for z/OS has a sophisticated query optimizer that can automatically select the most efficient execution plan for your queries. This ensures that your applications run as fast as possible.
• Data Compression: Db2 for z/OS supports data compression, which can reduce storage requirements and improve performance. By compressing data, Db2 can reduce the amount of data that needs to be read and processed.
• Parallel Processing: Db2 for z/OS can take advantage of multiple processors to perform tasks in parallel. This can significantly improve performance for large workloads.
• AI: IBM Db2 AI for z/OS integrates autonomics to simplify database management efforts. Using machine learning and AI, it can help improve operational performance and maintain Db2 for z/OS efficiency and health while enhancing Db2 for z/OS performance, reliability and cost effectiveness–even under the most demanding circumstances.

Workload Management

• Resource Allocation: Db2 for z/OS provides powerful tools for managing resources and ensuring that your database applications get the resources they need to perform optimally.
• Workload Balancing: Db2 can automatically balance workloads across multiple systems to ensure that resources are used efficiently.
• WLM: Workload Manager is an integrated, critical component of z/OS that is used for optimizing the performance and resource utilization of Db2 for z/OS. It provides a comprehensive framework for managing workloads across the mainframe environment, ensuring that Db2 applications receive the resources they need to perform optimally.

Data Sharing and Parallel Sysplex

Finally, Data Sharing using IBM Z Parallel Sysplex confers a significant advantage onto Db2 for z/OS in that it can enhanced availability by providing inherent redundancy, as multiple subsystems can access the same data. This helps to mitigate the impact of hardware failures or system outages. And in case of a disaster, data sharing can facilitate rapid recovery by allowing applications to access data from a different subsystem.

Furthermore, Data Sharing enhances scalability by enabling workloads to be distributed across multiple subsystems, improving scalability and preventing bottlenecks. It facilitates simpler growth: as data volumes and application demands increase, data sharing can help to accommodate growth without requiring significant hardware investments.

And Data Sharing can improve performance. By allowing multiple Db2 subsystems to access the same data without requiring individual copies, data sharing significantly reduces I/O operations, leading to improved performance. And with data readily available to multiple subsystems, queries can be executed more quickly, resulting in faster response times for applications.

So, IBM Z data sharing on Db2 offers a range of benefits, including improved performance, enhanced availability, increased scalability, reduced costs, and simplified management. These benefits make it a valuable feature for organizations that require high-performance, reliable, and scalable database solutions.

Real-World Results

Organizations around the world rely on Db2 for z/OS to power their most critical applications. From financial services to healthcare, Db2 has proven its ability to deliver the performance and reliability that businesses need to succeed.

So, if you're looking for a database that can handle your most demanding workloads and deliver exceptional performance, Db2 for z/OS is the way to go.

Highlights of the 2024 NA IDUG Db2 Tech Conference

Just a quick blog post today to let my readers know that I have written an overview of the 2024 IDUG Db2 Tech Conference in Chartlotte, NC this past June. 

The overview was written for the SHARE'd Intelligence blog, which is the official publication of SHARE. It offers news and education on enterprise solutions, and you would be wise to bookmark the site to keep up with the content shared there. 

The post I wrote is titled Riding the Waves of Knowledge at the IDUG Db2 Tech Conference. I hope you'll check it out, read my perspective, and share your thoughts on it here... and make plans to attend next year's IDUG event in Atlanta!

Coding Db2 Applications for Performance - Expert Videos Series

Today's blog post is to share with my readers that I have partnered with Interskill Learning and produced a series of videos in the Expert Video Series on how to code Db2 applications for performance.

My regular readers know that application performance is a passion of mine. You may also have read my recent book on the topic, A Guide to Db2 Performance for Application Developers. But if you are looking for videos to guide you through the process optimizing your application development for Db2, look no further than the six-part series I recorded for Interskill Learning, Coding Db2 Applications for Performance.

You do not need in-depth pre-existing knowledge of Db2 to gain insight from these video lessons. The outline of the six courses are as follows:

 Db2 Coding – Defining Database Performance

• Providing a Definition
• The Four Components
• Diving a Little Deeper

Db2 Coding – Coding Relationally

• What is Relational?
• Relational vs. Traditional Thinking
• What Does It Mean to Code Relationally?
• Unlearning Past Coding Practices

Db2 Coding – General SQL and Indexing Guidelines

• Types of SQL
• SQL Coding Best Practices
• Indexes and Performance
• Stages and Clustering

Db2 Coding – Coding for Concurrent Access

• Introduction to Concurrency
• Locking
• Locking Duration and Binding
• Locking Issues and Strategies
• Query Parallelism

Db2 Coding – Understanding and Reviewing Db2 Access Paths

• Single Table Access Paths
• Multi-table Access Paths
• Filter Factors
• Access Paths and EXPLAIN

Db2 Coding – SQL Coding Tips and Techniques

• Avoid Writing Code
• Reusable Db2 Code
• Dynamic and Static SQL
• SQL Guidelines
• Set Operations

So if you are looking for an introduction to Db2 performance or want to brush up on the fundamentals of coding for performance, look no further. Check out this series of videos on Coding Db2 Applications for Performance from Interskill Learning (featuring yours truly)!

Note that Interskill Learning also offers other categories of training in their Expert Video series including systems programming, quantum computing, and pervasive encryption. 

The Basics of Coding Db2 SQL for Performance

When it comes to assuring optimal performance of Db2 applications, coding properly formulated SQL is an imperative. Most experts agree that poorly coded SQL and application code is the cause of most performance problems – perhaps as high as 80% of poor relational performance is caused by “bad” SQL and application code.

But writing efficient SQL statements can be a tricky proposition. This is especially so for programmers and developers new to a relational database environment. So, before we delve into the specifics of coding SQL for performance, it is best that we take a few moments to review SQL basics.

SQL, an acronym for Structured Query Language, is a powerful tool for manipulating data. It is the de facto standard query language for relational database management systems and is used not just by Db2, but also by the other leading RDBMS products such as Oracle, Sybase, and Microsoft SQL Server.

SQL is a high-level language that provides a greater degree of abstraction than do procedural languages. Most programming languages require that the programmer navigate data structures. This means that program logic needs to be coded to proceed record-by-record through data elements in an order determined by the application programmer or systems analyst. This information is encoded in the program logic and is difficult to change after it has been programmed.

SQL, on the other hand, is fashioned so that the programmer can specify what data is needed, and not how to retrieve it. SQL is coded without embedded data-navigational instructions. Db2 analyzes the SQL and formulates data-navigational instructions "behind the scenes." These data-navigational instructions are called access paths. By having the DBMS determine the optimal access path to the data, a heavy burden is removed from the programmer. In addition, the database can have a better understanding of the state of the data it stores, and thereby can produce a more efficient and dynamic access path to the data. The result is that SQL, used properly, can provide for quicker application development.

Another feature of SQL is that it is not merely a query language. The same language used to query data is used also to define data structures, control access to the data, and insert, modify, and delete occurrences of the data. This consolidation of functions into a single language eases communication between different types of users. DBAs, systems programmers, application programmers, systems analysts, and end users all speak a common language: SQL. When all the participants in a project are speaking the same language, a synergy is created that can reduce overall system-development time.

Arguably, though, the single most important feature of SQL that has solidified its success is its capability to retrieve data easily using English-like syntax. It is much easier to understand the following than it is to understand pages and pages of program source code.

    SELECT  LASTNAME

    FROM    EMP

    WHERE   EMPNO = '000010';

Think about it; when accessing data from a file the programmer would have to code instructions to open the file, start a loop, read a record, check to see if the EMPNO field equals the proper value, check for end of file, go back to the beginning of the loop, and so on.

SQL is, by nature, quite flexible. It uses a free-form structure that gives the user the ability to develop SQL statements in a way best suited to the given user. Each SQL request is parsed by the DBMS before execution to check for proper syntax and to optimize the request. Therefore, SQL statements do not need to start in any given column and can be strung together on one line or broken apart on several lines. For example, the following SQL statement is equivalent to the previously listed SQL statement:

    SELECT LASTNAME FROM EMP WHERE EMPNO = '000010';

Another flexible feature of SQL is that a single request can be formulated in a number of different and functionally equivalent ways. One example of this SQL capability is that it can join tables or nest queries. A nested query always can be converted to an equivalent join. Other examples of this flexibility can be seen in the vast array of functions and predicates. Examples of features with equivalent functionality are:

·       BETWEEN versus <= / >=

·       IN versus a series of predicates tied together with AND

·       INNER JOIN versus tables strung together in the FROM clause separated by commas

·       OUTER JOIN versus a simple SELECT, with a UNION, and a correlated subselect

·       CASE expressions versus UNION ALL statements

This flexibility exhibited by SQL is not always desirable as different but equivalent SQL formulations can result in extremely differing performance. The ramifications of this flexibility are discussed later in this paper with guidelines for developing efficient SQL.

As mentioned, SQL specifies what data to retrieve or manipulate, but does not specify how you accomplish these tasks. This keeps SQL intrinsically simple. If you can remember the set-at-a-time orientation of a relational database, you will begin to grasp the essence and nature of SQL. A single SQL statement can act upon multiple rows. The capability to act on a set of data coupled with the lack of need for establishing how to retrieve and manipulate data defines SQL as a non-procedural language.

Because SQL is a non-procedural language a single statement can take the place of a series of procedures. Again, this is possible because SQL uses set-level processing and DB2 optimizes the query to determine the data-navigation logic. Sometimes one or two SQL statements can accomplish tasks that otherwise would require entire procedural programs to do.

High-Level SQL Coding Guidelines

When you are writing your SQL statements to access Db2 data be sure to follow the subsequent guidelines for coding SQL for performance. These are certain very simple, yet important rules to follow when writing your SQL statements. Of course, SQL performance is a complex topic and to understand every nuance of how SQL performs can take a lifetime. That said, adhering to the following simple rules puts you on the right track to achieving high-performing Db2 applications.

1)     The first rule is to always provide only the exact columns that you need to retrieve in the SELECT-list of each SQL SELECT statement. Another way of stating this is “do not use SELECT *”. The shorthand SELECT * means retrieve all columns from the table(s) being accessed. This is fine for quick and dirty queries but is bad practice for inclusion in application programs because:

·       Db2 tables may need to be changed in the future to include additional columns. SELECT * will retrieve those new columns, too, and your program may not be capable of handling the additional data without requiring time-consuming changes.

·       Db2 will consume additional resources for every column that requested to be returned. If the program does not need the data, it should not ask for it. Even if the program needs every column, it is better to explicitly ask for each column by name in the SQL statement for clarity and to avoid the previous pitfall.

2)     Do not ask for what you already know. This may sound simplistic, but most programmers violate this rule at one time or another. For a typical example, consider what is wrong with the following SQL statement:

    SELECT  EMPNO, LASTNAME, SALARY

    FROM    EMP

    WHERE   EMPNO = '000010';

 

Give up? The problem is that EMPNO is included in the SELECT-list. You already know that EMPNO will be equal to the value '000010' because that is what the WHERE clause tells DB2 to do. But with EMPNO listed in the WHERE clause Db2 will dutifully retrieve that column too. This causes additional overhead to be incurred thereby degrading performance.

3)     Use the WHERE clause to filter data in the SQL instead of bringing it all into your program to filter. This too is a common rookie mistake. It is much better for Db2 to filter the data before returning it to your program. This is so because Db2 uses additional I/O and CPU resources to obtain each row of data. The fewer rows passed to your program, the more efficient your SQL will be. So, the following SQL

    SELECT  EMPNO, LASTNAME, SALARY

    FROM    EMP

    WHERE   SALARY > 50000.00;

Is better than simply reading all of the data without the WHERE clause and then checking each row to see if the SALARY is greater than 50000.00 in your program.

These rules, though, are not the be-all, end-all of SQL performance tuning – not by a long shot. Additional, in-depth tuning may be required. But following the above rules will ensure that you are not making “rookie” mistakes that can kill application performance. 

In Closing

This short blog post is just the very beginning of SQL performance for Db2 programmers. Indeed, I wrote a book on the topic called A Guide to Db2 Performance for Application Developers, so check that out if this post has whetted your appetite for more Db2 performance tips... and if you are a more visual learner, I have also partnered with Interskill Learning for a series of videos in their Expert Video series on the topic of Coding Db2 Applications for Performance. So, why wait, dig in to a book, some videos, or both, to help improve the performance of your Db2 applications!

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.

Who's Going to Charlotte for IDUG 2024?

Spring is in the air and as a Db2 professional you know that can mean only one thing: the North American IDUG Db2 Tech Conference will soon be upon us! 

This year IDUG is back in Charlotte (like it was back in 2019). Ahhh, the pre-CovID days! Nevertheless, I am looking forward to an exciting week of learning more about my favorite DBMS and mingling with others Db2 users and fans!

This year, the event runs from Sunday June 23rd thru Thursday June 27th and it will be held at Le Meridien Hotel. But what can you expect if you attend? Well, there will be a tremendous number of educational opportunities at the IDUG Db2 Tech Conference. IBMers, vendor experts, users, IBM Champions, and IBM Gold consultants from all over the world converge in one location and share their experiences. So you know there will be numerous informative technical sessions on all the latest and greatest Db2 technologies, features, and related products. And you can view the grid with all of this year's presentations here.

Additionally, you can earn Db2 for z/OS and Db2 for LUW certification and badges at no additional cost. Every attendee can take one exam at no addtional cost! Not to mention the Expo Hall with all your favorite Db2 product vendors, pre- and post- conference workshops, Special Interest Groups, expert panels, evening receptions, and fun evening events sponsored by Broadcom and IBM.

If you’ve ever attended an IDUG conference in the past then you know why I’m excited. If you haven't attended before, I'm sure you'll find a lot to occupy your interest!

And be sure to seek me out and say "Howdy!" I'll be giving two presentations this year.

First up, on Monday June 24th I'll be delivering a presentation based on some of the things that I've encountered as an independent consultant. Titled Oh, The Things I've Seen: Db2 Stories and Best Practices, I'll talk about things like RUNSTATS, RID failures, and more in a Dr. Seuss-like way. You won't want to miss that, will you? 

Later I will be co-presenting a vendor-sponsored presentation with InfoTel on the topic of Data Governance for DBAs. In it, we will address the difference between governance and administration, as well as some of the biggest issues facing data governance these days. And, of course, how these things impact DBAs. InfoTel will also show how their technology can aid the process!

I will also be spending some time in the InfoTel booth so you can always stop by there to say hello or ask me a question!

So I hope to see you in Charlotte for a great week of education and camaraderie at this year's premier event for Db2 professionals, the IDUG Db2 Tech Conference. 

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)?