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

What Type of Changes Cause Db2 Packages to Get Invalidated?

Db2 DBAs are constantly working with database objects such as Databases, Tablespaces, Tables, and Indexes. And many requirements cause DBAs to have to modify these objects. Some modifications may be simple, such as just issuing an ALTER statement. Others may be more in-depth, even to the point of having to DROP and re-CREATE the object.

Now I've blogged here before about the many different types of Db2 database changes and how to manage them. For reference, here is a blog post that summarizes the posts I've made on this topic.

My purpose today is not to rehash all of that information again, but to discuss one aspect of change management that probably causes DBAs the most grief: package invalidation. 

Packages can be invalidated by Db2 for many reasons. For example, when a privilege that is required by a package is revoked, the package is invalidated by Db2. 

When a package is invalidated it cannot be executed until it has been rebound. This can be automatic, but it is usually better to be proactive and to Rebind packages when you take an action that invalidates packages. 

And as we all know, rebinding can cause access paths to change. Hopefully for the better... but not always. If access paths always got better then there would be no DBA grief, right? Whenever DBAs perform Rebinds they are always dreading that call from the developer or end-user that says "Hey, this transaction (or job) that used to run quickly is now taking forever."

So it makes sense that DBAs need to be aware of what changes cause packages to be invalidated. Of course, if you have to DROP an object that the package accessed it is obvious that a Rebind is required. But there are many other types of changes that will invalidate packages.

Fortunately, the IBM Db2 documentation is good and easy to find. Here is a link to the Db2 12 for z/OS documentation for Changes that invalidate packages. If you are a DBA, I recommend that you click on that link and bookmark that page!

I'm not going to copy and paste all of the information from the manual here (no reason to and over time it could change). But here are some of the things to keep in mind that you may not at first think will affect packages, but can:

• Altering, dropping, or renaming a column
• Adding date/time columns with defaults
• Adding a constraint
• Adding, changing, or rotating partitions in a partitioned or partition-by range UTS tablespace
• Temporal and transparent archiving changes
• Adding, altering, or dropping a materialized query table (MQT) 
• Activating or deactivating row-level or column-level access control
• Enabling or disabling masks if column access control is in effect
• Increasing a table space's MAXPARTITIONS attribute
• Changing a table space's SEGSIZE or DSSIZE
• Changing the buffer pool for a tablespace (with a different page size)
• Altering indexes to add a column, change the PADDED attribute, or changing the limit key value of a partitioning index
• Regenerating an index
• Running the REORG utility with the REBALANCE keyword
• Running the REPAIR utility on a database with the DBD REBUILD option

Again, these are just some of the admin changes that can invalidate packages. There are others and you should always refer to the current Db2 documentation for the list of things that will invalidate packages before you make any changes. Failing to do so might mean that you will have to run a mass Rebind... maybe at a time when you'd rather not!

Finally, I'll leave you with a couple of helpful queries you can run to help as you manage changes.

To identify all packages that will be invalidated by a change to a specific object, run the following query:

SELECT   DISTINCT DCOLLID, DNAME, DTYPE 
FROM     SYSIBM.SYSPACKDEP
WHERE    BQUALIFIER = ?
AND      BNAME = ?
AND      BTYPE = ?
ORDER BY DCOLLID, DNAME;

Simply plug in the qualifier and name of the object, along with type of the object (appropriate values can be found in the Catalog table documentation in the appendix of the IBM Db2 SQL Reference manual).

And if you want to identify all invalid packages, try running this query:

SELECT   COLLID, NAME, VALID
FROM     SYSIBM.SYSPACKAGES
WHERE    VALID <> 'Y'
ORDER BY COLLID, NAME;

Db2 for z/OS and Managing Database Changes - The Recap

During the month of April 2020 I wrote a series of blog posts on the different types of Db2 for z/OS database change management and the things to remember and consider...  Today, the first day of May, I just wanted to publish a quick recap and links to all of these posts. So without further ado...

The first post in this series introduced the types of changes and briefly explained the differences at a very high level. It serves as the introduction to the next three parts.

Part 2 examined simple changes, the easiest of the three types of change to implement. These usually just require issuing a simple ALTER to effect database changes.

In the next installment, Part 3 details medium changes, known in the Db2 world as pending changes. Introduced in Db2 10 for z/OS, these require a little bit more work and can only be performed on database objects in Universal table spaces.

And then in the final post, Part 4 takes a look at complex changes. These are the types of changes to database structures that are only supported by dropping and then re-creating the database structure with your required changes. 

If this quick recap whetted your appetite for more details, please take a moment or two to click through each of the links and read the more detailed posts.

And good luck managing your Db2 for z/OS changes!

Db2 for z/OS and Managing Database Changes - Part 4

Today brings the fourth, and final installment of our series examining the different types of changes that can be made to database objects and structures in Db2 for z/OS. Part 1 introduced the three types of changes, part 2 examined simple database changes, and part 3 took a look at medium, or pending changes. 

And that brings us to the final type of Db2 schema change, the complex change. A complex change is essentially one that is unsupported by Db2 other than by dropping and then re-creating the database structure with the desired change. Of course, implementing such changes is not as easy as just dropping and re-creating the object.  For example, if you want to add a column to the middle of an existing row, it cannot be done using ALTER, and such, it is a complex change. Of course, this is not the only type of complex change. Any change that is not simple (immediate) or medium (pending) is a complex change and it requires an in-depth series of tasks that will differ based on the database object being changed and the specific change to implement.

An example of the type of activities that may need to be scripted to implement a complex database change include:

• Retrieve the current definition of the database object by querying the appropriate Db2 Catalog tables, which will be different for each type of object.
• Retrieve the current definition of any dependent objects as well; for example, if you drop a table, then triggers, views, and indexes are also dropped.
• Capture all referential constraints for all tables involved in the change (either directly or indirectly).
• Retrieve all security authorizations that have been granted for all database objects that will be dropped either directly or as a result of cascading drops.
• Obtain a list of all programs that access impacted tables by using the Db2 Catalog, Db2 Directory, and any other program documentation at your disposal.
• Unload the data from all tables that will be impacted.
• Drop the database object to be changed, which in turn drops any dependent objects, revokes authorizations, and invalidates any SQL statements against any impacted tables in any application programs.
• Recreate the database object with the new specifications by using the definition obtained from the Db2 Catalog earlier.
• Reload the tables, using the unloaded data obtained earlier.
• Recreate any referential constraints that may have been dropped.
• Recreate any triggers, views, and indexes for the table.
• Recreate the security authorizations captured earlier.
• Examine each application program to determine whether changes are required for it to continue functioning appropriately.
• Test thoroughly.

The above list is not meant to be an exhaustive list of everything that must be accomplished for every type of complex schema change that you might have to implement. Instead, the list is intended to convey the intricacies involved in making complex changes and how automation can minimize risk and speed up the process!

Furthermore, it should be clear that complex changes will require an outage to complete. When database objects are dropped applications will no longer be able to access them until the changes are complete and if tables are involved, not until the data has been reloaded.

A well-designed and implemented database schema change solution must be able to understand and implement all of the types of changes covered in this section, and to implement them appropriately. That means that the tool should implement a medium, pending change when possible instead of simply deferring to a complex change. It also means being able to assemble a script of all the appropriate actions required for any type of complex change that the DBA may need to perform.

To work in a modern environment, the tool should also understand DevOps and agile development and integrate into any DevOps pipeline/toolchain seamlessly. 

Obviously, such capabilities require built-in intelligence and knowledge of Db2 for z/OS and its many nuances and features.

Db2 for z/OS and Managing Database Changes - Part 3

Welcome to the third installment of our series examining the types of database changes that can be performed using Db2 for z/OS. In part 1 we introduced the three types of changes and in part 2 we looked at simple changes. Today we will talk about the next type of change to consider, the medium or pending change.

A pending change requires a little more work than does a simple change, but is much easier to implement than a complex change. The pending change was introduced in DB2 10 and significantly simplifies some types of database change.

Pending changes are supported only for database objects in Universal table spaces. If a change must be made to a structure in a segmented or classic partitioned table space, you cannot use the pending change capability. Pending changes are made in a non-disruptive way using the ALTER statement to make the desired change, but requiring a REORG to drive the actual, underlying change to the database structures. Because a reorganization can be run online, pending changes can be implemented with little, to no downtime on the system. And changes are easier to back off; simply issue the DROP PENDING CHANGES command (as long as no REORG has been run).

With pending changes, Db2 semantically validates the request and checks authorization at execution time as usual, but the change is not actually implemented. It is simply registered in the Db2 Catalog in a table named SYSIBM.SYSPENDINGDDL. When the change is requested, the object goes into an advisory state, AREOR, and the ALTER statement returns an SQLCODE of +610 indicating that the object has been placed into a pending state, but it remains completely available to your applications.

So, as you make deferred ALTER changes Db2 will begin to populate the changes into the SYSIBM.SYSPENDINGDDL table. Each pending change will have a row in the table. Depending upon what you have changed, a single ALTER can produce multiple rows in the SYSPENDINGDDL table.

Your changes are recorded in SYSPENDINGDDL rows as they are made and then applied in that order. For example, you can convert a segmented table space to Universal PBG. And then modify the DSSIZE. These changes are recorded by DB2 in that order and allowed.

It is possible, too, to make multiple changes to the same parameter and have them build up in the pending table. Say that you change the buffer pool for a table space from BP0 to BP32K. And then later change the same table space to BP8K2 before you run a REORG. In this case, you will end up with the TS in the BP8K2 buffer pool and 8K page sizes. DB2 knows and maintains the order of your changes and will get it right when you implement the deferred changes using REORG.

The actual, underlying changes are only made by Db2 when you run the REORG utility using SHRLEVEL CHANGE or REFERENCE. Another way of thinking about this is that only when a Shadow object is being used will Db2 implement pending changes.  Of course, you can still run a REORG using SHRLEVEL NONE but none of your pending changes will be implemented (that is, the changes will still be pending and the Pending Status will not be reset). The REORG can be executed at either the table space or index level… keeping in mind that dependent index changes will be implemented by reorganizing the table space containing the table that the index is built on.

Db2 does not permit combining deferred and immediate ALTERs in a single SQL statement, so be careful about what you are trying to request. Additionally, most immediate ALTERs are not possible while changes are pending.

It is a good idea, though not a requirement, to avoid confusion by materializing pending changes as soon as possible. When you have an Advisory Reorg Pending (AREO*) status clean it up with a REORG as quickly as makes sense. And make sure that you do so before making new changes whenever possible. With multiple changes out there pending to be made, it can be confusing and you may have forgotten all that was requested before. Additionally, there can be performance degradation if you do not clean up that Advisory Reorg Pending (AREO*) status.

Examples of medium changes that can be implemented as pending include converting a segmented table space to a Universal partition-by-growth table space, converting a classic partitioned table space to a Universal partition-by-range table space[7], converting a Universal partition-by-growth table space to RPN[8], changing the DSSIZE[9] of a table space[10], SEGSIZE[11], increasing MAXPARTITIONS, changing MEMBER CLUSTER, dropping a column from a table[12], renaming a column[13], modifying partitioning and rotating partitions, and regenerating an index.

Additionally, as of Db2 12, there is a new capability to set a system parameter that will treat all ALTER COLUMN changes as pending, even though you can change the data type, length, precision, and scale as immediate changes.

Remember that all changes implemented as pending using deferred ALTER require Universal table spaces. For any other type of table space, they are treated as complex changes.

-----------------------------------
[7] The classic partitioned table space must be table-controlled, not index-controlled

[8] Using the PAGENUM RELATIVE parameter

[9] Although the change can be simple/immediate if the data sets have not yet been created and no pending changes have been requested.

[10] Although the change can be simple/immediate if the data sets have not yet been created, no pending changes have been requested or the specified buffer pool is the same size as the current buffer pool.

[11] There are conditions where this can be an immediate, simple change

[12] Some columns drops are not allowed without other changes or require a complex script to implement

[13] Renaming a column becomes a complex change if the column is referenced in a view, index, row permission, column mask, UDG, check constraint or FIELDPROC. The change is also complex if the table containing the column is or is referenced by an MQT, has a trigger, has a VALIDPROC or and EDITPROC with row attributes.

Db2 for z/OS and Managing Database Changes - Part 2

In part 1 of our multi-part series on Db2 for z/OS database change management, we provided an overview of the three types of database change that can be undertaken. In today's post, we are going to examine the first type of change -- the simple database change -- in a little more depth.

Simple database changes are the easiest to implement. A simple database change, typically implemented using the ALTER statement, can be executed immediately upon request. The change is made immediately but may require additional actions to fully implement. For example, if you add a nullable column at the end of a table using ALTER TABLE ADD COLUMN the change is made immediately. For all intents and purposes, the addition is complete. However, under the covers, Db2 has not expanded the storage for each row to include space for the column. This happens as the column is accessed and used, or when the table space is reorganized. Applications can use and access the new column without knowing this, however, so the change is immediate; housekeeping to implement the change entirely may occur over time.

Additional examples of simple, immediate changes are most CREATE and DROP statements; altering STOGROUPs; altering most default parameters for databases, table spaces, indexes, and STOGROUPs; renaming tables (packages are invalidated but privileges and indexes are maintained)[1]; renaming indexes; adding a column at the end of a table[2]; changing the data type[3], precision scale of length of a column[4]; identity column parameters, adding and dropping versioning to a temporal table, adding and dropping constraints[5]; activating and deactivating row access control; adding, dropping, and exchanging clone tables; altering, dropping, and refreshing materialized query tables[6]; creating, dropping, and renaming global temporary tables; altering most aspects of user-defined functions and stored procedures; and changing or dropping labels on tables, aliases, and columns. 

Additionally, the "new", Db2 12 TRANSFER OWNERSHIP command is implemented as a simple, immediate change.

---

[1] Not all types of tables can be renamed. Consult the IBM Db2 SQL Reference manual, page 2163, for types of tables and options that forbid renaming a table.

[2] Adding a column at the end of a table requires that the column be nullable or have a default assigned, otherwise it is a complex change

[3] Can change data type within data type families (text to text, number to number, etc.)

[4] Can change length as an immediate change as long as it is larger, otherwise it is a complex change.

[5] With the caveat that the CHECK utility will have to be run to enforce a check constraint if the CURRENT RULES ‘DB2’ option is in effect

[6] When a materialized query table is dropped, all packages dependent on it are invalidated

Db2 for z/OS and Managing Database Changes - Part 1

Today we begin a multi-part series of blog posts taking a look at what is involved in making database changes in a Db2 for z/OS environment. The first thing that DBAs will need is the ability to change all the database objects supported by Db2 for z/OS. There are numerous different types of  database objects and structures that can be created and modified by DDL, and at one point or another, DBAs are called upon to create, alter, and drop every one of them.

But let’s dig a little deeper into what is required. Assume that you are a Db2 DBA who has been given a request to make several changes to database structures. The first thing you must do, of course, is to review the requested changes to make sure they are appropriate. Assuming they are, what is the next step?

You must determine how to go about making each change. At a high level, there are three different types of schema changes: 

• simple (or immediate), 
• medium (or pending), and 
• complex. 

Simple changes can be implemented immediately without requiring intervening actions. Medium changes require a bit more work to implement by running a REORG, and then we have complex changes that require an in-depth script for dropping and re-creating the database object. But not every type of database change request can use each type of schema change method. There are requirements and nuances in deciding which method can be used when.

In our next blog post, we will discuss simple Db2 changes.

BMC AMI for DevOps Intelligently Integrates Db2 for z/OS Schema Changes

Organizations of all types and sizes have adopted a DevOps approach to building applications because it effectively implements small and frequent code changes using agile development techniques. This approach can significantly improve the time to value for application development. The DevOps approach is quite mature on distributed platforms, but it is also gaining traction on the mainframe.

As mainframe development teams begin to rely on DevOps practices more extensively, the need arises to incorporate Db2 for z/OS database changes. This capacity has been lacking until recently, requiring manual intervention by the DBA team to analyze and approve schema changes. This, of course, slows things down, the exact opposite of the desired impact of DevOps. But now BMC has introduced a new solution that brings automated Db2 schema changes to DevOps, namely BMC AMI for DevOps.

BMC AMI for DevOps is designed to integrate into the DevOps tooling that your developers are already using. It integrates with the Jenkins Pipeline tool suite to provide an automated method of receiving, analyzing, and implementing Db2 schema changes as part of an application update.

By integrating with your application orchestration tools AMI for DevOps can capture the necessary database changes required to move from test to production. But it does not just apply these changes; it enforces and ensures best practices using built-in intelligence and automated communication between development and database administration.

The ability to enforce best practices is driven by BMC’s Automated Mainframe Intelligence (AMI), which is policy driven. The AMI capability builds much of the DBA oversight for schema changes into the DevOps pipeline, enforcing database design best practices as you go instead of requiring in-depth manual DBA oversight.

Incorporating a database design advisory capability into the process offloads manual, error-prone tasks to the computer. This integrated automation enables automatic evaluation of Db2 database schema change requests to streamline the DBA approval process and remove the manual processes that inhibit continuous delivery of application functionality.

Furthermore, consider that intelligent database administration functionality can be used to help alleviate the loss of expertise resulting from an aging, retiring workforce. This is a significant challenge for many organizations in the mainframe world.

But let’s not forget the developers. The goal of adopting a DevOps approach on the mainframe is to speed up application development, but at the same time it is important that we do not forgo the safeguards built into mainframe development and operations. So you need a streamlined DevOps process—powered by intelligent automation—in which application developers do not have to wait around for DBA reviews and responses. A self-service model with built-in communication and intelligence such as provided by AMI for DevOps delivers this capability.

The Bottom Line

BMC AMI for DevOps helps you to bring DevOps to the mainframe by integrating Db2 for z/OS schema changes into established and existing DevOps orchestration processes. This means you can use BMC AMI for DevOps to deliver the speed of development required by agile techniques used for modern application delivery without abandoning the safeguards required by DBAs to assure the accuracy of the database changes for assuring availability and reliability of the production system. And developers gain more self-service capability for Db2 schema changes using a well-defined pipeline process.

DevOps is Coming to Db2 for z/OS

Mainframe development teams are relying on DevOps practices more extensively, bringing the need to incorporate Db2 for z/OS database changes into the toolset that is supporting their software development lifecycle (SDLC).

But most mainframe professionals have only heard a little about DevOps and are not really savvy as to what it entails. DevOps is an amalgamation of Development and Operations. The goal of DevOps is to increase collaboration between developers and operational support and management professionals, with the desired outcome of faster, more accurate software delivery.

DevOps typically relies on agile development, coupled with a collaborative approach between development and operations personnel during all stages of the application development lifecycle. The DevOps approach results in small and frequent code changes and it can significantly reduce the lead time for changes, lower the rate of failure, and reduce the mean time to recovery when errors are encountered. These are all desirable qualities, especially as organizations are embracing digital transformation driven by the 24/7 expectations of users and customers to access data and apps at any time from any device.

The need to be able to survive and thrive in the new digital economy has caused organizations to adopt new and faster methods of developing, testing and delivering application software. Moving from a waterfall software development methodology to an agile methodology is one way that organizations are speeding the time-to-delivery of their software development. Incorporating a DevOps approach is another.

Instead of long software development projects that may not deliver value for months, or perhaps even years (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. Furthermore, it requires solid, integrated automated tooling to facilitate the SDLC from development, through testing, to delivery. Creating such an environment and culture can be challenging.

With DevOps the result will be a constantly repeating cycle of continuous development, continuous integration and continuous deployment. This is typically depicted graphically as the infinity symbol such as in Figure 1 (below).

Figure 1 - continuous development, integration and deployment

Note, however, that this particular iteration of the DevOps infinity graphic calls out the participation of both the application and the database. This is an important, though often lacking, detail that should be stressed when adopting DevOps practices.

The Mainframe and DevOps

The adoption of DevOps has, until now, been much slower within mainframe development teams than for distributed and cloud application development. The staid nature of mainframe development and support, coupled with a glass house mentality, and a rigid production turnover process contribute to the delayed adoption of DevOps on the mainframe. This is not surprising as mainframes mostly are used by large organizations running mission critical workloads with an aversion to any kind of change and risk-averse.

Additionally, the traditional waterfall development methodology has been used by most mainframe software developers for multiple decades, whereas DevOps is closely aligned with an agile approach, which differs significantly from waterfall.

Notwithstanding all of these barriers to acceptance of DevOps on the mainframe, mainframe developers can, and in some cases already do successfully utilize a DevOps approach. Technically speaking, the mainframe is just another platform and there is nothing inherent in its design or usage that obviates the ability to participate in a DevOps approach to application development and delivery.

What about Db2 for z/OS?

Integrating database change into the application delivery lifecycle can be a stumbling block on the road to DevOps success. Development teams focus on application code, as they should, and typically view database structure changes as ancillary to their coding efforts. In most application development projects, it is not the programmer’s responsibility to administer the database and modify database structures. But applications rely on the database being designed, implemented, and changed in accordance with the needs of the business and the code.

This means that many development projects have automated their SDLC tool chain to speed up the delivery of applications. This is the “Dev” portion of DevOps. But the requisite automation and tooling has not been as pervasively implemented to speed up the delivery of database changes. This is the “Ops” portion of DevOps. And this is changing.

A big consideration is that the manner in which change is applied to applications differs from how database changes are applied. That means each must be managed using different techniques and probably different tools. When an application program changes, the code is compiled, and the load module is migrated from test to production. The old load module is saved for posterity in case the change needs to be backed out, but the change is a wholesale replacement of the executable code.

Database changes are different. The database is an entire configuration in each environment and changes get migrated. There is no wholesale replacement of the database structures. DDL commands are issued to ALTER, DROP, and CREATE the changes to the database structures as needed.

From the perspective of database changes on Db2 for z/OS, DBAs need the ability to modify all the database objects supported by Db2 for z/OS. Supporting Db2 for z/OS using DevOps requires tooling that understands both Db2 for z/OS and the DevOps methodology and toolchain. And the tooling must understand how changes are made, as well as any underlying changes that may be required to effectively implement the database change. Some types of database changes are intrusive, requiring a complicated series of unloads, metadata captures, drops, creates, loads, and additional steps to implement. The tooling must be capable of making any of these changes in an automated way that the DBA trusts.

Fortunately, for organizations adopting DevOps on the mainframe with Db2, there is a solution for integrating Db2 database change into the DevOps toolchain: BMC AMI DevOps for Db2. BMC AMI DevOps for Db2 integrates with Jenkins, an application development orchestration tool, to automatically research and determine database schema change requirements, to streamline the review and approval process, and to safely implement the database schema changes making development and operations teams more efficient and agile.  

Webinar: DevOps and Database Change Management for Db2 for z/OS - August 13, 2019

DevOps practices are gaining popularity on all development platforms and the mainframe is no exception. DevOps relies heavily on agile development and automated software delivery. However, the ability to integrate and orchestrate database changes has lagged. To learn more about DevOps, change management, and Db2 for z/OS, I am delivering a webinar on this topic along with John Barry of BMC. We will discusses issues including an overview of DevOps, the requirements for database change management, and an introduction to BMC’s new AMI DevOps for Db2 that solves the change management dilemma for Db2 for z/OS development. You can register today to attend the webinar on August 13, 2019 (Noon Central) at https://event.webcasts.com/starthere.jsp?ei=1251892&tp_key=3ff9b7af72.

Types of DB2 Tools

As a user of DB2, which I'm guessing you are since you are reading this blog, you should always be on the lookout for useful tools that will help you achieve business value from your investment in DB2. There are several categories of tools that can help you to achieve this value.

Database Administration and Change Management tools simplify and automate tasks such as creating database objects, examining existing structures, loading and unloading data, and making changes to databases. Without an administration tool these tasks require intricate, complex scripts to be developed and run. One of the most important administration tools is the database change manager. Without a robust, time-tested product that is designed to effect database changes, database changes can be quite time-consuming and error prone. A database change manager automates the creation and execution of scripts designed to implement required changes – and will ensure that data integrity is not lost.

One of the more important categories of DB2 tools offers Performance Management capabilities. Performance tools help to gauge the responsiveness and efficiency of SQL queries, database structures, and system parameters. Performance management tools should be able to examine and improve each of the three components of a database application: the DB2 subsystem, the database structures, and the application programs. Advanced performance tools can take proactive measures to correct problems as they happen.

Backup and Recovery tools simplify the process of creating backups and recovering from those backup copies. By automating complex processes, simulating recovery, and implementing disaster recovery procedures these tools can be used to assure business resiliency, with no data being lost when the inevitable problems arise.

Another important category of DB2 tool is Utilities and Utility Management. A utility is a single purpose tool for moving and/or verifying database pages; examples include LOAD, UNLOAD, REORG, CHECK, COPY, and RECOVER. Tools that implement and optimize utility processing, as well as those that automate and standardize the execution of DB2 utilities, can greatly improve the availability of your DB2 applications. You might also want to consider augmenting your utilities with a database archiving solution that moves data back and forth between your database and offline storage.

Governance and Compliance tools deliver the ability to protect your data and to assure compliance with industry and governmental regulations, such as HIPAA, Sarbanes-Oxley, and PCI DSS. In many cases business executives have to vouch for the accuracy of their company’s data and that the proper controls are in place to comply with required regulations. Governance and compliance tools can answer questions like “who did what to which data when?” that are nearly impossible to otherwise answer.

And finally, Application Management tools help developers improve application performance and speed time-to-market. Such tools can improve database and program design, facilitate application testing including the creation and management of test data, and streamline application data management efforts.

Tools from each of these categories can go a long way toward helping your organization excel at managing and accessing data in your DB2 databases and applications...

The Ever-Changing Role of the DBA

Defining the job of DBA is getting to be increasingly difficult. Oh, most people know the rudimentary aspects of the job, namely keeping your organization's databases and applications running up to par. The DBA has to be the resident DBMS expert (whether that is DB2, Oracle or SQL Server, or most likely a combination of those). He or she has to be able to solve thorny performance problems, ensure backups are taken, recover and restore data when problems occur, make operational changes to database structures and, really, be able to tackle any issue that arises that is data-related.

The technical duties of the DBA are numerous. These duties span the realm of IT disciplines from database design to physical implementation and consistent, on-going monitoring of the database environment.

DBAs must possess the abilities to create, interpret, and communicate a logical data model and to create an efficient physical database design from a logical data model and application specifications. There are many subtle nuances involved that make these tasks more difficult than they sound. And this is only the very beginning. DBAs also need to be able to collect, store, manage, and query data about the data (metadata) in the database and disseminate it to developers that need the information to create effective application systems. This may involve repository management and administration duties, too.

After a physical database has been created from the data model, the DBA must be able to manage that database once it has been implemented. One major aspect of this management involves performance management. A proactive database monitoring approach is essential to ensure efficient database access. The DBA must be able to utilize the monitoring environment, interpret its statistics, and make changes to data structures, SQL, application logic, and the DBMS subsystem to optimize performance. And systems are not static, they can change quite dramatically over time. So the DBA must be able to predict growth based on application and data usage patterns and implement the necessary database changes to accommodate the growth.

And performance management is not just managing the DBMS and the system. The DBA must understand SQL used to access relational databases. Furthermore, the DBA must be able to review SQL and host language programs and to recommend changes for optimization. As databases are implemented with triggers, stored procedures, and user-defined functions, the DBA must be able to design, debug, implement, and maintain these code-based database objects as well.

Additionally, data in the database must be protected from hardware, software, system, and human failures. The ability to implement an appropriate database backup and recovery strategy based on data volatility and application availability requirements is required of DBAs. Backup and recovery is only a portion of the data protection story, though. DBAs must be able to design a database so that only accurate and appropriate data is entered and maintained - this involves creating and managing database constraints in the form of check constraints, rules, triggers, unique constraints, and referential integrity.

DBAs also are required to implement rigorous security schemes for production and test databases to ensure that only authorized users have access to data. As industry and governmental regulations multiply, the need to audit who did what to which data when is also a requirement for sensitive data in production systems – and the DBA must be involved in ensuring data auditability without impacting availability or performance.

And there is more! The DBA must possess knowledge of the rules of relational database management and the implementation of many different DBMS products. Also important is the ability to accurately communicate them to others. This is not a trivial task since each DBMS is different than the other and many organizations have multiple DBMS products (e.g., DB2, Oracle, SQL Server).

Remember, too, that the database does not exist in a vacuum. It must interact with other components of the IT infrastructure. As such, the DBA must be able to integrate database administration requirements and tasks with general systems management requirements and tasks such as network management, production control and scheduling, and problem resolution, to name just a few systems management disciplines. The capabilities of the DBA must extend to the applications that use databases, too. This is particularly important for complex ERP systems that interface differently with the DBMS. The DBA must be able to understand the requirements of the application users and to administer their databases to avoid interruption of business. This includes understanding how any ERP packages impact the business and how the databases used by those packages differ from traditional relational databases.

But Things Are Changing

So at a high level, DBAs are tasked with managing and assuring the integrity and efficiency of database systems. But keep in mind, too, that there are actually many different DBAs. Some focus on logical design; others focus on physical design; some DBAs specialize in building systems and others specialize in maintaining and tuning systems; and there are specialty DBAs and general-purpose DBAs. Truly, the job of DBA encompasses many roles.

Some organizations choose to split DBA responsibilities into separate jobs. Of course, this occurs most frequently in larger organizations, because smaller organizations often cannot afford the luxury of having multiple, specialty DBAs.

Still other companies simply hire DBAs to perform all of the tasks required to design, create, document, tune, and maintain the organization’s data, databases, and database management systems.

But no matter what "type" of DBA you happen to be, chances are that your role is changing and adapting to new types of computing and data requirements. Indeed, one of the biggest challenges for DBAs these days is the ongoing redefinition of the job roles and responsibilities.

The primary role of database "custodian," of course, continues to be the main emphasis of the job. But that is no longer sufficient for most organizations. The DBA is expected to take on numerous additional -- mostly technical -- roles. These can include writing application code, managing the application server, enterprise application integration, managing Web services, network administration and more.

If you compare the job description of DBAs across several organizations, it is likely that no two of them would match exactly. This is both good and bad. It is good because it continually challenges the technically-minded employees who tend to become DBAs. But it can be bad, too; because the job differs so much from company to company, it becomes more difficult to replace a DBA who leaves or retires. And no one can deny that database administration is a full-time, stressful job all on its own. But the stress level just keeps increasing as additional duties get tacked onto the DBA's "to do" list.

Summary

There are many jobs that DBAs perform and it can be confusing when you try to match job title up against the responsibilities of the job. Don't let your job title keep you from expanding into other, related disciplines. The more you know and the more you can do, the more employable you become... and that is important in this day and age!