Share: |

Guest post by Peter Plevka, Consulting Brand Manager

We’ve all been there…working late into the night, adding and modifying DB2 data structures to cope with changes demanded by the business. To understand how difficult it can be to change the structure of a large DB2 database, imagine trying to change the layout/structure of a city. A large number of objects with complex relational dependencies must be managed. It takes a brave person to manage that degree of change.


Imagine we’ve created a completely new city - similar to a complex DB2 database - and each of the 100,000 houses built within it is represented by one DB2 table. The citizens of the city are the users. This city, like the DB2 database, is an immovable, inanimate object. Any attempt to make changes to the city’s shape, structure, or even to individual houses is risky, and is costly in terms of time, resources and availability.


But change is inevitable. The town is growing; it’s attracting more people, more houses, and more resources—in exactly the same way an organization evolves and demands changes to the DB2 database environment, such as the integration of a new ERP system. In our analogy, that new application may be a new school for the city, the widening of the roads, or just additional and larger rooms in some of the 100,000 houses. These are tough changes to make to the infrastructure: after all, the houses (DB2 tables) are already built/defined. But City Hall says that the new school or the new road is required, so the houses and the people (DB2 data and users) within them must be moved. Of course, these people have lives to live, so the change also needs to be non-disruptive. The people and all their furniture must be temporarily placed elsewhere.


With the decision made to make the changes, the houses are torn down, just like you would drop the DB2 table. But when you perform structural changes to DB2 objects, you must rebuild them exactly as they were before including the new or changed parts. That means rebuilding the houses (DB2 tables) brick-by-brick and putting the people and their furniture back exactly as they were before, but also considering the changes.


As any town planner will explain, it’s imperative not only to adopt quality assurance during any change—but also for the change to be made quickly. For large applications with thousands of objects, complex application changes can significantly increase the length of the time period to apply the changes. The utility maintenance work needed when making complex schema changes to thousands of DB2 objects requires the largest part of the change maintenance window.


If we did everything as a serial process, the reconstruction would take way too long. Parallel processing is a must. As the roads are torn up to be widened or the houses moved and re-built, multiple people must work in a synchronized manner simultaneously to move the houses (the tables) and the furniture (the data).


Finally, we must compare structures. DBAs need to test changes in a non-production environment and apply them when fully tested without losing any local customizations. It’s the equivalent to having a test city and a production city. Every street, every house, every piece of furniture that’s been changed needs to look identical in the production city


Before you make these changes, be sure that you have a plan and that you are using the tools you need to ensure that you make the changes quickly and accurately.

The postings in this blog are my own and don't necessarily represent BMC's opinion or position.