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.”
No comments:
Post a Comment