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.