The Tao of DB2 - Part 12: Dealing with Change

It is time, once again, to look in on our friends, the intern DBA and his mentor. After spending so much time on performance issues, the mentor decides to check on his apprentice and make sure that he is not overwhelmed.

“Well, now that you mention it, the users want to change the data type of several columns,” stated the intern.

“The only constant is change,” replied his mentor. “Remember the Tao; as Lao Tzu tells us through experience the sage becomes aware that all things change. We must be prepared to support many different types of change requests. Some will be simple, others will be more complex. Never the less, all changes require careful planning to implement.”

“I noticed that the ALTER statement can be used to change data types; that doesn’t seem to be all that hard to me?” queried the intern.

“Read that manual carefully,” countered his mentor. “Keep the words of Lao Tzu in mind; he tells us ‘The difficult and the easy complement each other.’”

“Hmmm… I see. Looks like I can switch the data type of a column as long as things stay within the same family of data types. Like character to character... or numeric to numeric. But if I have to change a numeric column to a character or something like that, it get complex very quickly.”

“Very astute,” replied the Taoist DBA.

“So I can make some of these changes relatively easily, but it looks like all I have to do for the others is to drop the table and re-create it specifying the new data type for the column they want to change from CHAR to DATE?” asked the intern.

“Yes, that is all,” replied the Taoist DBA, shaking his head sarcastically.

The lesson here is that dropping an object drops all related objects and authority. If you drop a table, all of its indexes are dropped. And all of the grants are removed. Not to mention the data itself... It is important to understand the connectedness of all DB2 objects and what it means when we change those objects. Without this understanding, catastrophes are inevitable.

An additional lesson to be learned is that changes may need to be undone; you must be ready. A DBA should be prepared to be able to undo any change that is implemented – and then redo it again. Seriously consider a change management tool that will keep track of changes for you and automate the process. Without such a tool you will need to manually track and store your DROP and CREATE statements somehow.

Realization dawning upon him, the intern mutters “Dealing with change seems like it will be a big part of my job.”

With a grin, the Taoist DBA replies, “You have no idea how true that is.”