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.